Entity Reference field fails when choosing from more than 1000 items

Question

I've set up my Drupal system to allow users to select from a list of locations, which are registered as nodes.

However, after importing my ~6000 locations the field lookup no longer works with the following error:

PDOException: SELECT revision.vid AS vid, base.uid AS uid, revision.title AS title, revision.log AS log, revision.status AS status, revision.comment AS comment, revision.promote AS promote, revision.sticky AS sticky, base.nid AS nid, base.type AS type, base.language AS language, base.created AS created, base.changed AS changed, base.tnid AS tnid, base.translate AS translate, revision.timestamp AS revision_timestamp, revision.uid AS revision_uid FROM {node} base INNER JOIN {node_revision} revision ON revision.vid = base.vid WHERE (base.nid IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, ... (trimmed) ... :db_condition_placeholder_5915, :db_condition_placeholder_5916)) ...

SQLSTATE[HY000]: General error: 1795 OCIStmtExecute: ORA-01795: maximum number of expressions in a list is 1000 (ext\pdo_oci\oci_statement.c:148)

I am using an Oracle backend, so it's possible this error is a quirk of Oracle. But in general, is the Entity Reference module intended to allow lookups on large lists of nodes? Or should I move to another module such as the References module?

Answer 1

I haven't tested whether Oracle's performance with Entity Reference is substantively worse than another backend such as MySQL.

However, implementing the workaround detailed here and converting a Drupal user.module query to a dynamic form to also implement partitioning worked, albeit with a 50 second execution time and a hugely increased PHP memory requirement. This is because of multiple complex queries across the whole node set.

Switching to use of the references module and the node_reference content type has vastly improved performance.