ORM performance - load all fields vs. load selected fields

Need Help
1

Hello there!

When I wrote sql queries without cake, I took care, that I had as few sql queries as possible and only selected the fields that were neccessary.

Now, with cake, I often use the finders with the result, that all fields of all related models are loaded.

So now I want to know, if I should modify my queries and only load the needed fields, or if it is no problem to load all data fields. (I’m not talking about big blob-fields, but teh default-fields like some varchar/text/integer-fields).

And if not - how can I exclude some fields while using the finders. e.g., when I load user(s), the password is also loaded. Sometimes we need the password to work with, but mostly we don’t.

Thanks in advance!

2

I did a couple of tests and yes, limiting the number of fields can definitely improve the speed of your queries.
The difference is proportional to how many entries you’re searching for; So if you’re expecting to find something like 100 entries, it would make a lot of sense to limit the fields. On the other hand, when you’re only trying to find one entry, there’s not really much of a difference at all, but it naturally depends on how complex the fields are.

Using contains also (predictably) adds to the query time.

You can use select to limit fields like so:

$this->Articles->find()->select(['id', 'title'])->all();

Unfortunately, I don’t think there’s an easy way to exclude fields; You’ll have to list the ones you want to fetch just as in a regular SQL query.

By the way - apparently, there’s also a significant performance reduction when using toArray(), but again only relevant for large result sets. So if you’re concerned about performance and want to get an array instead of an object, it’s better to use hydrate(false) in your query. That said, the difference in most cases seems to be very neglible, especially since we’re usually dealing with milliseconds in the first place, so there’s no reason to be too concerned with it.

In summary: Query performance is same as in previous versions and in a sense very predictable - the more data you retrieve and the more you do with it afterwards, the longer the query time will be. So trying to get less data and only what you need will pretty much always result in better performance.

3 Likes
3

Thx for the great answer. That’s just what I was thinking. And somehow, I missed the “select”-method.

So also with cake, I will go on with limiting the amount of queries and fields. Thanks!