Search code examples
sqloracle-databasecoldfusionoracle11gcoldfusion-9

Coldfusion Update SQL statement

I have the following query which does a table update referencing data from data read in using cfspreadsheet.(approx 600 rows)and is referneced as qry1. The SQL runs around 2 minutes to completion. Can I improve the SQL to improve the update timings? The products table has 1360000 rows in it. This example is updating 600 of those rows. Oracle version is 11g Coldfusion Version is 9.1

<cfloop from="1" to="#qry1.recordcount#" index="i">
    <cfquery name="qry2" datasource="db1">
        UPDATE PRODUCTS P
        SET P.QTY = <cfqueryparam value="#qry1.ITEM_NEW_INCOMING_QUOTE_QTY#" cfsqltype="cf_sql_numeric" />
        WHERE P.PRODUCT= <cfqueryparam value="#qry1.PRODUCT#" cfsqltype="cf_sql_varchar"  />
        AND EXISTS(
            SELECT QUOTE_ID
            FROM QUOTE Q
            WHERE P.QUOTE_ID = Q.QUOTE_ID
                AND Q.QUOTE_NUMBER = <cfqueryparam value="#Val(qry1.QUOTE_NUMBER)#" cfsqltype="cf_sql_numeric" /> 
            )
    </cfquery>
</cfloop>
Solution

  • 600 individual updates are going to take quite a while, no matter what. If you don't want the user to have to sit around and wait, you can use cfthread to do the updates and thank the user for his file on your main thread.

    Alternatively, you can simply upload the file and save it on your server somewhere. Then set up a scheduled job that looks for these files and processes them.

    Whatever you do, don't sacrifice good practices for speed. Since an uploaded file is user input, you have to validate the data you receive.