Efficient Magento code – database (Flat vs EAV). Part 2.

Care to share?

Now, let’s make the situation more complicated by retrieving multiple data and committing the gravest of Magento developer’s sins –  let’s retrieve consecutive data in the loop by load (I have shuddered).

Headless eCommerce - Download FREE Book

ORM – flat table structure (multiple data – load in a loop)

In the context of the flat table structure created before, let’s execute the code below:

16
17

Here, we can see data retrieval by load, as in the earlier example, but all that takes place in a loop. The result is displaying other guests:

ORM has executed the following SQL operations:

As can easily be inferred, for every loop iteration ORM submitted the same query again and again with a different ID in the condition.

The example above is the most frequent mistake of Magento developers and the one that affects performance. It should be popular knowledge that this is where collections should be used.

ORM – flat table structure (multiple data – collections)

Let’s correct the previous code:

This time after referring to divante_training/residents model we retrieve guests’ collections. Next, we declare that we need two attributes, name and surname, and finally we filter our collection by IDs.

The result is identical (displayed names and surnames of guests) but what ORM did differs from what we had before to a significant degree:

We see that instead of the same duplicated query we have one combined, returning the data required which increases the application performance.

And what happens when we repeat an analogous capacity error for EAV?

ORM – EAV (multiple data – load in a loop)

This is an example of a code loading guests’ data in a loop for EAV table structure:

And this is the result of ORM operation:

This is the worst scenario for Magento code with respect to the performance. In every loop iteration we create several EAV table queries anew. Only the first query retrieving EAV entity type is executed once, all the other are combined anew in every loop iteration.

If we would process more data (e.g. 200 products) in our code, the performance decrease could be seen with a bare eye, with no need to run the profiler.

Let’s not wait any longer and correct the code.

ORM – EAV (multiple data – collection)

The code above refers to divante_barcamp/residents model and retrieves a collection of guests. We only need the guest’s name and surname, and the list of guests required will be filter by IDs possessed.

The result displayed does not differ from the earlier ones, but ORM could ‘rest’ a bit.

We can see that instead of duplicating the same queries for every iteration, the queries were combined and filtered based on the elements required.

I would also like to emphasize that the last query differs from the one we executed before by the fact that we refer solely to resident_entity_varchar table (there is no character after resident_entity_int table).  This happened because both types of data required (name and surname) are VARCHAR, and there is no need to go to other tables as they do not contain any attributes of interest for us.

For collections, remember to enter the list of attributes required as unless we insert them explicitly ORM will not return them.

Conclusions

I hope the examples above showed you the difference between a flat table and EAV, not only with respect to the designing phase but also to the performance.

In every Magento module you should pay attention to the configuration you work with and to the data processing method.

To strengthen your sensitivity to the performance, let me use the diagram below:

The diagram presents differences in product (EAV) processing in a loop by load and by means of collections (that is the examples provided above).

We can see clearly that the load in a loop brings about a gradual capacity decrease. Using the collection does not affect the performance.

I hope this helped you to solve w few Magento code problems!

Go to Efficient Magento code – database (Flat vs EAV). Part 1.

Published December 10, 2014