SQL Server存储过程优化
我有以下存储过程运行在ExtractedMessages
表中,该表可能包含高达100M记录(100,000,000)。SQL Server存储过程优化
我的应用程序的目的,这个存储过程应该运行在不到一个秒钟
CREATE PROCEDURE GetNextMessages
@taskId bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ci INT
DECLARE @cr INT
SELECT
@ci = CurrentIndex, @cr = CurrentResources
FROM
ExtractedTasks
WHERE
Id = @taskId
UPDATE ExtractedTasks
SET CurrentIndex = @ci + @cr
WHERE Id = @taskId
SELECT *
FROM ExtractedMessages
WHERE TaskId = @taskId
ORDER BY Id
OFFSET @ci ROWS
FETCH NEXT @cr ROWS ONLY
END
NB:CR不能超过1500
感谢大家的帮助 我能够发现问题,毕竟这是运行SQL Server的服务器上的内存管理问题。我增加了服务器的专用内存。问题已解决。现在SP工作顺利
而不是查询表格两次,你可以做如下:
DECLARE @ci INT
DECLARE @cr INT
DECLARE @T TABLE (
ci INT
, cr INT
);
UPDATE ExtractedTasks
SET CurrentIndex = CurrentIndex + CurrentResources
OUTPUT DELETED.CurrentIndex, DELETED.CurrentResources
INTO @T (ci, cr)
WHERE Id = @taskId;
SELECT
@ci = ci, @cr = cr
FROM
@T
SELECT *
FROM ExtractedMessages
WHERE TaskId = @taskId
ORDER BY Id
OFFSET @ci ROWS
FETCH NEXT @cr ROWS ONLY
这里的关键是OUTPUT子句。它会将删除的记录(更新前的值)插入表变量中。这意味着你用一个表替换多个选择。除了正确的索引,我没有看到任何改进。
此外,请确保您只选择所需的列,而不是所有的列。列出所需的精确列通常是一种很好的做法。
+1上确实是一个非常奇怪的分页要求但是,现在我可以重现该问题, 表上的简单计数(*)需要6秒 –
附加执行计划,否则不存在我们可以帮助很多。 –
请注意,_ExtractedMessages_表已在_Id_和_TaskId_ –
上编入索引您有奇怪的分页逻辑。 –
你真的需要结果中的所有列吗?如果不是,则删除'*'并只添加所需的列 –