Coldfusion Update SQL statement

Question

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>

what takes 2 minutes, just the cfloop or the entire page that uploads the spreadsheet(?), parses the spreadsheet(?), and the cfloop? if the page is taking 2 minutes you should use gettickcount around each different process and see where the delay is. — genericHCU, Feb 07 '13 at 16:44
As others have mentioned, this is really not a job for ColdFusion. Bulk updates are better handled by database tools. Instead of executing many individual statements, one option is importing the data into a temp table. Then validate the data. Finally JOIN to the temp table to do a single UPDATE. — Leigh, Feb 07 '13 at 18:42

score 3 · Answer 1 · edited May 23 '17 at 12:04

3

Update Unfortunately due to some new information previously omitted by the OP, this solution is not valid.

Original Answer

I don't think you need to use or even SHOULD use ColdFusion to do this. This would make 600 database calls and is probably where a lot of your time is. At the very most you should only need CF to send the where clause to tell the query what 600 rows you need to update.

You can update a table in oracle from another table using straight SQL. See if this older SO post helps you. Oracle SQL: Update a table with data from another table

In the event something were to happen to this link, the gist of the highest rated answer is to do:

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id 
     AND  -- your where clause from your first cfquery here
 )

edited May 23 '17 at 12:04

Community

1
1

answered Feb 07 '13 at 15:18

genericHCU

4,394
2
22
34

I wouldn't be so quick to exclude ColdFusion. It is probably possible to do this with a single cfquery, hold the loop. – Dan Bracuk Feb 07 '13 at 15:43
This would be the sql in the single cfquery if it needed to run from CF. The last AND statement would probably include the where clause and cf parameters used in qry1 to generate the initial filter. Hence my statement `At the very most you should only need CF to send the where clause to tell the query what 600 rows you need to update.` – genericHCU Feb 07 '13 at 16:09
SHAME ON YOU!!! not only did you not mention that but you eluded that the tables were in the same database by saying `does a table update referencing data from another table` what you meant to say was `from another DATA SOURCE` – genericHCU Feb 07 '13 at 16:14
Fix your question to get a good answer. Include your version of oracle. – genericHCU Feb 07 '13 at 16:17

score 0 · Accepted Answer · answered Feb 07 '13 at 17:46

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.

Coldfusion Update SQL statement

2 Answers2