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.

3 comments:

Yoosaf Abdulla said...

But why made you use 10 threads..like is there some logic to determine that value?

Brijesh said...

@Yoosaf - You will find a setting in CF admin which says "Maximum number of threads available for CFTHREAD", I had 10 in there which is also the suggested value, so I used 10.

Yoosaf Abdulla said...

Hey thanks Brijesh.
Now I am having a nested query loop. I am using the cfthread in the inner loop. But ia getting the error saying that objGet0 already exists. Now thats because the outter loop is tring to create thread in its second loop. So is thre some way I can kill the threads created in the inner loop each time the inner loop is exited. I tried this link

http://www.websolete.com/posts/programmatically-kill-any-cfthread-spawned-from-anywhere

But error exists.

any help is much appreciated.