2 edited tags
| link
source | link

Is EntityFieldQuery really this inefficient?

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');


$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();
  ->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')

$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.