Quick Ques about variables & speed...
-
Hi all,
I've a weird problem, it's probably something very simple again, but I can't see it myself...
I wrote a query that allows me to look for 2 different type of products, listed under 2 different tables, like so:
SELECT *
FROM tbl_a
INNER JOIN tbl_b
ON tbl_a.index = tbl_b.serial
WHERE tbl_a.row_id = '123456' -- or tbl_b.row_id = '123456'
If I run this query, it takes < 1 sec in QA. However, I wanted to make this into a stored procedure, so I created a couple variables and now it looks like this:
DECLARE @a_or_b AS char(1)
DECALRE @row_id AS int
SET @a_or_b = 'a' -- input a or b here
SET @row_id = 123456 -- input row_id here
SELECT *
FROM tbl_a
INNER JOIN tbl_b
ON tbl_a.index = tbl_b.serial
WHERE CASE @a_or_b
WHEN 'a' THEN tbl_a.row_id
ELSE tbl_b.row_id
END = @row_id
So, the WHERE clause should end up being just tbl_a.row_id = 123456, right? The same as the top query. However, this version takes more than 6 minutes to run!!!!
Why is it taking so long?
Thanks for enlightening me...
Nick
-
A quick run through Query Analyzer would probably tell you more than I can, but I would believe that the conditional field check would cause the analyzer to miss the fact that the fields being checked are indexed, resulting in a table scan.
I would rewrite this WHERE clause as:
Where (tbl_a.row_id = @rowid AND @a_or_b = 'a') OR (tbl_b.row_id = @rowid AND @a_or_b = 'b')
In this case, you would almost certainly get the optimizer to recognize the indexes.
-
haha... and I thought it was something w/ SQL Server & variables in WHERE clauses... well, it is, sort of.
Both tbl_a and tbl_b are quite big, and when I just do WHERE row_id = 123456, then SQL Server using clustered index seek to pick out the specific row, before doing the joins (which are a bit more complicated than I had written on here.)
However, when I use variables, even if I changed to use your method brendthess, SQL Server still goes and do the joins first, which resulted in >200,000 rows of records being read, multiple times, hence severely hindered performance.
Is there any way to force SQL Server to do clustered index seek on the variable first? This is the real question.
Thanks!
-
hmm.. I can't edit the topic / subject? Wanted to change it to "Forcing Clustered index seek?"... Oh well, actually, I bet I can search in sqlservercentral & find something about forcing clustered index seek.
SqlServerCentral Rules!!!
-
Okay - I am going to play with the query optimizer's mind here:
SELECT * From (
SELECT *, 'a' AS Filter FROM tbl_a INNER JOIN tbl_b ON tbl_a.index = tbl_b.serial WHERE tbl_a.row_id = @row_id
UNION
SELECT *, 'b' As Filter FROM tbl_a INNER JOIN tbl_b ON tbl_a.index = tbl_b.serial WHERE tbl_b.row_id = @row_id )
WHERE Filter = @a_or_b
This will reduce the time significantly, while increasing the total over a perfectly optimized query. The only alternative that I see would include some IF statements, in the form of:
IF @a_or_b = 'A' Then
<statement>
ELSE
<statement>
However, I think that you have a case here where Dynamic SQL is more efficient, e.g.:
DECLARE @sql varchar(4000)
Set @sql = 'SELECT *, 'a' AS Filter
FROM tbl_a
INNER JOIN tbl_b
ON tbl_a.index = tbl_b.serial
WHERE tbl_' + @a_or_b + '.row_id = @row_id '
Then do whatever you need to with the query.
-
If you are going to use this in an SP and these are the only options the this will give best bang.
CREATE PROC ip_OptRun;1
@a_or_b AS char(1)
@row_id AS int
AS
SET NOCOUNT ON
If @a_or_b = 'a'
EXEC ip_OptRun;2 @row_id
ELSE
EXEC ip_OptRun;3 @row_id
GO
CREATE PROC ip_OptRun;2
@row_id AS int
AS
SET NOCOUNT ON
SELECT *
FROM tbl_a
INNER JOIN tbl_b
ON tbl_a.index = tbl_b.serial
WHERE tbl_a.row_id = @row_id
GO
CREATE PROC ip_OptRun;3
@row_id AS int
AS
SET NOCOUNT ON
SELECT *
FROM tbl_a
INNER JOIN tbl_b
ON tbl_a.index = tbl_b.serial
WHERE tbl_b.row_id = @row_id
GO
Then run
DECLARE @a_or_b AS char(1)
DECLARE @row_id AS int
SET @a_or_b = 'a' -- input a or b here
SET @row_id = 123456 -- input row_id here
EXEC ip_OptRun;1 @a_or_b, @row_id
-
The reason is due to the storing of the execution plan. If you have both in the same SP like that then you will only save the first execution plan which may not be optimal for the other condition and vice versa. With the method I showed it will store one for each condition under the individual item in the sp group.
-
Excellent! I went with the if statements + sprocs and performance is good.
Thanks all!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply