April 19, 2016 at 6:30 am
Some more cases of unexpected sargibility.
This is not prescriptive, it is descriptive.
Just because these result in seeks does not make it ok to build queries with functions on columns willy-nilly.
It is better to not use functions on columns as a rule of thumb, as far as is practical, but do not dogmatically follow any rule you hear from me... test test test always.
With that said, here are more exceptions to my previous belief that functions always cause a scan.
Tests done on Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64).
RTRIM was a surprise, but good to know the devs are looking into this sort of logic.
use adventureworks2012
go
CREATE NONCLUSTERED INDEX IX_DateTest ON [Person].[Person] ([ModifiedDate])
go
--RTRIM seems only sargible if compared to a parameter using LIKE but not with Equality.
declare @date datetime = getdate()-4812,@int int =6, @var nvarchar(50) = 'Smith%'
Select businessentityid from person.person where rtrim(lastname) like 'Smith%' --108 Reads What is happening here?
Select businessentityid from person.person where rtrim(lastname) like @var --4 Reads What is happening here?
Select businessentityid from person.person where cast(modifiedDate as date)=cast(@date as date) --2 Reads
Select businessentityid from person.person where convert(date,modifiedDate)=cast(@date as date) --2 Reads
Select modifiedDate from person.person where convert(smallint,businessentityid)=@int --2 Reads
Select modifiedDate from person.person where cast(businessentityid as smallint)=@int --2 Reads
April 20, 2016 at 4:11 pm
MadAdmin (4/19/2016)
Select businessentityid from person.person where rtrim(lastname) like 'Smith%' --108 Reads What is happening here?
Select businessentityid from person.person where rtrim(lastname) like @var --4 Reads What is happening here?
last name is NVARCHAR, 'Smith%' is VARCHAR, @var is VARCHAR.
Type mismatch - that's what happening here.
_____________
Code for TallyGenerator
April 20, 2016 at 4:41 pm
Sergiy (4/20/2016)
MadAdmin (4/19/2016)
Select businessentityid from person.person where rtrim(lastname) like 'Smith%' --108 Reads What is happening here?
Select businessentityid from person.person where rtrim(lastname) like @var --4 Reads What is happening here?
last name is NVARCHAR, 'Smith%' is VARCHAR, @var is VARCHAR.
Type mismatch - that's what happening here.
Have you tested it out? Seems like N'smith%' makes no difference, even with forced parameterization.
April 20, 2016 at 4:58 pm
That's what I thought at first, but I don't think so now.
Even if the string is correctly specified to unicode, I see the same behavior.
It actually appears that WHERE RTRIM(column) LIKE '<some literal>%' simply cannot use a seek.
Even with the correct datatype, using WITH (FORCESEEK) as a table hint causes that query to error out saying that the optimizer could not produce a plan.
On the other hand, WHERE RTRIM(column) LIKE @<some variable> seems to use the dynamic seek pattern regardless, so as long as the LikeRangeStart and LikeRangeEnd computed for the dynamic seek are at all selective, then that pattern gets some benefit.
At first I thought there might be some transformation rule that gets applied in the case with the variable but not in the case with the literal, but that doesn't seem to be the case (compared exec_query_transformation_stats before and after each query, and compared the deltas).
Also checked with 2372 enabled to make sure about the rules being applied, and still didn't see any relevant differences. From what I saw running them with 8606, the dynamic seek pattern is already present in the input tree.
One interesting note is that the dynamic seek plan for the version with the variable is marked a trivial plan (the behavior is the same if you skip the trivial optimization with 8757, though); you can still get a different plan by using a FORCESCAN, so there's some asymmetry. The version with the variable can seek or scan (to be fair, the dynamic seek pattern can easily just be a full scan anyway, with all the real work done by the residual predicate), while the version with the literal cannot seek.
Based on all this, it just seems that something about the internal implementation causes RTRIM to completely prevent a seek with LIKE('<literal>%'), but not with the variable.
Cheers!
April 20, 2016 at 7:19 pm
Sergiy (4/20/2016)
MadAdmin (4/19/2016)
Select businessentityid from person.person where rtrim(lastname) like 'Smith%' --108 Reads What is happening here?
Select businessentityid from person.person where rtrim(lastname) like @var --4 Reads What is happening here?
last name is NVARCHAR, 'Smith%' is VARCHAR, @var is VARCHAR.
Type mismatch - that's what happening here.
Looking at the code, I see @var defined as NVARCHAR(50) and there have been no edits to the original post. Other than that, I agree. However, NVARCHAR(50) of the column has a higher datatype precedence than the implicitly VARCHAR constant and a scan shouldn't have been chosen because of that. It's definitely the RTRIM that did that. There should have been a simple implicit conversion of the constant to NVARCHAR().
Rightly enough, If you change the constant in the first SELECT to N'Smith%', there's no change on my box (2008 DevEd)... it still does an index scan because of the RTRIM. On the surface, that's disappointing until you realize that it really is non-SARGable.
What's odd is that the "like @var " comes up with a seek predicate that's different than the predicate. The Seek Predicate comes up with a > < range using two scalar values formed in the compute-scalar. It's like the optimizer knew that the query wasn't SARGable and did a workaround substitute for the RTRIM.
The same holds true for the date comparisons. The optimizer created a different Seek predicate than the original to avoid the non-SARGable predicate.
That's pretty cool. I'd never write code that way but it' nice to see that people who would make such a mistake can sometimes be forgiven by the optimizer.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2016 at 7:13 am
Jeff Moden (4/20/2016)
...
What's odd is that the "like @var " comes up with a seek predicate that's different than the predicate. The Seek Predicate comes up with a > < range using two scalar values formed in the compute-scalar. It's like the optimizer knew that the query
wasn't SARGable and did a workaround substitute for the RTRIM.
...
That's the dynamic seek pattern I refer to several times in my post.
Under certain conditions, SQL Server will compute a LikeRangeStart and LikeRangeEnd that covers the predicate in the query, do a seek (really a range scan, seeking to the beginning of the range) and then apply the actual query predicate as a residual.
You can read some about that from Paul White here:
The fun aspect of this is that if you switch the variable to having a leading wildcard, then the plan still shows this as a seek, but it's really just doing a range scan where the range is the entire table, and applying the LIKE as a residual predicate.
But you're getting a seek, so everything's good, right? 😀
EDIT: Further, this is actually the way seeks with LIKE and a trailing wildcard work with literals already, except that the start and end of the range don't have to be calculated at run time, so you don't have to do the constant scan/compute scalar/join.
Remove the RTRIM so that the query with the literal can do a seek, and you'll see that the seek predicates are for >='Smith' AND <'SmitI', with the LIKE applied as a residual predicate.
Cheers!
April 21, 2016 at 2:26 pm
Yep. Understood on the dynamic seek pattern you spoke of. My question is why it's using "Open/Open" instead of "Closed/Open".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply