11

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.

  • 3
    can you dump both generated sql queries? – Andre Baumeier Mar 12 '13 at 17:08
  • 1
    See this one if you're not sure how to get the SQL out of an EFQ – Clive Mar 12 '13 at 17:28
  • 2
    OK, there's progress: What's going on here is that my site has a bunch of node access rules that are increasing the size of the query quite a bit. Those were getting automatically applied to the EFQ query (even though there is no ->addTag('node_access') in the query??). I reran the "direct" query with a node_access tag, and the execution times are much closer: EFQ's time is now only about a factor of 2 greater than the direct approach, which looks reasonable given the relative SQL that both are pumping out (which I can post if people still care). (cont'd on next comment....) – Jim Miller Mar 12 '13 at 19:33
  • So now the question, I guess, is why I'm automatically getting the node_access stuff in the EFQ version? I thought you had to explicitly ask for it via the addTag() clause?? – Jim Miller Mar 12 '13 at 19:34
  • 1
10

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.

  • i should admit i am probably not right, since the first query uses a pager, too (i didnt notice the ->extend('PagerDefault'); at first) – mojzis Mar 12 '13 at 18:18
  • Whoops, you are right. – kiamlaluno Mar 12 '13 at 18:22
  • this got me really interested, so i am trying something along the lines of the experiment above and cant confirm the huge difference in numbers ... could someone try it, too, please ? – mojzis Mar 12 '13 at 19:16
  • So, just to confirm: EFQ calls ALWAYS invoke the site's node access rules unless you do something to keep that from happening (as described above). Right? – Jim Miller Mar 12 '13 at 20:12
  • @JimMiller That is correct, and it is the reason why the "DANGEROUS_ACCESS_CHECK_OPT_OUT" tag has been added to Drupal 7.15. – kiamlaluno Mar 12 '13 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.