如何在DB2中以优化的方式更新多个查询 [英] How to Update Multiple Queries in optimized way in DB2
本文介绍了如何在DB2中以优化的方式更新多个查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请找到以下代码-
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;
最多1000条更新语句
for up to 1000 Update statements
当我们执行以上查询(即执行1000个更新查询)时,这会花费太多时间。我想提高性能。
When we are executing above query (i.e. Executing 1000 Update Queries) it is taking too much time. I want to boost the performance.
哪种是在代码和时间方面进行优化的最佳方法?是否有像DB2BulkCopy这样的类可用于Update?还是任何用于在DB2或SQL中更新的内置类?
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?
推荐答案
如何解决
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)
)
这篇关于如何在DB2中以优化的方式更新多个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文