18

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

  • Wow, great help from everyone. Thanks for all the feedback!! – Jordan Dec 18 '12 at 18:43
21

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.

  • btw I had to swap tmpl and functions in this line to get it to work. $this->return_data .= $this->EE->functions->var_swap($this->EE->TMPL->tagdata, $row); – Jordan Dec 18 '12 at 22:18
  • Oops, I had tested the code and then made a few tweaks after modifying it for SE. Updated. – EpicVoyage Dec 19 '12 at 2:49
  • This only works if you don't need to use fieldtypes. This really isn't a viable option for grabbing "channel entries". Rather this grabs data from the channel titles and channel data table, and replaces some literal strings - nothing more. – Justin Kimbrell Dec 19 '12 at 14:06
  • Correct, I would love to use field types but this is a cleaner version of what I currently have. We can most likely add in the field type looks up later. Or now if someone here is able to code it out quickly. – Jordan Dec 19 '12 at 17:01
10

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:

  1. Template optimisation
  2. Stack optimisation
  3. 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.

  • Thanks Tom, I am using new relic to get traces and do benchmarks. I am also using the graphite plugin for the benchmarking/debugger. Thanks for all the great advice. You answered a ton of my EE questions – Jordan Dec 18 '12 at 18:51
  • Do you have a prefernce between if/else and switchee? – Jordan Dec 18 '12 at 19:10
  • Different tools for different things. Use switchee where you'd use a case/switch statement in PHP (useful for being able to do default, and v fast, but must be the same test for each statement) and if/else when you need more complex conditional logic. – Tom Davies Dec 18 '12 at 19:27
  • There are two different types of {if} conditionals within EE. Some are considered "simple" and those are parsed before almost anything else. The "advanced" conditionals are parsed just before the {embed} tags are processed (virtually last). Switchee makes it possible to evaluate some advanced conditions much earlier in the parse order. Very useful. – EpicVoyage Dec 19 '12 at 2:56
  • Worth adding that's also the case when using Mark Croxton's if/else plugin - so you can use that if you need an early parsed complex conditional (ie more complex than switchee will allow). It's only the native complex conditionals that are always late parsed. – Tom Davies Dec 19 '12 at 21:01
7

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.

  • Thanks. I have tried disable but it was negligible. I will look into those plugins. I don't think they are quite what I am looking for but they may lead to the right solution. Thanks! – Jordan Dec 18 '12 at 0:18
  • 1
    If you have "categories" in your disable parameter, you don't need "category_fields". Disabling one disables the other automatically. – kmgdev Dec 18 '12 at 22:55
3

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).

  • Sorry I should have posted this earlier. It's realtime data. We basically do a few api calls to get the latest entires, run some of our own clean up code and then present it to the user. Thanks for the quick response. – Jordan Dec 17 '12 at 22:54
  • Also, I will try ce-cache either way. It may end up helping out in other areas of the site. – Jordan Dec 17 '12 at 22:57
  • 1
    Love you, Justin, but I STRONGLY disagree. Never, never, NEVER cache as the first line of attack on a performance problem. All it does is mask the symptoms; you haven't learned anything from it, and you haven't actually fixed the issue. Caching is something you do after you've done performance optimization to the extent that you can. – adrienne Dec 19 '12 at 7:11
  • I totally agree, and don't use caching ever at the first line of defense. But to those that know nothing of SQL and PHP optimization, this is a viable solution for low budget clients. If a client only pays a small fee, I personally don't care if their site isn't 100% optimal and if caching makes them satisfied so be it. – Justin Kimbrell Dec 19 '12 at 14:04
  • @adrienne I agree as well. I have a similar stance as Justin too. Fortunately this current project requires us to optimize the entire stack first then cache last. – Jordan Dec 19 '12 at 17:04
1

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

  • It's a call for 50 entries. Each entry has 15 custom fields with no additional parsing or plugins. We are using Engine hosting which has plenty of horse power for our needs. – Jordan Dec 21 '12 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.