SQL Server Performance Forum – Threads Archive

How will SQL treat this?

Hi, This is my first topic here so hello to you all. In an effort to avoid dynamic sql, i am thinking about using the following method but can’t decide how the optimizer would treat it… I want to procedure to take a few optional parameters (a value or NULL) and i want the sql to ignore the parameter in the where clause if its been passed NULL. The obvious way to accomplish this would be to use case statements to build up some a string and execute via sp_ExectuteSQL or EXEC, but the following code would also do the trick… CREATE PROC usp_AProc (
@Animal VarChar(50) = NULL
@Vegetable VarChar(50) = NULL
@Mineral VarChar(50) = NULL
) SELECT *
FROM tbl_Items
WHERE Animal = ISNULL(@Animal, Animal)
AND Vegetable = ISNULL(@Vegetable, Vegetable)
AND Mineral = ISNULL(@Mineral, Mineral) So if the variables are NULL the where clause is simply saying
WHERE COL1 = COL1 Becuase this is being wrapped in an ISNULL, would each row be equated or is the optimizer able to realise that the local variable will always be null right the way down and therefore map it correctly as COL1 = COL1 ?? Many thanks
bob.

When you examine the execution plans for your procedure, you most likely see index scans instead of seeks. If you want (or need) to avoid dynamic sql at any cost, you might want to change your procedure to
CREATE PROC usp_AProc (
@Animal VarChar(50) = NULL
@Vegetable VarChar(50) = NULL
@Mineral VarChar(50) = NULL
) SELECT *
FROM tbl_Items
WHERE Animal LIKE ISNULL(@Animal, ‘%’)
AND Vegetable LIKE ISNULL(@Vegetable, ‘%’)
AND Mineral LIKE ISNULL(@Mineral, ‘%’) That way you should get index seeks. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

even though Frank’s query is better, it will still probably generate scans or otherwise expensive plans,
basically, it is just too hard for the SQL Server optimizer to figure out what you really meant,
even though it is clear to us. if you want a really efficient plan, you must have an IF block
IF (ISNULL(@Animal,’%’) <> ‘%’) THEN
SELECT
ELSE IF (@Vegatable
SELECT
ELSE ID (@Mineral
SELECT
END since these are exlcusive, there are only 3 possibilities,
otherwise, there are more possibilities, which is difficult to code, but the only way to get an efficient plan

You’re right Joe, since here we have "only" three parameters using IF constructs should work. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] When there are more, dynamic sql probably is the winner. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

thanks, very helpfull [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

SELECT *
FROM tbl_Items
WHERE ((@Animal is null)or (Animal = @Animal))
AND ((@Vegetable is null) or (Vegetable = @Vegetable))
AND ((@Mineral is null) or (Vegetable = Mineral)) This will make SQL server ignore Animal column all together if @Animal is null. It is pretty much like if else if but in one SQL Statement Hope it works Reagrds
Mani

It works, of course. But then again, you’ll get index scans, no seeks. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi Frank,
I have a similar query and have taken the approach in Mani’s post…from your comments am i right in thinking that index seeks are better? I can see how your suggestion works, but i was under the impression that using like wouldnt be as efficient as using = ?? I’m confused! Ben ‘I reject your reality and substitute my own’ – Adam Savage

Ben, sorry to be short now. Just got a call from my wife. She’s feeling sick, so I’m going home for today. I picked this technique up from the MS newsgroups. I’m sure you can find all information you need when you do a search on the Google groups for "WHERE LIKE ISNULL Umachandar". —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Both Joe’s and Fank’s solutions are good. To achieve the best possible performance I would modify Joe’s script to execute separate sp for each branch, so I would have optimal exec plan for each branch ready in cache after first branch execution. The original code: <blockquote id="quote">quote:<hr height="1" noshade id="quote"><pre id="code">CREATE PROC usp_AProc ( @Animal VarChar(50) = NULL @Vegetable VarChar(50) = NULL @Mineral VarChar(50) = NULL ) SELECT * FROM tbl_Items WHERE Animal = ISNULL(@Animal, Animal) AND Vegetable = ISNULL(@Vegetable, Vegetable) AND Mineral = ISNULL(@Mineral, Mineral)</pre id="code"><hr height="1" noshade id="quote"></blockquote id="quote">may make query optimizer to consider that value is provided for each sarg (animal, vegetable and mineral) so it may choose index on wrong column. However, I haven’t tested that kind of script for a long time, so better test it yourself and please give us a feedback <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

quote:Originally posted by benwilson Hi Frank,
I have a similar query and have taken the approach in Mani’s post…from your comments am i right in thinking that index seeks are better? I can see how your suggestion works, but i was under the impression that using like wouldnt be as efficient as using = ?? I’m confused!

I knew, if have read this somwhere. At least I have translated it to German [:I]
See if this helps explaininghttp://www.sommarskog.se/dyn-search.html#Umachandar
The whole article is *really* worth reading. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks Frank! I will be sure to give it a good read <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Had a quick look at the German version as well to see how much of my 1 semester of German i remember…unfortunately i could read very little of it 🙁 maybe i should get out my books and start studying again! ’I reject your reality and substitute my own’ – Adam Savage

Yes, it’s not the worst thing to speak a foreign language. Wish I had the chance to actually speak english instead of writing only in english. I also wish, I would speak Spanish. < Sigh >
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>