Is EntityFieldQuery really this inefficient?

Question

I am an admitted newbie to the Entity API, but am trying to cure that. I'm working on a site that uses a number of content types with various fields attached to them; nothing fancy. So, when I want to retrieve a set of entries, I have been, in my ignorance, calling directly down into the database and doing something like this:

$query = db_select('node', 'n')->extend('PagerDefault');
$query->fields('n', array('nid'));
$query->condition('n.type', 'my_content_type');

$query->leftJoin('field_data_field_user_role', 'role', 'n.nid = role.entity_id');
$query->condition('role.field_user_role_value', $some_value);

$query->leftJoin('field_data_field_withdrawn_time', 'wt', 'n.nid = wt.entity_id');
$query->condition('wt.field_withdrawn_time_value', 0);

$query->orderBy('n.created', 'desc');

$query->limit(10);

$result = $the_questions->execute()->fetchCol();

(yes, I could probably collapse a bunch of these lines into a single $the_questions-> statement; pls ignore that for now.)

Trying to rewrite this with EntityFieldQuery, I come up with:

$query = new EntityFieldQuery();
$query
  ->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'my_content_type')
  ->fieldCondition('field_user_role', 'value', $some_value)
  ->fieldCondition('field_withdrawn_time', 'value', 0)
  ->propertyOrderBy('created', 'desc')
  ->pager(10);

$result = $query->execute();

if (isset($result['node'])) {
    $result_nids = array_keys($result['node']);
}
else {
    $result_nids = array();
}

which gives me the desired results and is surely much prettier.

So, now I'm wondering about performance. As a start, I throw each of those bits of code into a stupid for() loop, capturing time() before and after execution. I run each version 100 times over a not-very-big database, and get something like this:

• Direct version: 110 msec
• EFQ version: 4943 msec

Obviously I get different results when I re-run the test, but the results are consistently in the same ballpark.

Yikes. Am I doing something wrong here, or is this just the cost of using EFQ? I have not done any special database tuning with respect to the content types; they're just what comes from defining the content types in the usual, form-based way. Any thoughts? The EFQ code is definitely cleaner, but I really don't think I can afford a 40x performance hit.

Answer 1

The EntityFieldQuery class is as efficient as its requirements allow it to be. It needs to be compatible with any field storage classes, even with those that use a NoSQL engine to store the field data, such as the one that uses MongoDB. For that reason, EntityFieldQuery cannot directly query the database, because the current field storage backend might not use an SQL database at all.

Even in the case the field storage uses a SQL engine to store its data, the equivalent of $query->leftJoin('field_data_field_user_role', 'role', 'n.nid = role.entity_id'); $query->condition('role.field_user_role_value', $some_value); for the EntityFieldQuery class requires:

• Code to build the database table name from the field name
• Code to build the condition to use to join the table containing the field data with the table containing the entity data
• Code to build the name of the database row containing the field data

The difference is immediately visible: In one case you are using three litteral strings, while in the other case there is code that (in the simplest of the cases) is concatenating strings.

As per your comment about code that checks if the user has the permission to access the fields, you can bypass that using the following line to the code using the EntityFieldQuery class.

$query->addTag('DANGEROUS_ACCESS_CHECK_OPT_OUT');

This works if you are using Drupal 7.15, or higher; for earlier versions, you should use the following code.

$account = user_load(1);
$query->addMetaData('account', $account);

As usual, you should not bypass the access permission if the code could show to the user information to which the user should not have access. This is similar to what is done from Drupal when an unpublished node is only shown to the users who have the permission to see unpublished nodes. If the code's purpose is, for example, selecting some entities that are successively deleted (e.g. during cron tasks), then by-passing the access control doesn't make any harm, and it is the only way to proceed.