Why does loadMultiple load 1000 nodes 9.2 times faster than loading each node one by one?

Question

I have always known that loading multiple nodes at the same time is faster than loading them one by one.

What I wasn't aware of, is the huge performance difference between the two ways of loading nodes.

I made an example to see the difference. Here's my result:

Load 1000 nodes one by one:

$start_time = microtime(TRUE);
foreach (range(1, 1000) as $nid) {
  $node = \Drupal::entityTypeManager()->getStorage('node')->load($nid);
  if ($node) {
    print_r($node->id());
  }
}
$end_time = microtime(TRUE);
print_r($end_time - $start_time);

Execution time: 10.7266 seconds.

Load 1000 nodes at the same time:

$start_time = microtime(TRUE);
$nids = [];
foreach (range(1, 1000) as $nid) {
  $nids[] = $nid;
}

$nodes = \Drupal::entityTypeManager()->getStorage('node')->loadMultiple($nids);

foreach ($nodes as $node) {
  if ($node) {
    print_r($node->id());
  }
}
$end_time = microtime(TRUE);
print_r($end_time - $start_time);

Execution time: 1.1664 second.

How can loadMultiple be 9.2 times faster than loading the nodes one by one?

Is it the connection between Drupal and the database? What other factors are slowing down the process when loading the nodes one by one?

I have cleared the cache after every request to avoid the loaded nodes being cached.

I used the microtime function to calculate the exact execution times.

Answer 1

It's likely that when you are loading individual nodes, you are loading the entity type manager, then for each node retrieve the storage, do a query to get the node data, and build the node. With the multiple process, some of the DB queries are likely combined, and the processing will be in a batch rather than having to run through the code multiple times.

Answer 2

This actually has nothing to do with the storage or entity type manager itself.

However, it is true that loadMultiple is faster in this case.

The slowness comes from \Drupal\Core\Entity\Sql\SqlContentEntityStorage::getFromStorage to be exact.

As a matter of fact, even if you do call load, it will call loadMultiple.

For the reason why loading entities individually is slower is because how the data is queried from database. In most usual scenario entity has base table, data table and possible revision tables. With node, you often have also extra fields, of which have separate table as well.

So as bare minimum on every load, you will be querying node, node_field_data, node_revision and node_field_revision.

When you load multiple entities, under the hood these tables are called only once including all entity IDs. Total of 4 queries.

When you load individually entities, under the hood these tables are also called for every entity. That's total of 4000 queries in your example.

If you have 20 extra fields for node, that would mean 44 queries vs 44000 queries.

Here's snippet you can utilize to see the difference in action.

<?php

$entityType = 'node';
$storage = \Drupal::entityTypeManager()->getStorage($entityType);
$ids = $storage->getQuery()->range(0, 1000)->execute();
$storage->resetCache();

$startTime = microtime(TRUE);
foreach ($ids as $id) {
  $entity = $storage->load($id);
  unset($entity);
}
$endTime = microtime(TRUE);
$runTime = $endTime - $startTime;
echo "Individual: {$runTime} seconds" . PHP_EOL;

$storage->resetCache();

$startTime = microtime(TRUE);
$entities = $storage->loadMultiple($ids);
unset($entities);
$endTime = microtime(TRUE);
$runTime = $endTime - $startTime;
echo "Multiple: {$runTime} seconds" . PHP_EOL;