How to Update Multiple Queries in optimized way in

问题:

Please find the following code -

UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 1245789633 AND TENDOR_DATE = 2012-01-02;   
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 1235853861 AND TENDOR_DATE = 2011-02-22;     
UPDATE TENDORS SET TENDORS_VALUE = 0A0 WHERE ID = 2565854283 AND TENDOR_DATE = 2013-11-19;    
UPDATE TENDORS SET TENDORS_VALUE = 0A0 WHERE ID = 0565853935 AND TENDOR_DATE = 2012-02-02;    
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 5565854148 AND TENDOR_DATE = 2013-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850296 AND TENDOR_DATE = 2012-12-20;     
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850507 AND TENDOR_DATE = 2012-10-12;     
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565850644 AND TENDOR_DATE = 2012-09-02;    
UPDATE TENDORS SET TENDORS_VALUE = 480 WHERE ID = 4565851488 AND TENDOR_DATE = 2012-08-02;    
UPDATE TENDORS SET TENDORS_VALUE = 080 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = 4A0 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-06-02;    
UPDATE TENDORS SET TENDORS_VALUE = EB0 WHERE ID = 4610849238 AND TENDOR_DATE = 2012-05-02;    
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-04-02;     
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-03-02;    
UPDATE TENDORS SET TENDORS_VALUE = EA0 WHERE ID = 4610850154 AND TENDOR_DATE = 2012-12-02;     
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-08-02;     
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-07-02;    
UPDATE TENDORS SET TENDORS_VALUE = CA0 WHERE ID = 4610851768 AND TENDOR_DATE = 2012-06-02;    
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565853935 AND TENDOR_DATE = 2012-05-02;    
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565854146 AND TENDOR_DATE = 2009-04-02;     
UPDATE TENDORS SET TENDORS_VALUE = 8B0 WHERE ID = 4565854146 AND TENDOR_DATE = 2009-03-02;    
UPDATE TENDORS SET TENDORS_VALUE = 9B0 WHERE ID = 4565854283 AND TENDOR_DATE = 2010-02-02;    
UPDATE TENDORS SET TENDORS_VALUE = 9B0 WHERE ID = 4565854283 AND TENDOR_DATE = 2011-01-02;

for up to 1000 Update statements

When we are executing above query (i.e. Executing 1000 Update Queries) it is taking too much time. I want to boost the performance.

Which is best way to optimized it in terms of code and Time ? Any class like DB2BulkCopy for Update ? or Any Built in class for update in DB2 or SQL?

回答1:

How about this

WITH A(A,B,C) AS (VALUES
     ('080','1245789633','2012-01-02')   
    ,('080','1235853861','2011-02-22')     
    ,('0A0','2565854283','2013-11-19')    
    ,('0A0','0565853935','2012-02-02')    
    ,('080','5565854148','2013-07-02')    
    ,('480','4565850296','2012-12-20')     
    ,('480','4565850507','2012-10-12')     
    ,('480','4565850644','2012-09-02')    
    ,('480','4565851488','2012-08-02')    
    ,('080','4610849238','2012-07-02')    
    ,('4A0','4610849238','2012-06-02')    
    ,('EB0','4610849238','2012-05-02')    
    ,('EA0','4610850154','2012-04-02')     
    ,('EA0','4610850154','2012-03-02')    
    ,('EA0','4610850154','2012-12-02')     
    ,('CA0','4610851768','2012-08-02')     
    ,('CA0','4610851768','2012-07-02')    
    ,('CA0','4610851768','2012-06-02')    
    ,('8B0','4565853935','2012-05-02')    
    ,('8B0','4565854146','2009-04-02')     
    ,('8B0','4565854146','2009-03-02')    
    ,('9B0','4565854283','2010-02-02')    
    ,('9B0','4565854283','2011-01-02')
    )
SELECT COUNT(*) FROM NEW TABLE (
    UPDATE TENDORS
    SET TENDORS_VALUE = (SELECT A FROM A WHERE ID = B AND TENDOR_DATE = C)
    WHERE EXISTS        (SELECT A FROM A WHERE ID = B AND TENDOR_DATE = C)
)