1

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?

  • What about the code you posted isn't working? – Anna_MediaGirl Nov 17 '13 at 16:20
  • Code is working, just really slow. Looking for tips/tricks to improve performance. Since I've posted, I got the 18secs down to 8. Still slow... – Chris Nov 18 '13 at 1:03
  • What about the SQL isn't working? – Anna_MediaGirl Nov 18 '13 at 16:18
  • Made an edit to my post. I found out what query EE is using. Now I want to know how I can use that query to preform the same actions EE does. Sorry that my SQL/EE knowledge is cursory. – Chris Nov 18 '13 at 18:35
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?

| improve this answer | |
  • Agreed on 200 loops is not a lot. Which speaks to the inefficiency of how it was written. I wish for it to use pagination, but the page is crazy and can't work that way right now. – Chris Nov 18 '13 at 21:28
0

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.

| improve this answer | |
  • I do like black magic when it comes to coding. I will invest in this pluggin. – Chris Nov 18 '13 at 21:29
  • Yep - CE Cache is incredible. – Jules Matthews Nov 19 '13 at 22:11

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.