Efficientest stored procedure
-
We were investigating an issue that occured in a vendor's product database a while ago that caused scheduled jobs to stop working nearly every midnight, like clockwork. One of the first things we did to figure out the cause was to take a look at other jobs running at or around midnight in that database. It was then that we discovered the efficientest stored procedure:
CREATE PROCEDURE dbo.Efficientest_Stored_Procedure AS
DECLARE @PurgeDate datetime
SET @PurgeDate = GETDATE() - 1 -- Delete records more than a day oldDECLARE @ID int
DECLARE @time datetime
DECLARE @status intBEGIN TRANSACTION
DECLARE Efficient_Cursor CURSOR LOCAL FOR SELECT ID, time, status FROM Table
OPEN Efficient_Cursor
FETCH NEXT FROM Efficient_Cursor INTO @ID, @time, @status
WHILE @@fetch_status = 0
BEGIN
IF EXISTS( SELECT TOP 1 meas_pt FROM Table WHERE status = 1 )
BEGIN
DELETE Table
WHERE time <= @PurgeDate AND (status = 1 OR status = 2)
END
FETCH NEXT FROM Efficient_Cursor INTO @ID, @time, @status
END
CLOSE Efficient_Cursor
DEALLOCATE Efficient_CursorIF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN
END
GO"Table" is typically about 15000 records, with about 2000-4000 matching "time <= GETDATE() - 1" every midnight. All of those are deleted on the first execution of the WHILE loop, and then the cursor fetches the remaining ~12000 records and executes a no-op DELETE for each one, individually, because the IF EXISTS statement will always (and I mean always) return true.
Needless to say, once we replaced this with a simple DELETE ... WHERE statement every started working properly again. Instead of taking nearly three minutes to execute it only took one second.
-
Looks like you found the Most_Efficientest_Stored_Procedure.
-
-
How very picky of you. I mean that when it happened it happened always at 19 seconds after midnight, but it didn't happen every night. I can't really think of a terse way of saying that. I am open to suggestions.
-
@nickfitz said:
@Welbog said:
...nearly every midnight, like clockwork
Weird clockwork...
That's why we use quartz watches and atomic clocks nowadays.
-
@Welbog said:
...
...
CREATE PROCEDURE dbo.Efficientest_Stored_Procedure ASDECLARE Efficient_Cursor CURSOR LOCAL FOR SELECT ID, time, status FROM Table
OPEN Efficient_Cursor
FETCH NEXT FROM Efficient_Cursor INTO @ID, @time, @status
...FETCH NEXT FROM Efficient_Cursor INTO @ID, @time, @status
...
CLOSE Efficient_Cursor
DEALLOCATE Efficient_CursorGotta say it - he wasn't very efficient. I mean, it takes longer to type "efficient" than to type "effing" or even "eff".
Hah.
-
@Welbog said:
How very picky of you. I mean that when it happened it happened always at 19 seconds after midnight, but it didn't happen every night. I can't really think of a terse way of saying that. I am open to suggestions.
"nearly everynight just after midnight"
But you can't say "like clockwork" if it didn't happen every night (if that is your definition of clockwork, I don't want any of your clocks)
-
What? You mean your business doesn't operate under the assumption that there are 48 hours in a day?
-
@chrismcb said:
Now, now, it needn't, like, be, like, EXACTLY like clockwork, just, like, sort of vaguely similar. like, y'dig?@Welbog said:
How very picky of you. I mean that when it happened it happened always at 19 seconds after midnight, but it didn't happen every night. I can't really think of a terse way of saying that. I am open to suggestions.
"nearly everynight just after midnight"
But you can't say "like clockwork" if it didn't happen every night (if that is your definition of clockwork, I don't want any of your clocks)
-
@Otterdam said:
@nickfitz said:
@Welbog said:
...nearly every midnight, like clockwork
Weird clockwork...
That's why we use quartz watches and atomic clocks nowadays.
Damn SQL clocks kept running slow.
Deleted all my data, too.
-
Reminds me of an SP we had at work. It was supposed to run every minute to delete data, but was taking 8 minutes to run and causing huge locking and timeout issues on data being inserted.
A quick rewrite made it execute in 1s....