Wednesday, October 28, 2009

ListToQuery UDF

Today I happened to find myself at CFlib.org - I don't go there often - and I was browsing the newer function submissions just for the heck of it and I came across the function ListToQuery by Russ Spivey. It's a straight forward enough function but I thought it could be a little cleaner, and potentially a little faster when dealing with large lists.

Here is my version:


<cffunction name="listToQuery" access="public" returntype="query" output="false"
hint="Converts a list to a single-column query.">

<cfargument name="list" type="string" required="yes" hint="List to convert.">
<cfargument name="delimiters" type="string" required="no" default="," hint="Things that separate list elements.">
<cfargument name="columnName" type="string" required="no" default="column" hint="Name to give query column.">
<cfargument name="includeBlanks" type="boolean" required="no" default="false" hint="include empty elements in the list as empty values in the query">

<cfset var query = queryNew("")>
<cfset QueryAddColumn(query,arguments. columnName,ListToArray(arguments.list,arguments.delimiters,arguments.includeBlanks))>

<cfreturn query>

</cffunction>


Sadly, I couldn't contact Russ directly to share my contribution so hopefully he doesn't mind me sticking this here. If it weren't for his initial cut at the function I wouldn't have thought to even try and write it.

If anyone wants to benchmark the two it would probably be interesting to see if my intuition is correct.
UPDATE:
Ok, well, I benchmarked it with 5000, 10000 and 30000 element lists (I didn't have the patience for a 100,000 element list sorry (i suppose I could optimize my list building). Here were the results:






5000 element list
FunctionTime in ms
Using QueryAddColumn0
Origional Version31






10000 element list
FunctionTime in ms
Using QueryAddColumn47
Origional Version235






30000 element list
FunctionTime in ms
Using QueryAddColumn110
Origional Version453

3 comments:

Felipe Magalhães said...

Hey, guy! You should post this UDF in RiaForge! It would be very helpful! Congratulations man!

Bill said...

Well, I'd prefer to leave the credit with Russ Spivey. I just tweaked it a bit because I saw an easy optimization.

His is already at CFLib - I'll give it some thought though. Thanks for the suggestion.

Raymond Camden said...

@Felipe: RIAForge isn't really meant for individual UDFs. CFLib is. :)

@Bill: This looks like a good update. Can you email me the code (more as a reminder)? The original author will still get credit, but your new version looks to be significantly faster.