Performance: Efficient use of EntityFieldQuery (with node_load OR node_load_multiple) to update thousands of nodes with/without conditions

Question

I have script that queries a system called Symplectic for publications of over 30 users (each authored over 300 publications). Most of the publications are authored jointly by more than one user. I have done the following using a combination of Entity API and other methods:

1. Programmatically creating publication nodes (with fields i.e. integer, text, lists, taxonomy terms, entity reference, etc).

I would like to avoid the following scenario:

1. Creating duplicate nodes of type publication

Every time I create a publication, I would like to check whether the publication already exist or not (most probably yes because, the users have authored so many publications together). I can achieve this when I am creating all publications at the same time :

1. I will lookup a structure the stores publication specific properties (i.e. Symplectic object id, doi, publication source id, last-modified-timestamp where source being pubmed, scopus, etc.). If not exist then create publications and update the structure, else reference the user to the already existing publication.

The actual use I am Concerned about is, Updating thousands of publications at a later date after they are created. I have the following options, which one do you think is most efficient:

1. Use EntityFieldQuery to query for all nodes of type publications (over 2500 nodes) at once, and store the properties useful for comparison in a structure. Use the structure to decide whether publication exist in Drupal, if no then create it, if yes then check for last-modified-date.

2. Use EntityFieldQuery with condition fields to check if a publication already exist in the Drupal system, if yes then check if update is avaiable, if no then create it. This method would require, N number of quries (2500 publication nodes would result in 2500 calls).

3. During initial create operation (when no publication node exists), create a structure and store the few publication fields that are expected to be unique (not timestamp) across all publications (i.e. symplectic object id, doi, publication source id, last-modified-timestamp), serialize the structure and store it in the file system. During update phase, load the structure from file system, unserialize it and use it to check whether a publication node already exist in the drupal system, if no then create it and update this structure, if yes then check whether update is required.

My worries in regards to above available options:

1. Loading over 3000 publication nodes (of course, EntityFieldQuery would retrun all nids, then I will have to use either node_load_multiple or node_load) - Is this efficient? Would I not face memory problems?

2. 2500+ EntityFieldQuery does not seem to be the right choice due to query time. (Attaching conditions might increase the query time i.e. if symplectic id field of publication equals X)

3. This is my idea, I don't know if it makes sense.

I have Drupal 7 on CentOS 6.5, and a publication content type has the following number of fields: boolean - 2 text - 20 Double field - 3 Taxonomy - 1 List 2, title, body, etc.

What do you recommend based on your experience? I did have a look at Biblio module, it is not the way to go forward for our requirements. I also know that $conditions parameter of node_load_multiple is deprecated in D7.

Sorry for long description, I tried to be as descriptive as possible.

Answer 1

If you can access the data via SQL then I think the Migrate module can do 80%+ of what you want with relative ease. Your hardest part may be either: 1) getting an SQL dump of the data you want from the remote system, or 2) build an SQL representation of the data from the data you can retrieve from the webservice using a Cron type task.

Assuming you can get an SQL version of your (by the way Migrate and what I'm talking about works on XML, JSON, CSV, text files ... and the framework supports "lists" of data from other sources -- but you'd have to develop some of your own Classes for exotic import sources ... I digress) ... migrate supports querying your objects like "I want to move item(s) from point A to B ... and either update or create them." as of Migrate 2.5+. Most people are comfortable with SQL or CSV files so I'll talk about that here. Being Object Oriented you're free to adjust Migrates behavior of course by extending a classes default behavior.

Once you have the SQL data you can use a Migration to import the data. By using Migrate 2.6's (stable release) track_changes option within a Migration class. The Migrate module will take the fields your migrating from point A to point B of your data and create a hash value of all the data. This hash value is used in subsequent calls to Migrate data to deem whether the given item should be created or updated from the data being fed into the Migration.

An example of this can be seen in this great little blog post: http://timonweb.com/using-hash-value-trackchanges-to-detect-source-data-changes-in-migrate-for-drupal-7

The key excerpt enabling track_changes from the blog post is:

class ExampleNodeMigration extends Migration {
  public function __construct($arguments) {
    parent::__construct($arguments);
   $options = array('track_changes' => 1);
   $this->source = new MigrateSourceSQL($this->query(), $this->fields(), NULL, $options);
}

I say SQL would be easiest for this approach because you could easily build a query for your migration that is basically SELECT id, doi, publication source id, last-modified-timestamp where source being pubmed, scopus, etc FROM my_remote_data and put $options = array('track_changes' => 1); and Migrate is basically doing all the work for you -- and should be very little PHP code to consume the data.

I've actually done this process most recently with the Migrate modules default support for CSV files with a Migration that results in 6 different Drupal content types built out from the CSV in dependant-order -- and objects are created/updated on-the-fly using track_changes every time the system does an sync from the remote system ... so it's doable via SQL or CSV easiest I think.

random tidbit: my CSV file is like 40 columns of data and each sub-migration only cares about a limited set of columns in the csv file. So I tell each migration to ignore unwanted fields for that migration by using PHP's unset function in the prepare for the Entity being built. By unsetting the values the hash function only hashes the relevant data I care about for that single drupal object I'm creating with that migration.

Anyways... I hope this rant helps and offers you a path do this. I've used Migrate on and off for single table or larger migrations of data since the Migrate 1.x days ... it can be complicated to learn if you've never used it.