RE: [ cf-dev ] Confusing SQL

Dave Phipps Thu, 16 Jan 2003 05:08:46 -0800

The database is actually MySQL and whilst I could and should cache the circuits query this is only hit once as I call the other 2 queries from within the output of the circuits query and hence cannot cache them as their result is dependent on a var which changes each time the loop is executed.

Here's a code snip:

<cfoutput query="listCircuits" group="circuitid">

<cfinclude template="qry_list_menus.cfm">

<tr><td class="header" colspan="2"><img src="images/arrow_right.gif">#displayname#</td><td width="75" class="list_italic_g">Created</td><td width="75" class="list_italic_g">Modified</td></tr>

<cfoutput group="contentid">
<cfparam name="submenuid" default="0">

<cfloop query="list_menus">

<cfinclude template="qry_list_content.cfm">

<tr <cfif list_menus.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>>
<td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9">
<a href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.circuitid#&menuid=#list_menus.menuid#&contentid=#list_menus.contentid#&#request.urltoken#">#list_menus.pagetitle#</a></td>
<td width="348" class="list_italic">'#contentdescription#...'</td>
<td width="75" class="list">#LSDateFormat(list_menus.contentdate, "dd/mm/yy")#</td>
<td width="75" class="list">#LSDateFormat(list_menus.lastupdate, "dd/mm/yy")#</td>
</tr>
<cfif list_content.recordcount>
<tr>
<td colspan="4" class="header"><img src="images/1px_spacer.gif" width="12" height="9"><img src="images/arrow_right.gif">#list_menus.menuname#</td>
</tr>
</cfif>

<cfloop query="list_content">

<tr <cfif list_content.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>>
<td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9">
<a href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.circuitid#&menuid=#list_content.menuid#&submenuid=#list_content.submenuid#&contentid=#list_content.contentid#&#request.urltoken#">#list_content.pagetitle#</a></td>
<td width="348" class="list_italic">'#contentdescription#...'</td>
<td width="75" class="list">#LSDateFormat(list_content.contentdate, "dd/mm/yy")#</td>
<td width="75" class="list">#LSDateFormat(list_content.lastupdate, "dd/mm/yy")#</td>
</tr>

</cfloop>

</cfloop>
</cfoutput>
<tr><td class="header" colspan="4">   </td></tr>
</cfoutput>

The result of the above code looks something like this:

<tr><td class="header" colspan="2"><img src="images/arrow_right.gif">Academic</td><td width="75" class="list_italic_g">Created</td><td width="75" class="list_italic_g">Modified</td></tr>

<tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=14&contentid=22&CFID=129319&CFTOKEN=71536666">Introduction</a></td><td width="348" class="list_italic">'Introduction...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr>

<tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&contentid=23&CFID=129319&CFTOKEN=71536666">Curriculum</a></td><td width="348" class="list_italic">'The School Organisation...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">14/01/03</td></tr>
<tr><td colspan="4" class="header"><img src="images/1px_spacer.gif" width="12" height="9"><img src="images/arrow_right.gif">Curriculum</td></tr>
<tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&submenuid=5&contentid=25&CFID=129319&CFTOKEN=71536666">Pre-Prep</a></td><td width="348" class="list_italic">'Pre-Prep Curriculum...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr>

<tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&submenuid=6&contentid=26&CFID=129319&CFTOKEN=71536666">Prep</a></td><td width="348" class="list_italic">'Prep School Curriculum...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr>

<tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&submenuid=7&contentid=27&CFID=129319&CFTOKEN=71536666">Pastoral Care</a></td><td width="348" class="list_italic">'Pastoral Care...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">04/12/02</td></tr>

<tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&submenuid=8&contentid=28&CFID=129319&CFTOKEN=71536666">Trips</a></td><td width="348" class="list_italic">'Trips...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr>

<tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&menuid=15&submenuid=10&contentid=47&CFID=129319&CFTOKEN=71536666">Music</a></td><td width="348" class="list_italic">'Music...'</td><td width="75" class="list">10/12/02</td><td width="75" class="list">08/01/03</td></tr>

It all works but is a little heavy handed. Is there a better way. Could I build an array or structure to hold the info I need?

Cheers

Dave

At 12:48 1/16/2003 +0000, you wrote:

Oh and the suggestion would be (if the circuits/menus don't change too
much):


Cache the queries

This way the database doesn't get hit so much.  The content may change too,
but it might be useful to have, for example, a 10 or 30 minute cache on
those too.  Just to take the load off.

Paul

> Hi,
>
> I have three queries that build a navigation system which is
> running really
> slowly.  This is because I haven't been able to combine the
> three queries
> into one and therefore have to do nested loops which results
> in about 50
> queries running for some sections of the site.
>
> The three queries are below:
>
> ListCircuits:
>
> SELECT circuits.circuitid, circuits.circuitname, circuits.contentid,
> circuits.displayname, content.contentid, content.pagetitle,
> content.contentdate, content.contentdescription, content.lastupdate,
> content.app_status, content.lastupdate, groups.groupid,
> groups.groupname FROM groups, content_group, circuits LEFT
> JOIN content ON
> circuits.contentid = content.contentid
> WHERE content.contentid = content_group.contentid
> AND groups.groupid = content_group.groupid
> ORDER BY circuits.circuitid
>
> ListMenus
>
> SELECT circuits.circuitid, circuits.circuitname,
> circuits.displayname,
> menus.menuid, menus.menuname, menus.contentid, submenus.submenuid,
> content.pagetitle, content.contentdate, content.contentdescription,
> content.lastupdate, content.app_status, groups.groupid,
> groups.groupname FROM groups, circuits LEFT JOIN menus ON
> circuits.circuitid =
> menus.circuitid LEFT JOIN submenus ON menus.menuid =
> submenus.menuid LEFT
> JOIN content ON menus.contentid = content.contentid  LEFT JOIN
> content_group ON content.contentid = content_group.contentid
> WHERE content_group.groupid = groups.groupid AND
> circuits.circuitid = #listCircuits.circuitid# AND
> content.pagetitle != 'The Week Ahead' GROUP BY
> content.contentid ORDER BY menus.menuid
>
> ListContent
>
> SELECT submenus.submenuid, submenus.menuid, submenus.submenuname,
> content.contentid, content.pagetitle, content.contentdate,
> content.contentdescription, content.lastupdate, content.app_status,
> content.isDeleted, groups.groupname, groups.groupid
> FROM submenus LEFT JOIN content ON submenus.submenuid =
> content.submenuid
> LEFT JOIN content_group ON content.contentid =
> content_group.contentid LEFT
> JOIN groups ON content_group.groupid = groups.groupid
> WHERE  (submenus.menuid = #list_menus.menuid#)
> ORDER BY submenus.submenuid, content.contentid
>
> Can anyone see a way to pool these three queries into one query?  The
> problem I have found is that each table (circuits, menus,
> submenus) has a
> contentid (which is the default page for that section).
>
> Any help on this?
>
> Cheers
>
> Dave
>
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED] For human help, e-mail:
> [EMAIL PROTECTED]
>
>
>




--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]


============================================
Phipps CF Development
Oxford, Oxfordshire.
Telephone: +44(0)7718 896696
http://www.phipps-cf.co.uk
============================================


--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]

RE: [ cf-dev ] Confusing SQL

Reply via email to