I am using cfquery loop to run a stored procedure 1500 times. Will this crash Coldfusion?

I have tested this functionality for up to 76 groups. Tomorrow we will be testing to see if the function and stored procedure can be handled for 1500 groups. It took 25 minutes to run 76 groups in ColdFusion, so I should probably expect that it will take about 8 hours to complete the 1500 group test. Attached is my code. I would like to know if I can successfully run the process currently without crashing ColdFusion.
<cffunction name="getBatch" access="remote" returntype="query">
        <cfquery name="getFiles" datasource="ob">
            Select trackingNumber, GroupNo, GroupNameLong, SheildServiceKeyReg,  
            SheildServiceKey65, CrossServiceKeyReg, CrossServiceKey65, OBI_BAAG_ASC 
            From OB....
        </cfquery>
        
        <cfoutput>calling batch process<br/></cfoutput>
                           
        <cfloop query="getFiles">
        <cfoutput>running batch process<br/><br/></cfoutput>
           <cfinvoke component="batchService" method="decisionProcessBatch">
                <cfinvokeargument name="GroupNumber" value="#getFiles.GroupNo#">
                <cfinvokeargument name="Shield" value="#getFiles.SheildServiceKeyReg#">
                <cfinvokeargument name="Shield65" value="#getFiles.SheildServiceKey65#">
                <cfinvokeargument name="Cross" value="#getFiles.CrossServiceKeyReg#">
                <cfinvokeargument name="Cross65" value="#getFiles.CrossServiceKey65#">
                <cfinvokeargument name="OBI_BAAG_ASC" value="#getFiles.OBI_BAAG_ASC#">
                <cfinvokeargument name="GroupNameLong" value="#getFiles.groupnamelong#">
           </cfinvoke>
        </cfloop>
        
		<cfoutput>calling create oracle table<br/><br/></cfoutput>
        <cfinvoke component="oracleService" method="createOracleTable">
        <cfoutput>calling create oracle file<br/><br/></cfoutput>
        <cfinvoke component="oracleService" method="getOracleFieldsAndCreateFile">
        
        <cfreturn getFiles/>
    </cffunction>
    
    <!--- batch decision process function--->
    
    <cffunction name="decisionProcessBatch" access="remote" returntype="void">
		<cfargument name="GroupNumber" default="00..." required="yes" type="string"/>
		<cfargument name="Shield" default="S1..." required="yes" type="string"/>
		<cfargument name="Shield65" default="S1..." required="yes" type="string"/>
        <cfargument name="Cross" default="C1..." required="yes" type="string"/>
        <cfargument name="Cross65" default="C1...." required="yes" type="string"/>
        <cfargument name="OBI_BAAG_ASC" default="OB..." required="no" type="string"/>
        <cfargument name="GroupNameLong" default="MADA/...." required="yes" type="string">
        
        <cfstoredproc procedure="DBO.DECISION_PROCESS..." datasource="ob">
			<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.GroupNumber#"/>
			<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.Shield#"/>
			<cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.Shield65#"/>
            <cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.Cross#"/>
            <cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.Cross65#"/>
		</cfstoredproc>
        
        <!--- get forms and mnemonic for pdf links --->
        
        <cfinvoke component="batchService" method="getFormsAndMnemonic" returnvariable="getFormsAndMnemonicQuery">
                
        <cfset formArray = arraynew(1)>
        <cfset total_formMnem_records=getFormsAndMnemonicQuery.recordcount>
        
        <cfloop index="Counter" from="1" to="#total_formMnem_records#">
        	<cfset formArray[Counter] = "<li><a href='/obidoc..." & #getFormsAndMnemonicQuery.formnumber[Counter]# & ".pdf' target='_blank'>" & #getFormsAndMnemonicQuery.formnumber[Counter]# & "-" & #getFormsAndMnemonicQuery.MnemonicExpanded[Counter]# &"</a></li>"& Chr(10)>
         </cfloop>
         
         <cfset formMnemString = arraytolist(formArray,"")>
         
         <!--- get html lanuages --->
                  
		 <cfinvoke component="createFileService" method="getStandardRequestLanguageBatch" returnvariable="includesResult">
         
         <!---<cfdump var="#includesResult#">--->
         
         <cfset htmlLanguage = arraynew(1)>
		 <cfset includes = arraynew(1)>
         <cfset baagIncludes = arraynew(1)>
         
         <cfset LOB = #includesResult.LOB#>
                  
         <!---<cfoutput>running process. <strong>group:</strong> #arguments.GroupNumber# <strong>OBI_BAAG_ASC:</strong> #OBI_BAAG_ASC# <strong>LOB:</strong> #LOB# </cfoutput> --->
         
         <cfset total_language_records=includesResult.recordcount>
         
         <cfloop index="Counter" from="1" to="#total_language_records#">
         		<cfset htmlLanguage[Counter] = #includesResult.HTMLLanguage[Counter]# & Chr(10)>
				<cfset includes[Counter] = #includesResult.ObjectInclude[Counter]# & Chr(10)>
				<cfset baagIncludes[Counter] = #includesResult.BAAG_Include[Counter]# & Chr(10)>
         </cfloop>
                  
         <cfset htmlLanguageString = arraytolist(htmlLanguage,"")>
         <cfset includeString = arraytolist(includes,"")>
         <cfset baagIncludesString = arraytolist(baagIncludes,"")>
         
         <cfoutput><strong>LOB:</strong> #LOB#<br/></cfoutput>
         <cfoutput><strong>OBI_BAAG_ASC:</strong> #OBI_BAAG_ASC#<br/></cfoutput>
                           
         <!---get headers--->
         
         <cfinvoke component="batchService" method="createTestHeaderFile" returnvariable="headerResults">
         
         <cfset ascHTMLHeader = #headerResults.ascHTMLHeader#>
         <cfset ascSHTMLHeader = #headerResults.ascSHTMLHeader#>
         <cfset baagHeader = #headerResults.baagHeader#>
         <cfset obiHeader = #headerResults.obiHeader#>
         
         <cfset ascHTMLHeader = replace(#ascHTMLHeader#,"GroupNameLong",#GroupNameLong#)>
         <cfset ascHTMLHeader = replace(#ascHTMLHeader#,"GroupNumber",#GroupNumber#)>
         <cfset ascSHTMLHeader = replace(#ascSHTMLHeader#,"GroupNameLong",#GroupNameLong#)>
         <cfset ascSHTMLHeader = replace(#ascSHTMLHeader#,"GroupNumber",#GroupNumber#)>
         <cfset baagHeader = replace(#baagHeader#,"GroupNameLong",#GroupNameLong#)>
         <cfset baagHeader = replace(#baagHeader#,"GroupNumber",#GroupNumber#)>
         <cfset obiHeader = replace(#obiHeader#,"GroupNameLong",#GroupNameLong#)>
         <cfset obiHeader = replace(#obiHeader#,"GroupNumber",#GroupNumber#)>
         
         <!---create files--->
         
         <cfset lenOBI = len(#OBI_BAAG_ASC#)>
         
         <cfoutput>total_language_records: #total_language_records#<br/></cfoutput>
         <cfoutput>lenOBI: #lenOBI#<br/></cfoutput>
         
         <cfif #total_language_records# gt 0>
			 
			 <cfif #trim(OBI_BAAG_ASC)# IS "OBI">
             <cfoutput>creating obi file<br/><br/></cfoutput>
                <!---<cfoutput>create obi file<br/></cfoutput>--->
                <cfinvoke component="createFileService" method="createOBIFile">
                    <cfinvokeargument name="Includes" value="#includeString#">
                    <cfinvokeargument name="FileName" value="#GroupNumber#">
                    <cfinvokeargument name="header" value="#obiHeader#">
                    <cfinvokeargument name="lob" value="#LOB#">
                </cfinvoke>
                
                <cfinvoke component="createFileService" method="createHTMLFile">
                    <cfinvokeargument name="forms" value="#formMnemString#">
                    <cfinvokeargument name="FileName" value="#GroupNumber#">
                    <cfinvokeargument name="groupName" value="#GroupNameLong#">
                    <cfinvokeargument name="lob" value="#LOB#">
                </cfinvoke>
             </cfif>
             
             <cfif #trim(OBI_BAAG_ASC)# is "ASC">
                <!---<cfoutput>create asc file</cfoutput>--->
                <cfinvoke component="createFileService" method="createAscSHTMLFile">
                        <cfinvokeargument name="Includes" value="#htmlLanguageString#">
                        <cfinvokeargument name="FileName" value="#GroupNumber#">
                        <cfinvokeargument name="header" value="#ascSHTMLHeader#">
                </cfinvoke>
             
                <cfinvoke component="createFileService" method="createAscHTMLFile">
                        <cfinvokeargument name="forms" value="#formMnemString#">
                        <cfinvokeargument name="FileName" value="#GroupNumber#">
                        <cfinvokeargument name="groupName" value="#GroupNameLong#">
                        <cfinvokeargument name="header" value="#ascHTMLHeader#">
                </cfinvoke>
             </cfif>
             
             <cfif #trim(OBI_BAAG_ASC)# is "BAAG">
             <cfoutput>creating baag file<br/><br/></cfoutput>
                <!---<cfoutput>create baag file</cfoutput>--->
                <cfinvoke component="createFileService" method="createBaagCFMFile">
                        <cfinvokeargument name="Includes" value="#baagIncludesString#">
                        <cfinvokeargument name="FileName" value="#GroupNumber#">
                        <cfinvokeargument name="header" value="#baagHeader#">
                </cfinvoke>
             
                <cfinvoke component="createFileService" method="createBaagHTMLFile">
                        <cfinvokeargument name="forms" value="#formMnemString#">
                        <cfinvokeargument name="FileName" value="#GroupNumber#">
                        <cfinvokeargument name="groupName" value="#GroupNameLong#">
                </cfinvoke>
             </cfif>
         
		 </cfif>
   </cffunction>

Open in new window

rarid122481Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

srikanthmadishettiCommented:
My suggestion would be create multiple threads using cfthread first take the query object and run the 5 to6 different loops with each loop running in different threads .

U can run different loop of same query object by using startrow and endrow

Let me know if you need any more info
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
silvera21Commented:
I have had import processes that took ~5-6 hours or more. It will not crash the server, if the process is written well.  Set a really high timeout though.

<cfsetting requesttimeout="BIGNUMBER" />

Luckily I have a server monitor (fusion reactor) so I can tell what is going on at any point during the process. I highly recommend it if you run a lot of large batch processes.
0
srikanthmadishettiCommented:
When u create a thread its a asynchronous process there is no problem of time out at all ,  and what you want to do is a job , If you want to monitor You can just write a log to a file after certain define steps and at the end of the job just send out an email with that log file  by this U don't even worry about time outs.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

_agx_Commented:
The real question is why on earth do you need to run a stored procedure 1500 times ...?
0
srikanthmadishettiCommented:
Good Question agx , Can't you push every thing to back end it self .
0
ThutM0seCommented:
As long as you do not start creating dead locked threads and clogging up the request que you should be fine. I've had rogue requests that run for 24+ hrs, as long as they don't cause a memory leak forcing the heap to commit suicide and there are always threads available you should really only see a slow down in request execution times.

Using CFthread would probably speed up your application but if its going to run for a long period of time regardless it may be better to have the request be low impact and not use as much cpu. Using cfthreads will cause more cpu/resource usage than without using them. That way you can still process other requests without seeing a significant slow down. Just food for thought i guess.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.