I have been sitting on this for some time now and thought it was about time to write it up.SQL queries can be used in place of native <cms:pages> tags to speed up page load by up to 90x, which is a huge jump.
We wanted to create a graph that showed user activity (represented by cloned pages) over the course of a typical week. This was our previous code
It basically ran through each cloned page, checked what day it was published and then added '1' to the running total. This did the trick but the page took 95.878 seconds to load and relied upon 38733 queries. With the help of Trendoman and a very basic understanding of constructing SQL SELECT queries, this was reduced to take 1.592 seconds to load and relied upon 33 queries. I've included the code below.
The code above could be optimised even further by using %N as the PHP date format to prevent the need for setting the 'day' variable at all.
Again this is a niche application but I am certain there are other uses for SQL queries to speed up page load times significantly when using large data sets.
Here are is another speed improvements
from 80.867 sec to 3.155 sec
from 38737 queries to 67 queries
We wanted to create a graph that showed user activity (represented by cloned pages) over the course of a typical week. This was our previous code
- Code: Select all
<cms:pages masterpage='activity.php' skip_custom_fields='1' limit='1000000'>
<cms:set day = "<cms:date k_page_date format='D'/>"/>
<cms:put var="add_day" value="<cms:get day/>"/>
<cms:put var="<cms:show day/>" value="<cms:add add_day '1'/>" scope='global' />
</cms:pages>
It basically ran through each cloned page, checked what day it was published and then added '1' to the running total. This did the trick but the page took 95.878 seconds to load and relied upon 38733 queries. With the help of Trendoman and a very basic understanding of constructing SQL SELECT queries, this was reduced to take 1.592 seconds to load and relied upon 33 queries. I've included the code below.
- Code: Select all
<cms:repeat count='7'>
<cms:if k_count = '0'><cms:set day = "Mon"/></cms:if>
<cms:if k_count = '1'><cms:set day = "Tue"/></cms:if>
<cms:if k_count = '2'><cms:set day = "Wed"/></cms:if>
<cms:if k_count = '3'><cms:set day = "Thu"/></cms:if>
<cms:if k_count = '4'><cms:set day = "Fri"/></cms:if>
<cms:if k_count = '5'><cms:set day = "Sat"/></cms:if>
<cms:if k_count = '6'><cms:set day = "Sun"/></cms:if>
<cms:query sql=" SELECT p.id pid, t.name tname FROM <cms:php>echo K_TBL_PAGES;</cms:php> p inner join <cms:php>echo K_TBL_TEMPLATES;</cms:php> t on p.template_id = t.id WHERE t.name='activity.php' AND publish_date < '<cms:date format='Y-m-d H:i:s' />' AND DATE_FORMAT(publish_date,'%a')='<cms:show day/>' limit='1' skip_custom_fields='1'>
<cms:put var="<cms:show day/>" value="<cms:show k_total_records/>" scope='global'/>
</cms:query>
</cms:repeat>
The code above could be optimised even further by using %N as the PHP date format to prevent the need for setting the 'day' variable at all.
Again this is a niche application but I am certain there are other uses for SQL queries to speed up page load times significantly when using large data sets.
Here are is another speed improvements
from 80.867 sec to 3.155 sec
from 38737 queries to 67 queries