Ways to speed up Channel Entries

Question

Setup: We have a call that pulls 50 entires from channel entries.

{exp:channel:entries channel="posts"  orderby="entry_id" limit="50" dynamic="no" disable="categories|category_fields"}

This call takes on avg 700ms. When our traffic has around 50 users concurrent (from a load test), these calls can take upwards of 10 seconds each. It used to take up to 150 seconds but we picked up the if else plugin to bypass how ee deals with your advanced conditionals.

When we switch our call to a sql it takes on avg .1ms. And stays under 1sec during high load.

{exp:query sql="SELECT field_id_35, field_id_39, field_id_40, field_id_41 field_id_ FROM exp_channel_data WHERE channel_id = '9'  ORDER BY entry_id DESC LIMIT 50"}

• Is there a way to use channel entires but completely bypass all of the parsing? Like a parsing off switch/hook?
• Does anyone know of a way to completely bypass ee/ci's channel entries parsing?
  • Possibly a plugin to completely replace channel entires?

Thanks everyone

Answer 1

Unfortunately the {exp:channel:entries} tag is fairly heavy (even if it is useful!). If you look at the code, the main function is over 1,000 lines and there are several additional functions which are called.

To get a view of what is happening to the database, enable the Output Profiler. I assume that you know where it is, but for the sake of others this can be found under Admin -> System Administration -> Output and Debugging.

Now duplicate your {exp:channel:entries} template tag and sort one ASC and the other DESC. Reload the page and you should see 4 SQL queries per tag pair.

I can see that you know at least a little SQL. Do you see how EE is performing one query to select the entry_ids it should display, and then another query to retrieve the actual results? This is related to the pagination code, except that it happens even if you disable pagination.

In the name of raw speed, you will be better off with a straight SQL query. If you want to make it EE-friendly, you can write a small plugin that only retrieves the standard and custom fields for the channel. That will cut down your requests by at least one SQL query, and you can control the optimization better than EE does. Here is a minimal plugin that does roughly the same thing as your SQL query above:

<?php if (!defined('BASEPATH')) { exit('No direct script access allowed.'); }
$plugin_info = array(
    'pi_name' => 'Faster Entries',
    'pi_version' => 0.1,
    'pi_author' => 'Your Name Here',
    'pi_author_url' => 'http://your-domain-here.com',
    'pi_description' => 'Load channel entries faster',
    'pi_usage' => Faster_entries::usage()
);

class Faster_entries {
    function __construct() {
        $this->EE =& get_instance();

        $limit = $this->EE->TMPL->fetch_param('limit', 50);
        $channel = $this->EE->TMPL->fetch_param('channel', 9);

        $this->EE->db->from('channel_titles ct');
        $this->EE->db->join('channel_data cd', 'cd.entry_id = ct.entry_id');
        $this->EE->db->where('ct.channel_id', $channel);
        $this->EE->db->order_by('ct.entry_id');
        $this->EE->db->limit($limit);
        $query = $this->EE->db->get();

        $this->return_data = '';
        if ($query->num_rows()) {
            foreach ($query->result_array() as $row) {
                $this->return_data .= $this->EE->functions->var_swap($this->EE->TMPL->tagdata, $row);
            }
        }
    }

    public static function usage() {
        ob_start(); ?>
        {exp:faster_entries channel="9" limit="50"}{title}<br />{/exp:faster_entries}
        <?php
        return ob_get_clean();
    }
}

/* End of file pi.faster_entries.php */
/* Location: ./system/expressionengine/third_party/faster_entries/pi.faster_entries.php */

And then comes the best part. You can add in the site lookup when dealing with MSM sites, or you can leave that out. You could perform another database query to retrieve the custom field names (caching them in the Session class, of course). It's all up to you and the templates continue to look clean.

Answer 2

If you're getting these kind of issues for a single channel:entries loop with no other "heavy" tags nested inside, and caching of any kind really isn't an option for you, then my first question would be, what are you doing in that template? If you're doing nothing funky (no embeds, complex relationships etc) then I'd start further down the stack, but without seeing some code it's pretty tough to tell where to start.

But looking generally, you've essentially got three possible areas of attack:

2. Template optimisation
3. Stack optimisation
4. Bypassing channel entries

First though I'd get some lower level benchmark data - the core one being to see where you are becoming resource contrained - ie is it CPU or memory usage that's holding you back. Munin is you're friend.

I've found graphite to be a really useful way of finding bottlenecks in the template parsing process. If you haven't used it, it basically replaces the default template debugger with a graph, which really helps to visualise where things are getting held up.

Template optimisation

If your benchmarking shows the issue is slow PHP execution during your entries loop, take a close look at any data manipulation / plugin tags being executed for each result. Ideally post some of your template code so we can have a look. If you're not doing anything crazy move on to stack optimisation instead.

If you're having to do any jumping through hoops to navigate the parse order take a look at the very wonderful Stash, which has completely changed the way many of us code EE templates. It sounds as if you're already using If/Else, have a look at Switchee as well.

Stack optimisation

Of both PHP & MySQL, but also your webserver. From on your benchmarking/debugger output you should be able to tell whether the issue is PHP execution time or MySQL query execution time. Use that to inform where to start.

With MySQL even basic tuning can produce serious performance gains. If you have access to a dba/sysadmin type set them loose, otherwise if you're happy in the shell take a crack at using MySQLTuner. Does your data need to be literally real time or could you cope with a few seconds of lag? If you're getting a large number of parallel requests for the same complex data then even very short lived caching in MySQL can help considerably.

Also take a look at what PHP handler you're using. If it's an option for you in terms of cost, you might also want to look at your hardware setup, I've switched a couple of high traffic EE sites to SSD drives and the speed gains have been unbelievable (especially when using Ngnix + FastCGI).

Bypassing channel entries

The reason I've put this last, is that I've gone down this route in the past, and while the performance gains can be considerable, the downside is a massive increase in the fragility of your code and an accompanying decrease in its readabilty. Do bear in mind that it also does not escape values on it's own, so can open SQL injection vulnerabilities if using dynamic/user inputted data.

To mitigate against these factors I'd always recommend using the Active Record plugin in preference to the native query module, as it escapes vars and is far more readable. You've still got the issue of hard coding your channel_data column names into your template though, which is icky to say the least. Either comment the heck out of your code or consider using snippets/low varialbes to substitute in readable/easily updateable variable names instead (eg map field_id_XX to sn_your_field_name).

Alternatively do have a look at the Data API module Ben suggests, though it's pretty alpha at current.

Answer 3

The first thing you should try is adding some disable parameters:

{exp:channel:entries channel="curated_posts" orderby="entry_id" limit="50" dynamic="no" disable="categories|category_fields|member_data|pagination"}

Put this in a template on its own and try to measure how long it takes to load. Then check if it is significantly more in your actual template. Perhaps you are using inefficient code.

If you want to bypass the channel entries tag completely then you can try Chris Imrie's ExpressionEngine Data API - a drop in channel and entry data REST API. It is actually a separate CodeIgniter App so should run really fast but will return either XML or JSON. Since you are preprocessing the data this may be ok for you.

You can also try Eric Lamb's Export It add-on which has a similar channel entries REST API.

Answer 4

I wouldn't necessarily go that route first. I would try to cache the templates using CE Cache. http://www.causingeffect.com/software/expressionengine/ce-cache

That's pretty much the standard caching add-on everyone uses, and works very well. Before altering the logic, I would try to cache the templates (provided you don't need truly "real-time" data).

Answer 5

700ms for a single {exp:channel:entries} is a bit much... it shouldn't take more than a few milliseconds.

What are your server specs and do they meet the minimal requirements? Joel Bradbury once did some research and according to him it didn't make much difference if you had 10, 100, or 10.000 entries... but if your server doesn't have enough power to parse some simple PHP functions this can slow down your site considerably.

– Wouter