Structs As Query Indexes, Speed, And Rick Osborne

Posted September 13, 2006 at 8:02 PM by Ben Nadel

Tags: ColdFusion

For those of you who follow my blog, you will know that Rick Osborne is the guy who comes in after I explain things and makes killer suggestions about how they can be done better. I recently gave a case study of how ColdFusion code can be optimized. It involved using the IndexOf() method of the ColdFusion query column object. Rick came in and suggested that using a Struct to create your own query-index would perform faster. As I am a man who likes to learn by doing, I thought I would put this to the test.

To test this, I query from a web statistics program. I am getting information from two tables: web_stats_hit and web_stats_session. Each "hit" in the hits table will have a session id in the session table. The session table also have a UUID column "session_id".

As I am trying to test the merging of two data sources, I am going to hit each table individually and then try to update one with matching values from the other. First I am grabbing the two different data sets:

  • <!--- Query for web hits. --->
  • <cfquery name="qHit" datasource="...">
  • SELECT
  • h.id,
  • h.date_created,
  • h.web_stats_session_id,
  • (
  • ''
  • ) AS session_id
  • FROM
  • web_stats_hit h
  • </cfquery>
  •  
  •  
  • <!--- Query for web sessions. --->
  • <cfquery name="qSession" datasource="...">
  • SELECT
  • s.id,
  • s.session_id
  • FROM
  • web_stats_session s
  • </cfquery>

As I tried to explain earlier, for every qHit.web_stats_session_id, there is a matching session, such that for some combo, qHit.web_stats_session_id == qSession.id. And just to get an idea of the amount of data we are talking about:

qHit: 52,290 records

qSession: 34,753 records

That's a LOT of data to go through. Let's get our "test" on:

  • <cftimer label="IndexOf() Methodology" type="outline">
  •  
  • <!--- Loop over the hit query. --->
  • <cfloop query="qHit">
  •  
  • <!---
  • We want to find a matching session_id based
  • on the session. Get index of matching row.
  • --->
  • <cfset intIndex = qSession[ "id" ].IndexOf(
  • JavaCast( "int", qHit.web_stats_session_id )
  • ) />
  •  
  • <!--- Add one to index (to be ColdFusion friendly). --->
  • <cfset intIndex = (intIndex + 1) />
  •  
  • <!--- Check to see if we have an index. --->
  • <cfif intIndex>
  •  
  • <!--- We found the match, update the row. --->
  • <cfset qHit[ "session_id" ][ qHit.CurrentRow ] =
  • qSession[ "session_id" ][ intIndex ]
  • />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cftimer>
  •  
  • <cftimer label="Struct Index Methodology" type="outline">
  •  
  • <!--- Create a session look up table. --->
  • <cfset objSessionLookUp = StructNew() />
  •  
  • <!---
  • Loop over session and set index rows. We will be using
  • the id column of the session as the key and the
  • session_id as the value. This creates our very own,
  • in-memory index of the qSession query based on ID.
  • --->
  • <cfloop query="qSession">
  •  
  • <!--- Index this value. --->
  • <cfset objSessionLookUp[ qSession.id ] = qSession.CurrentRow />
  •  
  • </cfloop>
  •  
  •  
  • <!--- Loop over the hit query. --->
  • <cfloop query="qHit">
  •  
  • <!---
  • Check to see if the session key exists. If it
  • does, then we found a match.
  • --->
  • <cfif StructKeyExists(
  • objSessionLookUp,
  • qHit.web_stats_session_id
  • )>
  •  
  • <!--- Update the session based on the struct-index. --->
  • <cfset qHit[ "session_id" ][ qHit.CurrentRow ] =
  • objSessionLookUp[ qHit.web_stats_session_id ]
  • />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  • </cftimer>

It turns out Rick was absolute correct. The struct index performs MUCH faster. Here are the stats:

IndexOf() Methodology : 402,037 ms

Struct Index Methodology : 33,403 ms

Some quick math will show you that the struct index method performs in 8% of the time that the IndexOf() methodology does. EIGHT PERCENT! Nuts. I guess the only downside is that you can potentially create a HUGE in-memory structure; but it's only temporary.

Nicely done Rick!



Reader Comments

Rick O
Sep 14, 2006 at 11:29 AM // reply »
153 Comments
277 Points

I figured you'd appreciate this:

http://rickosborne.org/images/screenshots/wtg-ben.png

That graph represents the last week's traffic to my site. This site has accounted for 3% of the traffic to my site, behind only Google and MXNA.


Ben Nadel
Sep 14, 2006 at 11:32 AM // reply »
10,640 Comments
69,065 Points

Rick,

That's awesome :) Glad to be sending people to you. You have good information to share.


Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Bartezz
Feb 10, 2012 at 7:21 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
Update! Instead of $(eval(options.insertAfter)).after(data['insertData']); I now use: var ajaxNode = document.createElement('span'); var parent = $(eval(options.insertAfter))[0].parentNode; ... read »
Bartezz
Feb 10, 2012 at 6:18 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
encountered this same, what I consider, jQuery bug last week. I'm building a site in which I load some content via AJAX. This content contains Linkedin share button placeholders which Linkedin API ne ... read »
Drew Wells
Feb 10, 2012 at 11:30 AM
Cross-Origin Resource Sharing (CORS) AJAX Requests Between jQuery And Node.js
After you understand the concepts here, this is an awesome cheatsheet for enabling CORS in just about anything http://enable-cors.org/ ... read »
JM
Feb 10, 2012 at 9:10 AM
My Safari Browser SQLite Database Hello World Example
@Amy, Here is a very good tutorial on how to use JOIN: http://www.sqltutorial.org/sqljoin-innerjoin.aspx ... read »
Stefan Richter
Feb 10, 2012 at 4:42 AM
Building A Twitter-Inspired RESTful API Architecture In ColdFusion
This is great, very useful Ben. I spotted a small typo in the api.cgm listing: <cfthrow type="Unauthroized" /> Cheers Stefan ... read »
Hugh
Feb 9, 2012 at 10:35 PM
CFDirectory Filtering Uses Pipe Character For Multiple Filters (Thanks Steve Withington)
I was wondering if there would be a filter you could apply so that you got everything but what you included in the filter. As in show me all docs that are not a .pdf. ... read »
Stephen
Feb 9, 2012 at 10:29 PM
Learning ColdFusion 9: Application-Specific Data Sources
@Ben, No offence, but if people were really wanting advanced features they would be using a platform like ASP.NET MVC. CFML is so structurally compromised as a tag-based scripting language that ... read »
Nathan
Feb 9, 2012 at 10:03 PM
Subversion - Cleanup Failed To Process The Following Paths
@Leviaguirre, do you still have problems with this? ... read »