我有一个遗留存储过程试图创建一个光标来遍历查询中的每一行.表现非常糟糕.然后我检查查询计划,大部分成本(> 47%)都在一个对象上[tempdb].[CWT_PrimaryKey]
.此对象由存储过程中创建的游标创建.不确定如何提高此案例的性能,因为在tempdb
SQL Server创建的此对象上无法执行任何操作.
存储过程中的伪代码如:
BEGIN TRY BEGIN TRANSACTION declare mycusorr local fast_forward for SELECT * From MyTab Where a=b; open mycusorr; fetch next from mycusorr into @v1, @v2, ...; while @@fetch_status = 0 begin --some query to check rules from different tables Update AnotherTab Set column=value where id = @v1; if (there is error) insert error to error user log table; End close mycusorr; deallocate mycusorr; COMMIT; END TRY BEGIN CATCH close mycusorr; deallocate mycusorr; SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage; ROLLBACK TRAN; END CATCH
没有主键MyTab
,但在条件中使用的列上创建了索引.
大约有10,000行Mytab
.运行存储过程需要3个多小时甚至没有完成.如果我从存储过程中删除事务,它将是快速的.
当我检查锁定时SP_lock
,在循环中具有update子句的表的键或页面上有超过1万个X或IX锁.
怎么样:
UPDATE t SET t.column = m.value FROM dbo.AnotherTab AS t INNER JOIN dbo.MyTab AS m ON t.id = ... no idea what the join criteria is because your cursor uses SELECT * WHERE m.a = m.b; -- I also don't think this is described well enough to guess
如果您提供真实代码而不是伪代码,您可以获得更多,更好,更好的答案.