低速SQL查询
低速SQL查询
提问于 2014-08-09 13:53:58
我在Server中有16000行，我想将文档号物理地分配给记录，并且使用下面的代码，当我的记录是8000时，它可以工作得很好，但是当记录增加到16000时，它会给我一个超时错误，请帮助我提高我的查询的性能？
declare @NewDoc int;
set @NewDoc=0
declare t1_cursor cursor dynamic for select documentheaderid,documentnumber from Accounting.DocumentHeader where FinancialPeriodFK=@FinancialPeriodFK and Date>=@FromDate and Date<=@ToDate order by Date
open t1_cursor
fetch next from t1_cursor
while(@@fetch_status=0)
begin
set @NewDoc=@NewDoc+1;
update Accounting.DocumentHeader set DocumentNumber=@NewDoc where current of t1_cursor
fetch next from t1_cursor
end
close t1_cursor
deallocate t1_cursor回答 1
Stack Overflow用户
回答已采纳
发布于 2014-08-09 14:31:25
尝试这样的方法--避免任何row-by-agonizing-row处理(如游标或while循环)，避免：
-- creates a temporary "inline" view of the data you're interested in and
-- assigns each row a consecutive number
;WITH DataToUpdate AS
(
SELECT
DocumentHeaderId,
DocumentNumber,
NewDocNum = ROW_NUMBER() OVER(ORDER BY [Date])
FROM
Accounting.DocumentHeader
WHERE
FinancialPeriodFK = @FinancialPeriodFK
AND Date >= @FromDate
AND Date <= @ToDate
)
-- update the base table to make use of that new document number
UPDATE dh
SET dh.DocumentNumber = dtu.NewDocNum
FROM Accounting.DocumentHeader dh
INNER JOIN DataToUpdate dtu ON dh.DocumentHeaderId = dtu.DocumentHeaderId这应该是显着地加快您的处理时间的！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25219283
复制相关文章
相似问题
腾讯云开发者
