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.
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:
| Function | Time in ms |
|---|---|
| Using QueryAddColumn | 0 |
| Origional Version | 31 |
| Function | Time in ms |
|---|---|
| Using QueryAddColumn | 47 |
| Origional Version | 235 |
| Function | Time in ms |
|---|---|
| Using QueryAddColumn | 110 |
| Origional Version | 453 |

3 comments:
Hey, guy! You should post this UDF in RiaForge! It would be very helpful! Congratulations man!
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.
@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.
Post a Comment