Trigger Happy Rotating Header Image

June, 2010:

Query Tuning and Index Review June 2010

Here are the scripts and slides for my session on Query Tuning and Index Reviews.  I hope you enjoy(ed) the session.

QueryTuningAndIndexReview

Bullet Dodging Scripts Prairie Devcon 2010

Here are the scripts for my bullet-dodging and spoon-bending session at Prairie DevCon Regina 2010.  Enjoy!

BulletSpoon

Do your T-SQL variables have the right values?

For some of us who are bewildered by even the code we write ourselves, this might be a question we ask ourselves in more circumstances than we’d like to admit, but what I’m talking about here is assigning values to a variable with a SELECT statement, and expecting the value to be null if the query didn’t find anything.  This can be particularly nefarious in cursor loops as I will show.  First, to set up the scenario, run the following script.  It creates two temp tables – one with 10 rows and one with 5:

IF OBJECT_ID('tempdb..#one') IS NOT NULL DROP TABLE #one
IF OBJECT_ID('tempdb..#two') IS NOT NULL DROP TABLE #two
CREATE TABLE #one (number INT IDENTITY (1,1))
CREATE TABLE #two (number INT IDENTITY (1,1))
GO
INSERT #one DEFAULT VALUES
GO 10
INSERT #two DEFAULT VALUES
GO 5
SELECT * FROM #one
SELECT * FROM #two

number
-----------
1
2
3
4
5
6
7
8
9
10
(10 row(s) affected) number
-----------
1
2
3
4
5
(5 row(s) affected)

Now we will use a cursor loop to go through the first (10-record) table and look for matching values in the second table – we should obviously only find 5 matching values – and display our findings.  This is what it *should* look like:

DECLARE @CR CURSOR, @Number INT, @Found INT
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SET @Found =(SELECT number FROM #two WHERE number = @Number)
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Looking
for number 7...
Looking
for number 8...
Looking
for number 9...
Looking
for number 10...

Of course, it only found the five (by the way, please don’t take this as a recommendation to use cursors for this type of thing – I’m illustrating an unrelated point here…) How about the following, though – there is a subtle difference – do you see it right away?

DECLARE @CR CURSOR, @Number int, @Found int
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SELECT @Found = number FROM #two WHERE number = @Number
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Found: 5
Looking
for number 7...
Found: 5
Looking
for number 8...
Found: 5
Looking
for number 9...
Found: 5
Looking
for number 10...
Found: 5

So “SELECT @Var =” and “SET @Var =” are the same thing, right? Well, it appears not.  Using the SELECT method, the variable is not resetting to NULL.  There is a simple explanation for this.  The SET method is setting the variable to the output of a query – that query did not return anything which is a missing value – the very definition of NULL – but the SELECT method is changing the variable as part of the query.  Since the query returns no rows, the variable assignment never takes place so the value of the variable does not change.

You may want the variable to change to NULL and you may not – so choose your method accordingly, but I have seen code that uses the SELECT method followed by a test for NULL clearly expecting the variable to be nulled if no record is found.

Exercise:  What happens if you use an aggregate like MAX() with the SELECT method – do you know what it will do before you try it?  Try it and see if you’re right (hint:  aggregate queries always return at least one row).

Be careful out there – the devil is in the details.


Query Tuning Scripts Prairie DevCon 2010

Here are the scripts from the session.  I hope you enjoyed it.  Sorry that I got a bit carried away and didn’t quite finish.  At the end of the demo script you’ll see that we were going to actually optimize a query…  But the tools, and what to look for are the most important piece anyway.  Feel free to post a comment or send me mail if you have any questions.  Enjoy the rest of the conference.

IntroQueryTuning