Adventures in Web Application Develompent by Phil Duba: Improving Performance: Using Structures instead of Arrays

Popular Entries

Top Commenters

Nathan Mische (12)
CFFusionDev (6)
CFdevfusion (6)
Peter Bell (4)
Sean Corfield (3)
Rey Bango (3)
Terrence Ryan (3)
ah7866 (3)
Scott (2)
Jim Priest (2)

Slideshows

Dresser/Changing Table...
Images related to the lay...
Nursery renovations...
Pool Surprises...

Search

Enter your email address to subscribe to this blog.

RSS

AGGREGATORS

Improving Performance: Using Structures instead of Arrays

Posted On November 21, 2006 4:54 PM By Phil in ColdFusion,Performance

In this entry on performance, I want to talk about using Structures to help save performance. I believe Structures are probably one of the least utilized variable types in the CF Community, yet they are probably the most powerful. At work, in some of the reports I was looking at improving, they utilized a base query (which I talked about in earlier posts) but had supporting, aggregate-based queries. To add the SQL in these queries would critically drop the performance of the report.

In this report, in addition to all the meetings and presentations a member may have attended, we also want the first attended meeting to be displayed. The query for this would be something like:

<CFQUERY name="qryFirstmeeting" dbtype="query">
SELECT userId, min(meetingDate) as firstMeeting
FROM qryMeetings
GROUP BY userId
ORDER BY userId
</CFQUERY>

Now, I have seen some developers do this in every loop of the CFOUTPUT tag, but that contributes to the performance issues one may see on the report/display of this data. At, the reports I was evaluating had something along the following:

<CFSET arrFirstMeeting = ArrayNew(1) />
<CFOUTPUT query="qryFirstMeeting">
<CFSET arrFirstMeeting[qryFirstMeeting.userId] = qryFirstMeeting.firstMeeting />
</CFOUTPUT>

<CFOUTPUT query="qryMeetings">

<TD>#DateFormat(arrFirstMeeting[qryMeetings.userId])#</TD>

</CFOUTPUT>

Now, this code is ok if your userIds start at 1 and there are no breaks, but with data zero downs, deleting of profiles, etc., we know this isn't the case. If, for instance, the userIds started at 100, then the very first would end up producing 99 blank Array elements before it ever began populating and on the output, it would scan across all of these to get to 100 for the first output. I changed this to building a Struct and the improvement was significant, although not as much as the query and string building in the previous posts. The following is how I would make the Struct logic:

<CFSET stctFirstMeeting = StructNew() />
<CFOUTPUT query="qryFirstMeeting">
<CFSET StructInsert(stctFirstMeeting,"#qryFirstMeeting.meetingId#","#qryFirstMeeting.firstMeeting#") />
</CFOUTPUT>

<CFOUTPUT query="qryMeetings">

<TD>#DateFormat(stctFirstMeeting["#qryMeetings.userId#"])#</TD>

</CFOUTPUT>

Now, in this instance, ColdFusion does not have to fill in any array elements and the number of keys is equal to the recordcount and the lookup is cleaner. The above technique along with the previous two posts helped to dramatically improve performance in a number of areas of our systems and hope the examples, while simple, show the techniques in a clear manner.