如何更快地更新具有数百万条记录的表？ [英] How to update a table having millions of records faster?

问题描述

我必须更新一张有数百万条记录的表格。



我使用了以下查询。



1.我已将pkeys更新到临时表中

I have to update a table which has million of records.

I have used the below query.

1.I have put the pkeys to be updated in a temp table

select * into tempPkey  
  from Prd WHERE status<>'d' and pKey NOT IN (SELECT PrdPKey FROM PrdGroupRel where status <>'d')

2.然后我使用以下更新命令更新。

2. Then i have use below update command to update.

SET NOCOUNT ON
DECLARE    @PKey  AS Varchar(16) ;

DECLARE UPDATE_CURSOR CURSOR FOR

SELECT pkey FROM tempPkey FOR UPDATE 

OPEN UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
INTO @PKey

WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN Transaction



Update Cndcpmain set status ='d' ,Hostsource='INC7892752_30Oct2018'  
where mergedkey like '%'+@PKey+'%'  and CndcpMetapkey='00100000007gu6ib' and salesorg='MS01' and status<>'d'

Print 'Pkey Update completed :' + @Pkey+' ' + convert(varchar(50),Getdate() )

COMMIT Transaction  
    FETCH NEXT FROM UPDATE_CURSOR
    INTO @PKey
END

CLOSE UPDATE_CURSOR
DEALLOCATE UPDATE_CURSOR

SET NOCOUNT OFF

我在Cndcpmain上禁用了触发器和索引表。但只有14-15个pkeys在1分钟内得到更新。总共有18916个pkeys需要更新，预计时间为20小时。我们可以加快它吗？



此外，我无法为已经更新的记录编写选择查询，以便我可以在下次运行时排除更新的记录。你可以帮忙吗



我尝试过：



[见上面的代码]

I have disabled triggers and indexes on Cndcpmain table. but only 14-15 pkeys are getting updated in 1 min. There are total 18916 pkeys to update and expected time is 20 hrs. Can we expedite it?

Also I unable to write a select query for the records which got already updated ,so that i can exclude the updated records in next run. could you please help

What I have tried:

[See code above]

推荐答案

CURSOR可能是在SQL中做事最慢的方式，只有在真正需要的时候才能使用...

你似乎用一个简单的UPDATE语句更好地处理了你的情况...

这样的事情可以解决这个问题：

CURSOR is probably the slowest way to do things in SQL and should be used only when there is a real need...
You case seems to me better handled with a simple UPDATE statement...
Something like this would do the trick:

UPDATE CNDCPMAIN 
SET 
	CNDCPMAIN.STATUS = 'D',
	CNDCPMAIN.HOSTSOURCE = 'INC7892752_30OCT2018'  
FROM PRD 
WHERE
	PRD.STATUS <> 'D' AND 
	PRD.PKEY NOT IN (SELECT PRDGROUPREL.PRDPKEY FROM PRDGROUPREL WHERE PRDGROUPREL.STATUS <> 'D') AND
	CNDCPMAIN.MERGEDKEY LIKE '%' + PRD.PKEY + '%'  AND 
	CNDCPMAIN.CNDCPMETAPKEY = '00100000007GU6IB' AND 
	CNDCPMAIN.SALESORG = 'MS01' AND 
	CNDCPMAIN.STATUS <> 'D'

这篇关于如何更快地更新具有数百万条记录的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！