Simple optimizing pleasures

Sometimes you have an entertaining half an hour of good old simple tuning, for example this afternoon I had to consider this stored procedure (the names of the variables have been changed to protect the innocent): 

CREATE PROCEDURE COUNTER_VALUE (@TAB AS VARCHAR(50),@CNT INT OUTPUT)AS

DECLARE @VALUE INT

       SET NOCOUNT ON

       BEGIN TRANSACTION COUNTER

       UPDATE COUNTERS SET CUR_VALUE = CUR_VALUE + 1 WHERE TABLE_NAME = @TAB

       SELECT @VALUE = CUR_VALUE FROM COUNTERS WHERE TABLE_NAME = @TAB

       IF @@ERROR <> 0

       BEGIN

              ROLLBACK TRANSACTION COUNTER

              SELECT @CNT = -1    

              RETURN

       END

       ELSE

       BEGIN

              COMMIT TRANSACTION COUNTER

              SELECT @CNT = @VALUE      

              RETURN

       END

 Allegedly, this sproc is used to get successive numbers for many tables in the database. This database is suffering of performance problems and, as this sproc is used in many places, we should try and boost its performance. Let’s do it step by step:

1. Measure: calling the sproc a thousand times takes 6.2 seconds
2. Review your design: someone may point out that instead of using this sproc you could use an IDENTITY column. Certainly this would kill all problems with the sproc but in my case such a change would force changes in many places of the system: too risky. Conclusion: as you design and develop your system test for performance or else you may find that you are not able to apply tunings that are obvious
3. Check your indexes: it so happens that the COUNTERS table has a non-clustered index on the TABLE_NAME column (which is the primary key.) Given that the COUNTERS table has only a few dozen rows, the index shouldn’t matter that much but anyway I tried clusterizing the index.
4. Measure: calling the sproc a thousand times takes 2.7 seconds. A 229% improvement!
5. Check your SQL: why is it that I have to do a SELECT after the UPDATE? To get the new value of course, but we can collapse these two statement:

UPDATE COUNTERS SET CUR_VALUE = CUR_VALUE + 1 WHERE TABLE_NAME = @TAB

SELECT @VALUE = CUR_VALUE FROM COUNTER WHERE TABLE_NAME = @TAB

       Into this:

UPDATE COUNTERS SET @VALUE = CUR_VALUE = CUR_VALUE + 1 WHERE TABLE_NAME = @TAB

6. Measure: calling the sproc a thousand times takes 1.2 seconds. A 516% improvement over the original measurement!
7. Reconsider your SQL: now why is that we need an internal transaction. Furthermore, what can go wrong in the one and only SQL statement left? So lets simplify the sproc to this:

CREATE PROCEDURE sp_COUNTER_VALUE (@TAB as varchar(50),@CNT int OUTPUT)AS

       SET NOCOUNT ON

       UPDATE COUNTERS SET @CNT = CUR_VALUE = CUR_VALUE + 1 WHERE TABLE_NAME = @TAB

8. Measure: calling the sproc a thousand times takes 1.3 seconds. And actually it’s a little slower than the previous version, funny. After some tweaking I settle for this:

CREATE PROCEDURE sp_COUNTER_VALUE (@TAB as varchar(50),@CNT int OUTPUT)AS

DECLARE @VALUE INT

       SET NOCOUNT ON

       SET @VALUE = -1

       UPDATE COUNTERS SET @VALUE = CUR_VALUE = CUR_VALUE + 1 WHERE TABLE_NAME = @tab

       SELECT @CNT = @VALUE

9. Measure: calling the sproc a thousand times takes 1.2 seconds. At least I am back to my previous mark and the procedure is simpler.

Lessons learned:

1. Don’t design without checking the performance consequences
2. Check the design, check the indexes and only then tweak your logic
3. Measure before and after. Don’t trust your experience or hunches: measure.

 And that’s enough because we don’t want performance just for the sake of it. A 500% enhancement is already great and now it’s time to check other parts of the system.