April 11, 2008 at 10:11 am
This is SQL 2000, BTW. What I'm trying to do is write a stored procedure that allows a user to pass in any combination of, say, a customer key (CKey) and a store key (SKey) along with some other optional filter values, and simply dump out the rows from a big table that match the combination of keys supplied. Here is how I would like to accomplish this:
SELECT * FROM tblData
WHERE (@CKey IS NULL OR (@CKey IS NOT NULL AND CKey = @Ckey))
AND (@SKey IS NULL OR (@SKey IS NOT NULL AND SKey = @Skey))
Suppose the parameters are supplied as @CKey = 1234 and @SKey = NULL. What it seems to me *should* happen is that the database engine should determine statically that the entire SKey condition is true because @SKey is NULL. And it *should* determine that @CKey is not null statically, so the only thing it needs to validate row-by-row is that tblData.CKey = 1234, and it should use my index on CKey.
However, this never works as exepcted, and, even though the results of this query are correct, I end up with a multi-minute clustered index scan, verified via estimated execution plan and the time it takes to run.
But the following query written straight up uses the a CKey index seek with bookmark lookup and runs in a fraction of a second:
SELECT * FROM tblData
WHERE CKey = @Ckey
Is there any way to do this, other than to write IF...THEN statements with every combination of parameters supplied? E.g.:
IF (@CKey IS NULL AND @SKey IS NULL)
SELECT * FROM tblData
ELSE IF (@CKey IS NOT NULL AND @SKey IS NULL)
SELECT * FROM tblData WHERE CKey = @Ckey
-- Remainding parameter combinations
-- ...
The problem is that, in reality, I have several more optional parameters than just @CKey and @SKey, so the number of combinations increases exponentially, making this an unfeasible solution.
Thank you!
April 11, 2008 at 10:19 am
Have you tried the OPTION (RECOMPILE) query hint on that statement? Without it, it will generate an exec plan based on whatever is passed in the first time, and will not always check if the execution plan is any good for the NEW parameters.
Also - for what it's worth - you don't need the IS NOT NULL clauses if you're leaving the ANSI_NULLS setting alone (which you should be, since that's a deprecated setting on its way out with 2008 as I recall).
Meaning - you should probably try this:
SELECT *
FROM tblData
WHERE
(@CKey IS NULL OR CKey = @Ckey)
AND (@SKey IS NULL OR SKey = @Skey)
OPTION (RECOMPILE)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 1:11 pm
SQL Server 2000 doesn't support the OPTION (RECOMPILE) option at the statement level. However, I did try adding WITH RECOMPILE on the stored procedure definition (for proc-level recompilation), and, unfortunately, there was no improvement.
Any other ideas?
April 11, 2008 at 1:24 pm
There isn't a way around that. At least not one I'm aware of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:57 pm
Actually, there is a partial way around it, which is using Union statements instead of Or statements.
For example:
select Col1, Col2
from dbo.Table1
where Col1 = @Col1_in
union
select Col1, Col2
from dbo.Table1
where Col2 = @Col2_in
The problem with that method is that it only works for logical "or", not "and".
I'm going to play with some CTEs. I have an idea that might work for logical "and", but I'm not sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 2:10 pm
Nope. My idea didn't work.
The only way I can get it to use only Index Seeks, without Scans, is to separately query each index, build a temp table for each set of IDs, then query against that. And that gets even messier than using a simple "where (@Param is null or @Param = Col)" type structure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 2:27 pm
Probably the simplest solution would be to build a dynamic SQL statement. You are much more likely to get an optimal query plan, than with what you are trying.
April 11, 2008 at 2:30 pm
Now I know it's been a long week. Yeah, dynamic SQL will do that quite nicely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 2:51 pm
Unfortunately, I think you're right about the dynamic SQL and building the query string on the fly. That is while I'll likely do. Plus, it won't force me to recompile the entire proc. Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply