Houston, we have a problem...

Thanks for the help in advance.

I have been working on a project where i need to update some of the 150000 records depending on bunch of criterias. I figured it out but my problem is it takes way too long, approximately 20 minutes. Not only that, I also have to repeat the same action three times for 3 tables.


I got two options:

First, i can get everything with three queries then <cfoutput> them and update the tables.
something like

<cfloop index="mm" list="#list1#">

<cfloop from="1" to="30" index="j" step="1"><!--- date loop --->

<cfset newDate = DateFormat(DateAdd('d', -j, myDate), 'MM/DD/YYYY')>

<cfquery name="getDp1" dbtype="query">
SELECT * FROM ….
WHERE ….=<cfqueryparam value="#newDate#" cfsqltype="cf_sql_date">
AND … = <cfqueryparam value="#mm#" cfsqltype="cf_sql_integer">
</cfquery>
<cfquery name="getSC" dbtype="query">
SELECT SC1, SC2, SC3, SC4
FROM spotCount
WHERE … = <cfqueryparam value="#newDate#" cfsqltype="cf_sql_date">
AND … = <cfqueryparam value="#mm#" cfsqltype="cf_sql_integer">
</cfquery>

<cfquery name="getPerc" dbtype="query">
SELECT * FROM … WHERE … = <cfqueryparam value="#mm# " cfsqltype="cf_sql_integer">
</cfquery>

<cfset num1 = ROUND(getSC.SC1 * getPerc.dp1)>
<cfset num2 = ROUND(getSC.SC2 * getPerc.dp2)>
<cfset num3 = Round(getSC.SC3 * getPerc.dp3)>
<cfset num4 = Round(getSC.SC4 * getPerc.dp4)>

<cfoutput query="getDP1">
<cfset id = Evaluate("getDP1.#myID#")>
<cfif TRIM(….) EQ ….>
<cfif getDP1.CurrentRow MOD 5 EQ 0 AND (…. GE 0 AND … LT 21600)>
<cfloop from="1" to="#num1#" step="1" index="i">
update the table…
</cfloop>
<cfelseif getDP1.CurrentRow MOD 5 EQ 0 AND (…. GE 21600 AND ….. LT 43200)>
<cfloop from="1" to="#num2#" step="1" index="i">
update the table….
</cfloop>
<cfelseif getDP1.CurrentRow MOD 5 EQ 0 AND (…. GE 43200 AND ….. LT 64800)>
<cfloop from="1" to="#num3#" step="1" index="i">
update the table
</cfloop>
<cfelseif getDP1.CurrentRow MOD 5 EQ 0 AND (…. GE 64800 AND …. LT 86400)>
<cfloop from="1" to="#num4#" step="1" index="i">
update the table
</cfloop>
<cfelse>
do nothing
</cfif>
</cfif>

</cfoutput>

</cfloop> <!---date loop --->

</cfloop> <!--- first loop -?

the problem with this approach is it connects and disconnects to the SQL server million times.

second option was to make an array of the ids that i need to update. so instead of

cfloop from="1" to="#num4#" step="1" index="i">
update the table
</cfloop>

i said
<cfloop from="1" to="#num3#" step="1" index="i">
<cfset UpdateID[listPos] = #id#>
<cfset listPos = listPos + 1>
</cfloop>

and after all the loops are closed i did a UPDATE ... WHERE ..ID IN (...)

i will really appreciate any input to help me make this process faster. It will run once a week at Wednesday midnight.
by the way, i couldn't implement any stored procedure since my update is row dependent.
happy new year...

Hi FALCONSEYE!

Without knowing what your trying to do I wont be able to go into specific code. But an array sounds the most promosing. You can use exact field names rather tha SELECT *, instead of cfif's try including a template with cfswitch tags, you can also add some speed by using cfscript instead of cfset's, also Im almost positive you can use a combination of INNER JOIN and UNION ALL to reduce the cfqueries youve got going. If you tell me what exactly your script does I might be able to offer more specific help.