December 20, 2013 at 4:50 pm
Evil Kraig F (12/20/2013)But, I'd like to bring us back to the origination of this minor alteration in the direction of this topic, and that's SARGability and effeciency and if it should even be allowed to exist. The following construct was built in SQL 2k5 and tested there.
You provide the specific clustered key value separately, so of course the final results are very similar. Although,
even in your example, the full seek on the clustering key is often much better performance-wise than a residual predicate.
I've had many instances of great performance gains just from changing:
ISNULL(col1, '')
to
(col1 IS NULL or col1 = '')
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:
Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
Then run this:
SET STATISTICS IO, TIME ON
SELECT
*
FROM
#tester
WHERE
SomeDate = '20001231'
AND ( SomeCharacter IS NULL OR SomeCharacter = '' )
SELECT
*
FROM
#tester
WHERE
SomeDate = '20001231'
AND ISNULL( SomeCharacter, '' ) = ''
SET STATISTICS IO, TIME OFF
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 20, 2013 at 5:02 pm
ScottPletcher (12/20/2013)
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
I'm not sure if my post was too long or wasn't clear, but I did that EXACT test particularly for the second half of my post, and agree with you thoroughly about when you're on/in the leading edge of the index. I even show that there's a ~15-20% time difference between the two.
My point, however, was that SARGability is not the be all/end all for equivalently performing code, particularly if you are getting more readable code. ISNULL vs. OR is a simple example of said more complex topic. I don't disagree with best practice when it makes a difference, but if your code is more readable and it makes no difference, I would go with the easier to read version. EDIT: Which sometimes means wrap the simple function around the column instead of bending over backwards creating a massive OR component. ROUND() is the first example that comes to mind as one that would be impressively painful to attempt to SARGabilize.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 20, 2013 at 5:04 pm
Evil Kraig F (12/20/2013)
Jeff, neat trick with the a.Field > ''. Was unaware of that.
Thanks for the feedback, Craig. And, because comparatively few people are aware of that, I strongly recommend always including the comment.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2013 at 5:05 pm
Evil Kraig F (12/20/2013)
ScottPletcher (12/20/2013)
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
I'm not sure if my post was too long or wasn't clear, but I did that EXACT test particularly for the second half of my post, and agree with you thoroughly about when you're on/in the leading edge of the index. I even show that there's a ~15-20% time difference between the two.
My point, however, was that SARGability is not the be all/end all for equivalently performing code, particularly if you are getting more readable code. ISNULL vs. OR is a simple example of said more complex topic. I don't disagree with best practice when it makes a difference, but if your code is more readable and it makes no difference, I would go with the easier to read version.
And my original point that somehow upset so many was this since this code:
WHERE ( SomeCharacter IS NULL OR SomeCharacter = '' )
is both clearer and performs better, then this code:
WHERE ISNULL( SomeCharacter, '' ) = ''
should simply not be used.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 20, 2013 at 5:07 pm
Evil Kraig F (12/19/2013)
IT2012 (12/19/2013)
As is the case with all "canned" software, seems we always need functionality that doesn't quite come with the package. SO.... I'm teaching myself with the aid of about 6 trusty T-SQL books, but the books only go so deep.I would recommend adding a book to your collection. Look for Fundamentals of T-SQL (your version) by Itzik Ben-Gan, particularly if you're worried that the books don't go deep enough.
Another excellent one for deeper learning is SQL Server Internals. Many authors, but you'll find it. Again, get it for the version you're working with.
+1000!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2013 at 5:10 pm
dwain.c (12/18/2013)
Jeff Moden (12/18/2013)
Using no more arcane knowledge than knowing that NULL can't be "not equal" to anything, the following code will do the same thing even on fixed length datatypes such as CHAR and still be SARGable.
WHERE a.field > '' --Not Null and Not Blank
It's also faster and burns a lot less clock cycles than LTRIM/RTRIM or even ISNULL() or COALESCE.
That is way cool Jeff, even if it is a bit arcane!
Thanks, Dwain. That's why I always include the comment even in my own code. It helps me remember before I have that all important 2nd cup of coffee. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2013 at 5:16 pm
Evil Kraig F (12/20/2013)
ScottPletcher (12/20/2013)
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
I'm not sure if my post was too long or wasn't clear, but I did that EXACT test particularly for the second half of my post, and agree with you thoroughly about when you're on/in the leading edge of the index. I even show that there's a ~15-20% time difference between the two.
My point, however, was that SARGability is not the be all/end all for equivalently performing code, particularly if you are getting more readable code. ISNULL vs. OR is a simple example of said more complex topic. I don't disagree with best practice when it makes a difference, but if your code is more readable and it makes no difference, I would go with the easier to read version. EDIT: Which sometimes means wrap the simple function around the column instead of bending over backwards creating a massive OR component. ROUND() is the first example that comes to mind as one that would be impressively painful to attempt to SARGabilize.
The smarmy condescension really isn't justified, given that you did NOT do that "EXACT" test -- you added a nonclustered index to the table that SQL (apparently mistakenly(?) for the SARGable query) preferred over the clustered index.
Did you actually run the code above and look at the I/O results? The SARGable query does 10 logical I/Os total vs 4079 for the other query -- the nonclustered index provided a crutch that made it appear as if the two queries got similar results, when they really should not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 20, 2013 at 5:26 pm
Apologies, I missed your split response on this earlier. I scrolled too quickly.
I wasn't ignoring anything. Me real point was that indexes could be modified in the future in unknown ways, obviously w/o rewriting all existing code. In such cases, it's possible two separate indexes could then exist, one for each column. Then the optimizer might be able to do seeks on each separate index to determine a final list ... unless, as in the situation being examined, one column was excluded from a seek because it contained ISNULL().
My understanding was split indexes like that would only be used during an OR component, not an AND. For example:
StartDate > @stdt AND (EndDate IS NULL OR EndDate >= @enddt)
That wouldn't use a split index, whereas:
StartDate > @stdt OR (EndDate IS NULL OR EndDate >= @enddt)
Can.
I haven't gone out of my way to do heavy experimentation with that, but if you have an easy example showing how ANDs can use split indexes I'd love to be able to learn from it.
ScottPletcher (12/20/2013)
And my original point that somehow upset so many was this since this code:WHERE ( SomeCharacter IS NULL OR SomeCharacter = '' )
is both clearer and performs better, then this code:
WHERE ISNULL( SomeCharacter, '' ) = ''
should simply not be used.
I'm going to drag your quote down from above for reference:
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
This is a different statement. That is not a statement of "Use this code instead of that", it's a hard and fast rule that it should never exist there. If you meant that particular line of code should always be swapped, then my apologies, I misread your intent and your "voice" in further discussions about it.
For example, the following is currently a line of a rather long where clause in my system:
ISNULL( NULLIF( RTRIM( LTRIM( drv.SubAcctIndicator)), ''), drv.pCode) = drv.pCode
This is mostly due to some pretty significant gaps in data controls in a vendor system I work against and trying to report/warehouse against it. Now, it's probably possible this could be significantly re-written for better performance, but it's easy to read and I have almost no issues with it. drv happens to be the alias subquery of the main structure.
It's cases like this, where it's not just the simple swap you mention here, later, that your original statement caused me to feel the need to be involved in this discussion.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 20, 2013 at 5:34 pm
ScottPletcher (12/20/2013)
...And my original point that somehow upset so many was this since this code:
WHERE ( SomeCharacter IS NULL OR SomeCharacter = '' )
is both clearer and performs better, then this code:
WHERE ISNULL( SomeCharacter, '' ) = ''
should simply not be used.
Really want to go back to your original point? Okay, let's look at the original question and please show me the WHERE clause in the question.
IT2012 (12/18/2013)
Can someone tell me what the following statement is doing??IF ISNULL(@category, '') = ''
I understand it's testing whether the variable @category is null, but what I don't understand is what the values in the parentheses mean. (@category,'').... what is that?!?!
Thanks in advance.
Carol
I see an IF statement and that the question is asking what the values in the parenthesis mean. Not asking what the IF statement is doing.
My answer was simple by showing the OP the help info in BOL on the ISNULL function.
December 20, 2013 at 5:35 pm
ScottPletcher (12/20/2013)
The smarmy condescension really isn't justified,
Wasn't justified either way. My apologies, that was undeserved. It didn't read that way to me when I first wrote it.
given that you did NOT do that "EXACT" test -- you added a nonclustered index to the table that SQL (apparently mistakenly(?) for the SARGable query) preferred over the clustered index.
True.
Did you actually run the code above and look at the I/O results? The SARGable query does 10 logical I/Os total vs 4079 for the other query -- the nonclustered index provided a crutch that made it appear as if the two queries got similar results, when they really should not.
True, and you've also pointed out to me a case where an AND can split the request across two indexes. In this case, it's the same one, but it points it out.
Thank you.
EDIT: Wow, that's rather curious. It split the ( AND (OR) ) into a ( (AND) OR ( AND) ). Wonder where the complexity level would stop it for that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 20, 2013 at 8:14 pm
ScottPletcher (12/20/2013)
Evil Kraig F (12/20/2013)
ScottPletcher (12/20/2013)
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
I'm not sure if my post was too long or wasn't clear, but I did that EXACT test particularly for the second half of my post, and agree with you thoroughly about when you're on/in the leading edge of the index. I even show that there's a ~15-20% time difference between the two.
My point, however, was that SARGability is not the be all/end all for equivalently performing code, particularly if you are getting more readable code. ISNULL vs. OR is a simple example of said more complex topic. I don't disagree with best practice when it makes a difference, but if your code is more readable and it makes no difference, I would go with the easier to read version.
And my original point that somehow upset so many was this since this code:
WHERE ( SomeCharacter IS NULL OR SomeCharacter = '' )
is both clearer and performs better, then this code:
WHERE ISNULL( SomeCharacter, '' ) = ''
should simply not be used.
Without an index on SomeCharacter, you'd have to prove to me that the code runs faster. 😉
If you go back and look, the thing that upset folks was the adamant stance you took on that being more clear than ISNULL. I'll give you that your method is no less clear than ISNULL but ISNULL should only be unclear to the greenest of neophytes and once it's discovered that they don't know what it is, it should be taught to them right away.
I do, however, agree that ISNULL() should be avoided in the WHERE clause for all the reasons that you and I previously agreed upon.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2013 at 7:17 am
Jeff Moden (12/20/2013)
ScottPletcher (12/20/2013)
Evil Kraig F (12/20/2013)
ScottPletcher (12/20/2013)
Admittedly the gain is much clearer when the clustering key is the ISNULLed column. So, let's just flip the clustering keys on your original table -- as might happen in real life -- and re-test:Run this first, the clus index change:
SET STATISTICS IO, TIME OFF
DROP INDEX idx_test ON #tester;
CREATE CLUSTERED INDEX idx_test ON #tester ( SomeCharacter, SomeDate );
I'm not sure if my post was too long or wasn't clear, but I did that EXACT test particularly for the second half of my post, and agree with you thoroughly about when you're on/in the leading edge of the index. I even show that there's a ~15-20% time difference between the two.
My point, however, was that SARGability is not the be all/end all for equivalently performing code, particularly if you are getting more readable code. ISNULL vs. OR is a simple example of said more complex topic. I don't disagree with best practice when it makes a difference, but if your code is more readable and it makes no difference, I would go with the easier to read version.
And my original point that somehow upset so many was this since this code:
WHERE ( SomeCharacter IS NULL OR SomeCharacter = '' )
is both clearer and performs better, then this code:
WHERE ISNULL( SomeCharacter, '' ) = ''
should simply not be used.
Without an index on SomeCharacter, you'd have to prove to me that the code runs faster. 😉
If you go back and look, the thing that upset folks was the adamant stance you took on that being more clear than ISNULL. I'll give you that your method is no less clear than ISNULL but ISNULL should only be unclear to the greenest of neophytes and once it's discovered that they don't know what it is, it should be taught to them right away.
I do, however, agree that ISNULL() should be avoided in the WHERE clause for all the reasons that you and I previously agreed upon.
Fair enough: we just disagree on that. I do believe that the written out version is clearer, although, yes, Books Online is sufficient to explain it.
But, since you must avoid ISNULL() in WHEREs, just avoid it in all conditional statement/clauses. You never lose anything, and you could gain big, including in understanding and maintenance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply