Optimising amount of calls to custom fields

Question

I have this code fragment in my codebase:

    foreach($this->adverisers as $adveriser) {     

        $this->data[$adveriser->name]['image'] = bfi_thumb(get_post_meta($adveriser->ID, 
                                                            'ss_advertisers_cats_image', true ), 
                                                             $this->imgDimensions  
                                                           );  

        $this->data[$adveriser->name]['description'] = get_post_meta($adveriser->ID,
                                                        'ss_advertisers_cats_description', 
                                                         true 
                                                       );        

        $this->data[$adveriser->name]['advertiser'] = $wpdb->get_results( "SELECT DISTINCT * FROM {$wpdb->posts}
                                                              WHERE(post_type='brands' OR post_type='boutiques') 
                                                              AND post_author='{$adveriser->post_author}' ", OBJECT 
                                                           ); 

        $this->data[$adveriser->name]['advertiserRedirectionLink'] = get_post_meta($adveriser->ID, 
                                                                      'ss_advertisers_cats_link', 
                                                                      true 
                                                                      ); 
}

The loop builds a custom array of data for later display on the site. As you can see, there is a total of four queries to the database executed on each iteration of the loop. This is probably far from optimal performance-wise as, in my view, this is an example of N+1 Query problem.

Is there any straightforward solution that would bring siginificant performance increase to this code? Or is my only option crafting a complex, muli-join SQL query with $wpdb to get all the required data in one/two queries, or .. maybe the presented code is not so bad after all and would not hinder performance so considerably

Answer 1

I will not worry to much about performance here, but I will optimize the code a bit by calling get_post_meta() only once. This will not increase or decrease performance as you will see in the post I have linked to later on, but it comes down to the principle of not repeating yourself.

On the contrary to what you are thinking, querying custom fields is quite optimized and really fast as they are cached. It does not matter whether you are calling get_post_meta() once or a hundred times, the time and amount of queries to get these custom fields stays the same

In short, running a custom SQL query will only decrease performance and not increase it.

You can probably reduce your code to something like the following:

foreach($this->adverisers as $adveriser) {     

    $meta = get_post_meta($adveriser->ID);

    $this->data[$adveriser->name]['image'] = bfi_thumb($meta['ss_advertisers_cats_image'][0]), 
                                                         $this->imgDimensions  
                                                       );  

    $this->data[$adveriser->name]['description'] = $meta['ss_advertisers_cats_description'][0];        

    $this->data[$adveriser->name]['advertiser'] = $wpdb->get_results( "SELECT DISTINCT * FROM {$wpdb->posts}
                                                          WHERE(post_type='brands' OR post_type='boutiques') 
                                                          AND post_author='{$adveriser->post_author}' ", OBJECT 
                                                       ); 

    $this->data[$adveriser->name]['advertiserRedirectionLink'] = $meta['ss_advertisers_cats_link'][0]; 
}

For a full description and reasoning to my madness, take your time and read this post I have recently done on the performance of calling multiple custom fields

Answer 2

Early optimization is the source of 99% of the unreadable and unmaintainable code. (statistics I made up just now)

There are two kinds of optimizations

1. to make things faster
2. to make things reasonable fast

You know what is the type 1 optimizations you need only after your code is ready and you can do general profiling and decide what is worth the effort optimizing. For example if that code is executed only when an admin is logged in then the priority of optimizing it should be super low. If you can use page caching like W3TC then there is probably no need to optimize at all as the code is rarely executed.

The best way to handle type 1 optimization is caching. If you can't cache full page caching then you need to come up with some partial caching - for example store the results of the whole calculation that you do in a transient for 5 minutes (or use options but then you will need to write better invalidation then a time based that the transients provide)

Type 2 optimizations are actually the important ones, because if your query is too slow to execute in 30 seconds, or requires too much memory, then it will just not complete and therefor no caching scheme will help you. But like the first type of optimizations, you should only start thinking about it once you have a reason to do saw, when something in your testing server is just too slow and not randomly trying to optimize parts of your code.

With all that said, the important thing to remember in the context of wordpress is that the core does a great work of optimizing DB queries if you will just let it do so by using the APIs. For example in practice almost all options are loaded at once and stored in the memory and if you use get_option it does not generate an sql query. Same principal goes to data related to posts and queries, therefor the biggest sin in your code is the direct query that looks like it can be written as a wp_query. Unless you are 100% sure you need to write your own SQL, always use the most high level wordpress API.