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
- Measure: calling the sproc a thousand times takes 6.2 seconds
- 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
- 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.
- Measure: calling the sproc a thousand times takes 2.7 seconds. A 229% improvement!
- 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
- Measure: calling the sproc a thousand times takes 1.2 seconds. A 516% improvement over the original measurement!
- 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
- 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
- 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:
- Don’t design without checking the performance consequences
- Check the design, check the indexes and only then tweak your logic
- Measure before and after. Don’t trust your experience or hunches: measure.