November 2, 2006 at 12:06 pm
I have a simple select with joins to 2 other tables and in my where clause I basiclly have a statement
C.CtgyLvl04I= isnull(@CtgyLvl04I,C.CtgyLvl04I)
Running the query with this line takes 22min removing that line it runs in 1 second. Has anyone seen posts about this happening?
November 2, 2006 at 5:45 pm
From what I can gather, you are saying that if @CtgyLvl04I is null, don't use it (because your option is to compare C.CtgyLvl04I to itself)
You could try using the following syntax
(
@CtgyLvl04I IS NULL
OR
C.CtgyLvl04I = @CtgyLvl04I
)
November 2, 2006 at 6:44 pm
Thank you for the reply. I added a similar statement (case statement) to that where clause and it fixed it I was just wondering if anyone else was having this problem or if anyone else could duplicate this problem. Work arounds are great but I was looking for what was going on that causes this to happen.
Thank you again for the help.
November 2, 2006 at 6:53 pm
Fair enough.
You could probably use SQL Profiler on the query to try to find out what is going on. Run it once without the isnull, then again with the isnull to compare. Not sure exactly what you would look for though.
November 2, 2006 at 11:32 pm
What does the execution plan look like with the isnull and without it?
If it's a stored procedure, it may be getting a bad execution plan. If the optimiser compiles for a single row (based on a parameter value) it may choose a plan that's optimal for a small number of rows.
If a different parameter would result in a large number of rows (like your null) then SQL may use the cached plan and run really badly.
If that's the case, consider forcing recompiles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2006 at 9:01 am
ISNULL frequently causes this sort of problem. We've had to rewrite a number of queries to change ISNULL to an or statement to improve performance.
November 3, 2006 at 10:21 am
Thanks for the replies.
It switches from a Index seek (without isnull) to a index scan on a rather large table and takes 70% of that query which takes 80% of the entire plan.
November 10, 2006 at 8:26 pm
You find that all functions can cause this problem.
SELECT *
FROM
WHERE
@SomeVariable = Function(SomeColumn)
Check your execution plan or SET STATISTICS IO ON and look at your read counts. You will see that it is reading the entire Index and comparing each calculated value to your variable. The "or version" is better, but I have had problems with it in SQL 2000, but not in SQL 2005
(
@CtgyLvl04I IS NULL
OR
C.CtgyLvl04I = @CtgyLvl04I
)
The other alternative is to write 2 quere is
IF @CtgyLvl04I IS NULL
SELECT ....
ELSE
SELECT ....
If you have several of these filtering conditions that are giving you fits then a you might build some dynamic paramterized sql. look at sp_executesql. The only draw back is the plans might not get effectively cached and you can eat up alot of procCache memory which can cause other problems.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply