Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Justin Carter
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Justin Carter ( @justincarter )

Structs As Query Indexes, Speed, And Rick Osborne

By on
Tags:

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!

Want to use code from this post? Check out the license.

Reader Comments

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel