I have the following stored procedure running against an ExtractedMessages table which might contain up to 100M records (100,000,000).
For the purpose of my application, this stored procedure should run in less than one second
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 can not be more than 1500
Thanks Everyone for your kind help I was able to identify the problem, after all it was a memory management issue on the server that is running SQL server. I have increased the dedicated memory of the server. And the issue has been resolved. The SP works smoothly now
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments