Is NULL comparison slow?
-
Greetings,
I have a query which returns around 800 records.
In the WHERE clause I have various conditions.
Two of them check for NULL values of a parameter passed in.
Each of the lines addes around 30ms to the query.
Other comparisons like date, int etc don't add anything (Maybe a couple ms)
The line is:
AND(ErrorID >= @ErrNumStart OR @ErrNumStart Is Null)
If @ErrNumStart is not null then it'll use it, else won't care.
I have used this type of statment plenty times without a problem.
Ideas?
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Try this
AND (CASE WHEN ErrorID >= @ErrNumStart OR @ErrNumStart Is Null THEN 1 ELSE 0 END) = 1
I am just curious what effect it has. Is the code in an SP and have you looked at the execution plan to see how compares without the line.
-
hmmm, What is the difference?
Speed?
Your solution gives about a 30ms decrease in time taken.
AND(I.ErrorID >= @ErrNumStart OR @ErrNumStart = 0) -- Slow option
AND(CASE WHEN I.ErrorID >= @ErrNumStart OR @ErrNumStart Is Null THEN 1 ELSE 0 END) = 1 -- fast option.
Why would there be a difference?
Your option has more to evaluate. Does it?
The code is currently script. Once done roughly I will create a SP out of it.
Thanks! Once again you make me rewrite some code...
This is what I am after, it's a slow proc I am trying to increase...
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I still haven't totally figured out the difference yet and the optimal way to work it. But it does seem to evaluate faster which I think has to do with the 1 or 0 = 1 bit comparision in some way. If I develope a better theory I will try to pass along but I just discovered this today.
-
Have you tried using a coalesce with a number that isn't valid for the variable like a negative number. e.g
AND(ErrorID >= COALESCE(@errNumStart,-1))
-
Something strange.
Your fast option is slower in another clause.
AND(I.LogDate <= @DateTo OR @DateTo = 'Jan 01 1900') -- Faster
AND(CASE WHEN I.LogDate <= @DateTo OR @DateTo = 'Jan 01 1900' THEN 1 ELSE 0 END) = 1 -- Slower.
Am I missing something? Could it be the datatypes handled differently? Int is faster than a datetime?
So many ways, so little time...
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Kenny,
Just tried it and the query now runs at 19ms against 35ms for the second option.
I have used COALESCE once or twice but only to "turn" a record set into a string.
Explain how this works in this context?
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
SOrry, had not tried with a date value as I have had no need but do you happen to have an index on that column and not the other (could answer a few things I saw today)?
Try also using NULL as the default for @DateTo.
I.LogDate <= IsNull(@DateTo, I.LogDate)
In regards to COALESCE it is the same as ISNULL except it can take more than on NULL possibility so in your situation you can jsut replace COALESCE with ISNULL in his statement and see what happens. Basically ISNULL checks the first value and if NULL return the second value. COALESCE will check the first and if null then check the second and if null go to the third. The only thing thou is if your ErrorID is NULL it will be eliminated from the resultset otherwise it will be faster as it can better take advantage of any index on that column.
-
Antares answered the coalesce question.
Antares,
In the original query, rows with a NULL ErrorID would not have been returned if his @ErrNumStart was not null.
It would have evaluated to
AND (NULL >= 8 OR 8 IS NULL)
If both were NULL everything would be returned
AND (NULL = 8 OR NULL IS NULL)
Am I correct in my thinking here?
-
Yes, that is primarily why I went the way I did since that was what first popped in my head and I just couldn't help my curiosity.
In mine if the value = the ErrorID then if presents a 1 = 1 or if not then 0 = 1. But if he has NULL as the value the OR IS NULL will present 1 for all values and thus no 0 = 1 elimination. However, I didn't stop to ask if it mattered so yours would be a much better solution if ErrorID is always populated and the value is 0 or better. But I would probably do ISNULL since the second value will never be NULL in your example and to avoid the check COALESCE does for NULL on each value.
-
Ahhh, I didn't look at your code that closely. Now I see what you were talking about.
-
But whatever works best and does accomplish the task is the better answer. So hopefully for Crispin yours fully meets his needs as it does provide a faster output. But I have had a chance to see that what I came across today isn't just a chance incident and there is probably so more exploring for my code so I understand it's interaction.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply