ColdFusion Optimization Question

Hello, I am a database developer but I've been given a task to optimize a table in our database that is used in a cold fusion application page.  The complaint was the cold fusion process was taking too long.  I have looked at this from a database perspective and the database side is running quite speedily.  

In looking at the cold fusion code, it seems what is slowing this process down is how the code was written?  I'm not a cold fusion developer but the hang up to me seems to be the way that the code is looping through each record of the dataset.

It basically gets dataset records from our SQL Server that meet the criteria its looking for and then loops through each of those records and tries to run either an insert or an update statement in an Oracle database.  If the statement fails, it then updates the SQL server with failure information.   If it succeeds, it updates the SQL Server table with posted dates.  There could be anywhere between 1 record and 500+ records returned in the dataset that it needs to loop through.

I'm wondering if there is some way to get it to try to run the statements in bulk (without doing the loop) and then do a check to see which statements failed?  Would that be possible?

Any optimization help here would be appreciated. :)

<CFQUERY NAME="GetQueueRequests" DATASOURCE="#SQLServerDBDSN#">
select issue_num
from SQLServerDB_liw_queue
where date_posted is null
group by issue_num
order by min(id)
</CFQUERY>

<CFSET get_date = #Now()#>

<CFIF #GetQueueRequests.RecordCount# GT 0>
    <CFLOOP query="GetQueueRequests">

        <CFQUERY NAME="GetQueueRecords" DATASOURCE="#SQLServerDBDSN#">
        select distinct id, oracle_statement, issue_num, mcn_niin, nslin, liw_table_name, liw_column_name1, liw_column_name2
        from SQLServerDB_liw_queue
        where date_posted is null
        and issue_num = #issue_num#
        order by id 
        </CFQUERY>

        <CFIF #GetQueueRecords.RecordCount# GT 0>
            <CFLOOP query="GetQueueRecords">
                <CFTRY>
                    <CFSET page_name = 'cftasks/SQLServerDB_LIW_Queue'>
                    <CFSET query_name = 'GetQueueRecords'>

					<CFIF #left(oracle_statement,6)# is "update">
                        <CFQUERY NAME="CheckLIW" DATASOURCE="lidb_nslin">
                        select #liw_column_name1#
                        from #liw_table_name#
                        where #trim(liw_column_name1)# = '<CFIF #trim(liw_column_name1)# is "lin">#nslin#<CFELSE>#mcn_niin#</CFIF>'
                        <CFIF #trim(liw_column_name2)# is not "">and #trim(liw_column_name2)# = '#nslin#'</CFIF>
                        <CFIF #trim(liw_table_name)# is not "nslin">and status = 'C'</CFIF>
                        </CFQUERY>
					</CFIF>

					<CFIF ((#IsDefined("CheckLIW.RecordCount")# AND #CheckLIW.RecordCount# GT 0) OR (#left(oracle_statement,6)# is "insert"))>
                        <CFQUERY NAME="RunOracleStatement" DATASOURCE="lidb_nslin">
                          #replace(oracle_statement, "''", "'", "ALL")#
                        </CFQUERY>
					<CFELSE>
                        <CFQUERY NAME="UpdateDatePosted" DATASOURCE="#SQLServerDBDSN#">
                        update SQLServerDB_liw_queue
                        set date_failed = #get_date#,
						date_executed = getdate(),
						notes = '<CFIF #liw_column_name1# is "substr(mcn,5,9)">MCN<CFELSE>#UCase(liw_column_name1)#</CFIF> <CFIF #trim(liw_column_name1)# is "lin">#nslin#<CFELSE>#mcn_niin#</CFIF> record does not exist in LIW table #UCase(liw_table_name)#.'
                        where issue_num = #issue_num#
						and id = #id#
                        and date_posted is null
                        </CFQUERY>
					</CFIF>
            
                    <CFCATCH TYPE="Database">
                        <CFQUERY NAME="UpdateDatePosted" DATASOURCE="#SQLServerDBDSN#">
                        update SQLServerDB_liw_queue
                        set date_failed = #get_date#,
						date_executed = getdate(),
						notes = '#cfcatch.message# - #cfcatch.detail#'
                        where issue_num = #issue_num#
						and id = #id#
                        and date_posted is null
                        </CFQUERY>
                    </CFCATCH>
                </CFTRY>
            
                <CFQUERY NAME="RequestFailed" DATASOURCE="#SQLServerDBDSN#">
                select date_failed
                from SQLServerDB_liw_queue
                where id = #id#
                </CFQUERY>

				<CFIF #RequestFailed.date_failed# is not #get_date#>
                    <CFQUERY NAME="UpdateDatePosted" DATASOURCE="#SQLServerDBDSN#">
                    update SQLServerDB_liw_queue
                    set date_posted = #get_date#,
					date_executed = getdate()
                    where id = #id#
                    and date_posted is null
                    </CFQUERY>
				</CFIF>        
            </CFLOOP>
        </CFIF>
	</CFLOOP>

    <CFQUERY NAME="GetFailedRequests" DATASOURCE="#SQLServerDBDSN#">
    select distinct display_issue_num, notes
    from SQLServerDB_liw_queue a, request_change b
    where a.issue_num = b.issue_num
	and date_failed = #get_date#
	order by display_issue_num
    </CFQUERY>

	<CFIF #GetFailedRequests.RecordCount# GT 0>
        <CFMAIL to="#admin_email_addresses#" from="#SQLServerDBHelpDeskEmail#" subject="Requests Failed to Update LIW" TYPE="HTML">
        <CFINCLUDE template="/SQLServerDB/Common/td_input_newdesign.css">
        <TABLE SUMMARY="" BORDER="0" CELLPADDING="4" CELLSPACING="4">
            <TR>
                <TD CLASS="text_main" COLSPAN="2">The following requests failed to update the LIW database.</TD>
            </TR>
            <TR><TD COLSPAN="2"><HR COLOR="Black"></TD></TR>
            <TR>
                <TH CLASS="title_main" align="left">Request Number</TH>
                <TH CLASS="title_main" align="left">Reason</TH>
            </TR>
			<CFLOOP QUERY="GetFailedRequests">
                <TR>
                    <TD CLASS="text_main" align="left"><cfoutput>#trim(display_issue_num)#</cfoutput></TD>
                    <TD CLASS="text_main" align="left"><cfoutput>#trim(notes)#</cfoutput></TD>
                </TR>
			</CFLOOP>
        </TABLE>
       </CFMAIL>
	</CFIF>

</CFIF>

Done.

Select allOpen in new window