Adventures in Web Application Develompent by Phil Duba: Improving Query Performance using Query of Queries

Popular Entries

Top Commenters

Nathan Mische (11)
Peter Bell (4)
Terrence Ryan (3)
Scott (2)
Jim Priest (2)
David (2)
Scott Stroz (2)
Scott P (2)
Justin Alpino (2)
Ravneet (2)

Slideshows

Pool Surprises...

Search

Enter your email address to subscribe to this blog.

RSS

AGGREGATORS

Improving Query Performance using Query of Queries

Posted On October 14, 2006 6:29 AM By Phil in ColdFusion,Performance

I think I'm going to start a little series on how to improve performance of a ColdFusion application using some of the techniques I've done at work to improve our products' overall performance. The first itemI'll explore is query performance. Now, there are many things one can do to improve performance such as removing Select *, adding the <CFQUERYPARAM> tag, using Joins, removing IN clauses, etc.

Let's say one has done all of this but the query still takes an inordinate amount of time. If that query has an ORDER BY clause in it, then that is most likely the cause of the performance issue. All of the major database vendors have set memory for executing aggregate and sorting type functions. If the recordset is large, and some of ours at work were over 120,000 records, then a sort on that data might utilize more memory than has been allocated, thus, slowing down the execution of that query to the point it may time out during the request causing a slew of other problems. My solution to this issue was to execute the query as it was, but remove the ORDER BY clause. I would then use a query of queries to execute the sorting. Below I have an example of the before and after:

Before:

<CFQUERY datasource="myCFUG" name="qryMeetings">
   SELECT    Mtg.meetingId, Mtg.Title AS Meeting, Mtg.Description AS MeetingDescription, Mtg.MeetingDate
         Pres.presentationId, Pres.Title AS Presentation, Pres.Description AS PresentationDescription,
         File.fileId, File.Filename, File.FileType,
         Speak.FirstName AS SpeakerFirstName, Speak.LastName AS SpeakerLastName,
         Attend.FirstName AS AttendeeFirstName, Attend.LastName AS AttendeeLastName
   FROM   Meeting Mtg INNER JOIN MeetingAttendees MtgAtt ON Mtg.MeetingId = MtgAtt.MeetingId
         INNER JOIN Member Attend ON MtgAtt.MemberId = Attend.MemberId
         OUTER JOIN Presentation Pres
         ON Mtg.MeetingId = Pres.MeetingId
         OUTER JOIN File ON Pres.PresentationId = File.FileId
         OUTER JOIN Member Speak ON Pres.MemberId = Speak.MemberId
   ORDER BY Mtg.MeetingDate, Pres.Title, File.Filename, Attend.LastName, Attend.FirstName
</CFQUERY>

The above query doesn't seem like much but imagine a long history of meetings, lots of attendees, and this type of query could return lots of data, causing a perforamnce drop in the page executing it. The code below is one way to incorporate query of queries, removing the ORDER BY clause from the main query:

<CFQUERY datasource="myCFUG" name="qryMeetings_base">
   SELECT    Mtg.meetingId, Mtg.Title AS Meeting, Mtg.Description AS MeetingDescription, Mtg.MeetingDate
         Pres.presentationId, Pres.Title AS Presentation, Pres.Description AS PresentationDescription,
         File.fileId, File.Filename, File.FileType,
         Speak.FirstName AS SpeakerFirstName, Speak.LastName AS SpeakerLastName,
         Attend.FirstName AS AttendeeFirstName, Attend.LastName AS AttendeeLastName
   FROM   Meeting Mtg INNER JOIN MeetingAttendees MtgAtt ON Mtg.MeetingId = MtgAtt.MeetingId
         INNER JOIN Member Attend ON MtgAtt.MemberId = Attend.MemberId
         OUTER JOIN Presentation Pres
         ON Mtg.MeetingId = Pres.MeetingId
         OUTER JOIN File ON Pres.PresentationId = File.FileId
         INNER JOIN Member Speak ON Pres.MemberId = Speak.MemberId
</CFQUERY>
<CFQUERY dbtype="query" name="qryMeetings">
   SELECT    MeetingId, Meeting, MeetingDescription, MeetingDate
         PresentationId, Presentation, PresentationDescription,
         fileId, Filename, FileType,
         SpeakerFirstName, SpeakerLastName,
         AttendeeFirstName, AttendeeLastName
   FROM   qryMeetings_base
   ORDER BY MeetingDate, Presentation, Filename, AttendeeLastName, AttendeeFirstName
</CFQUERY>

Using this technique at work, we reduced the processing time of our queries against 20+ tables and returning 100,000+ records by over 50%, sometimes up to 80%, just by removing the ORDER BY clause. Again, there are other techniques out there, but if one has put most of those best practices into the query, take a look at ths sorting and see if this technique will improve the performance.