Optimizing performance when sorting through hundreds of entries

Question

Currently working on a new EE site that I've inherited, and having difficulty optimizing performance. Everything was dandy until I finished imported all the old content. Now there are ~700 page entries which cause some pages to take ~18 SECS to render!

I have pages entries setup where you can checkbox multiple categories that the page would belong to. User can select multiple categories which a resulting page widget would filter and display entries that correspond with the selected categories.

Categories(example)
- News
--Featured
--Internal
--External

- Year
--2013
--2012

-Product
--Food
--Cars
--Tech

I have a filter template (filter_template) that was made that apparently sorts through all page entries and displays content. I also have a higher level widget which passes the params to the template. *categories is a WB Category Select type. probably a plugin from whom I inherited from.

{embed="includes/filter_template" _cat_ids="{categories}{cat_id}|{/categories}"}
--> passes to
{exp:channel:entries entry_id="{embed:_entry_ids}" category="{embed:_cat_ids}" limit="200"...}
*Do stuff
{/exp:channel:entries}

Looking through online forums, people are saying to convert the calls directly to ee sql queries. However, for the life of me, I'm really bad at SQL...

EDIT: I've managed to get the queries used by EE by using the output profiler. Now my question is how can I use this direct query within my page, so I can bypass EE channel calls?

/*Grab All Entries*/
SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t
                LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
                         INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id WHERE t.entry_id !='' AND t.site_id IN ('1')  AND t.entry_date < 1384799111  AND (t.expiration_date = 0 OR t.expiration_date > 1384799111) AND exp_categories.cat_id = '58' AND t.status = 'open' ORDER BY t.sticky desc, wd.field_id_61 desc, t.entry_date desc, t.entry_id desc LIMIT 0, 200 


/*Grab relevant data text, links etc... of entries*/
SELECT  DISTINCT(t.entry_id),  t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id,
                        w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled, 
                        m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
                        md.*,
                        wd.*
                FROM exp_channel_titles        AS t
                LEFT JOIN exp_channels         AS w  ON t.channel_id = w.channel_id
                LEFT JOIN exp_channel_data    AS wd ON t.entry_id = wd.entry_id
                LEFT JOIN exp_members        AS m  ON m.member_id = t.author_id
                LEFT JOIN exp_member_data    AS md ON md.member_id = m.member_id WHERE t.entry_id IN (605,244,...717) ORDER BY t.sticky desc, wd.field_id_61 desc, t.entry_date desc, t.entry_id desc 

SELECT field_id, field_name FROM exp_category_fields WHERE site_id IN ('1') 

/*Grab category-filter information*/
SELECT c.cat_name, c.cat_url_title, c.cat_id, c.cat_image, c.cat_description, c.parent_id,
                        p.cat_id, p.entry_id, c.group_id , cg.field_html_formatting, fd.* 
                FROM    (exp_categories AS c, exp_category_posts AS p)
                 LEFT JOIN exp_category_field_data AS fd ON fd.cat_id = c.cat_id
                            LEFT JOIN exp_category_groups AS cg ON cg.group_id = c.group_id
                WHERE    c.cat_id = p.cat_id
                AND        p.entry_id IN ('605','244'...'717') ORDER BY c.group_id, c.parent_id, c.cat_order 

Any help for a web dev just trying to piece it together?

Answer 1

I'd grab a copy of the EE Debug Toolbar and install it:

http://devot-ee.com/add-ons/ee-debug-toolbar

It adds a better interface for EE's template debugging, including a graph to plot the performance & time of each page render. Look for all the places where the processing time jumps.

If you have a number of {embed=} tags, particularly within a loop of any kind, work hard to refactor those away. Without seeing more of your template it's hard to know what to recommend.

Also, a limit of 200 for a channel entries loop is not insignificant. Is that section paginated, can you reduce that limit to something lower?

Answer 2

I'm not very familiar with SQL myself, so unfortunately I don't have a direct answer to your question. But once you've sorted out the most efficient way to pull in the entries / data you need I'd recommend taking a look at CE Cache.

It's not a replacement for well written code, but it can be incredibly helpful for situations like the one you're describing. It does a great job of lessening the load on your server by providing some nifty caching options (which should help dramatically drop your query count). The page load boost it can provide is black magic, crazy awesome kind of stuff.