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 old

    DECLARE @ID int
    DECLARE @time datetime
    DECLARE @status int

    BEGIN 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_Cursor

    IF @@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.



  • @Welbog said:

    ...nearly every midnight, like clockwork

    Weird clockwork... 



  • 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 AS

    ...

    DECLARE 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_Cursor

    Gotta 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:

    @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)

    Now, now, it needn't, like, be, like, EXACTLY like clockwork, just, like, sort of vaguely similar. like, y'dig?


  • @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.... 


Log in to reply