Thursday, March 26, 2009

Using CFTHREAD to loop through a query

My first experience with Cfthread tag, I had to loop through a query with loads of records. To make the looping faster, I used 10 cfthreads, below is how to do it.

<cfquery name="get_data" datasource="whatever">
select xyz from table
</cfquery>

First create 10 threads

<cfloop index="intGet" from="0" to="9" step="1">
<cfthread action="run" name="objGet#intGet#">

do some calculations to get the startrow and endrow for each thread to loop through

<cfset startRow = int((#intGet# * #get_users.Recordcount# / 10) + 1)>
<cfset endRow = int(((#intGet# + 1) * #get_users.Recordcount# / 10))>

Loop through the query

<cfloop query="get_data" startrow="#startRow#" endrow="#endRow#">
-------- Your calculations / stuff goes here -------------
</cfloop>

</cfthread>
</cfloop>

Lastly JOIN the threads

<cfloop index="intGet" from="0" to="9" step="1">
<cfthread action="join" name="objGet#intGet#" />
</cfloop>

you would be able to save a lot of time, also you can use "thread" scopes to assign values and then do the calculations.

Tuesday, March 24, 2009

Coldfusion Query to List

Well, I was looking for a simple function which could give me the query results in form of a list, I only had one Column Selected

<cfquery name="get_data" datasource="testDsn">
select date from table1
</cfquery>

what is was looking for was just I can do <cfset list = QueryToList(get_data.date)> and I found that there is already a coldfusion function "valueList" to do the same. So to get the list from above you can simply do

<cfset list = valueList(get_data.date)>

really helpful.