In versus = 1

[Catadmin]

I have been given a stored procedure to optimize which contains a very complicated If/Then Select. The worst (as far as I can tell) part of the IF/Then contains a query with a complicated Inner Join setup and a Where statement with no less than 6 main conditions and 2 - 10 nested subqueries within each main condition.

OY VEY!!!!

So, I'm hear to pick brains.

Does anyone have any opinions on whether the use of IN is better than the use of = (aside from the fact that IN is prettier) during complicated queries?

What about experience with the keywords AND NOT in front of the conditional versus AND <conditional> NOT IN or AND <conditional> <> ?

Let me know if you want me to post the WHERE clause. It's pretty messy. I had to print it out and circle all the nested stuff so I could tell what went with what... @=/

Thanks in Advance!!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

IN and = are used for two different things.

One is an exact match (=), the other tests for existance in an array (IN). You could take the IN and change it to a bunch of ='s using ORs.

col1 = 'a'
OR col1 = 'b'
OR col1 = 'c'
etc.

I've never done a Query Execution Plan on the two methods so I don't know if there would be a difference between the amount of lines of code used.

-SQLBill


Posting advice: FAQ481-4875

 

[Catadmin]

I guess I need to do some editing and check that. The current Displayed Estimated Query Plan counts this one section of the SP as 86% + of the entire query time. <shudder>.

Thanks, SQLBill.

If anyone else has insite on this matter before I post my results, let me know, please. Danke!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

I just noticed a typo (unimportant, but still a typo) in my previous post....


"...difference between the amount of lines of code used." SHOULD have been "...difference besides the amount of lines of code used."

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

HeeHee. Well, at least you weren't quoting code for me. @=)

Hey, have you or anyone else ever used a CASE or IF/Else statement in the WHERE clause? I'm not even sure it's possible.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Yes. I use one in a daily script I run. In fact, I was given the suggestion on how to do it on this site.

Let me find the original thread and I'll also post my where clause example.

-SQLBill



Posting advice: FAQ481-4875

 

[SQLBill]

Just check out THREAD183-999189. That should answer your question about using CASE in a WHERE clause.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

SQLBill, you genuis!!!!

To quote a certain beer commercial: "BRILLIANT!"

Thanks for the assist. One more question. Did you notice any performance issues with using the CASE in your WHERE clause?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Sometimes.....I use the CASE to provide a default when parameters are left null for a stored procedure. Basically, the CASE says if the parameter field is NULL then MATCH EVERYTHING.

For example:

WHERE FName = 
  CASE WHEN FName IS NULL
       THEN FName
       ELSE @FName END

Therefore, in my CASE (pun intended), if the parameter is provided then a small amount is returned. If the parameter isn't provided, lots of records are returned. So, yes it can cause a performance hit - but it's in the way it's used.

-SQLBill


Posting advice: FAQ481-4875