Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Problem with Datetime Function Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
That has nothing to do with the SARGability of the query.

That's SQL Server avoiding key lookups.

Two completely different things.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397384
Posted Monday, December 17, 2012 1:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 2,112, Visits: 5,495
I'm sorry but I don't see what it has to do with lookups. Notice that I had 2 queries. Both of them had the same criteria. The only difference between them was that one of them used variables that got there values at the same scope as the query and the other one used the values directly. The one that used it directly did use seek operator and the one that used variables used a table scan (of course both of them returned the same records). If this would have been to avoid lookups, then both queries should have acted the same.

Also if I modify the queries and use dates that don't exist in the table (again for both queries) I still get index seek for the query that is not using the variables and table scan for query that is using the variables. This is because what I've explained before. The query plan is generated before runtime. The variables get there values during runtime, so when the server creates the query plan it has no idea about the values that will be used in the query so it estimates that 20% will be returned

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1397396
Posted Monday, December 17, 2012 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Adi Cohn-120898 (12/17/2012)
I'm sorry but I don't see what it has to do with lookups. Notice that I had 2 queries. Both of them had the same criteria. The only difference between them was that one of them used variables that got there values at the same scope as the query and the other one used the values directly. The one that used it directly did use seek operator and the one that used variables used a table scan (of course both of them returned the same records). If this would have been to avoid lookups, then both queries should have acted the same.

Also if I modify the queries and use dates that don't exist in the table (again for both queries) I still get index seek for the query that is not using the variables and table scan for query that is using the variables. This is because what I've explained before. The query plan is generated before runtime. The variables get there values during runtime, so when the server creates the query plan it has no idea about the values that will be used in the query so it estimates that 20% will be returned

Adi


Partially true. In the fixed-values version, the optimizer knows before-hand that the number of rows that will be returned is small enough to allow for a seek+key lookup.

Expand the date range in that version out to a year, so that the number of key lookups is more expensive than a scan, and you'll get a clustered index scan on it.

Again, it has nothing to do with the SARGability of it. It has to do with SQL Server's query optimizer deciding, based on the data presented to it, what method of resolving the query is the least expensive.

I tested various ranges, and I get a seek+lookup at 1 month, but a scan at 5 weeks. Took it narrower, and 31 days gets a seek (on your fixed-values version) while 32 gets a scan. Exact results will vary depending on the data in your version of the table, since generating the data off of checksums on NewID() is effectively random.

The variables version, SQL Server is smart enough to know that the values may change from run to run, so it builds an execution plan that can expand to cover a larger number of rows, where the key lookups might be too expensive. The fixed values version, it doesn't do that.

Nothing to do with whether the argument itself is SARGable.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397401
Posted Monday, December 17, 2012 2:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 2,112, Visits: 5,495
At this point we both agree that when variables are used in a query and the variables got there values in the same scope as the query, the server will do a table scan (again if the index is not clustered and is not a covering index). Our disagreement now is more about semantics. I claim that this is a matter of SARGability and you claim that it isn't. I guess that we'll leave it at that point.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1397420
Posted Tuesday, December 18, 2012 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Adi Cohn-120898 (12/17/2012)
At this point we both agree that when variables are used in a query and the variables got there values in the same scope as the query, the server will do a table scan (again if the index is not clustered and is not a covering index). Our disagreement now is more about semantics. I claim that this is a matter of SARGability and you claim that it isn't. I guess that we'll leave it at that point.

Adi


As per Voltaire's recommendation: Before you may [discuss] with me, you must define your terms. (Can't say "argue" here, since we're not arguing, merely discussing.)

The definition I'm using of "SARGable" is: filtering criteria written in such a way that an appropriate index, if created, can have a seek operation run against it instead of forcing a scan.

I say "filtering criteria", because it can include Where clause items, join-math, or even Group By items.

I don't include the actual existence of an index in the definition. Just the potentiality of one. This way, SARGability is a skill that can be taught to query writers (database devs). I don't consider it "fair" to tell a dev "your Where clause is poorly written because I haven't been smart enough to create an index on that table that you're querying".

I specify "can" instead of "will" with regard to the potentiality of a seek, because other factors can cause the optimizer to choose a scan regardless of how the query is written. For example, if the table has only a very few rows, SQL Server will often scan instead of seek, regardless of indexes, properly written Where clauses, et al, simply because there's no gain to be had from a scan on a table that fits on (for example) a single 8k page.

The primary reason I state that SARGability doesn't take into account data volume, row distribution, et al, as you seem to, is because I consider SARGability a characteristic of the query, not the underlying data and structure.

Hence, "WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances, but "WHERE Col1 = 5" is SARGable, by my definition, regardless of what table it's being run on. If "WHERE Col1 = 5" is used on an indexless heap, it obviously won't result in a seek, since there are no indexes that it can seek on, but my definition still calls it SARGable code.

By the definition of SARGable that you seem to be using, "WHERE Col1 = 5" is sometimes SARGable and sometimes not. To me, that makes the subject far too vague and complex to teach to beginning database devs, so it's less useful as a definition. By the definition I use, "WHERE Col1 = 5" is SARGable code, even if the database objects it is run against don't support that.

Just a difference of definition. I find my definition more useful, you find yours more useful (or more accurate, or whatever).

I'm surmising/extrapolating your definition of SARGable, of course. Can you elucidate your exact definition?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397767
Posted Wednesday, December 19, 2012 2:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 2,112, Visits: 5,495
I have to admit that it is very hard for me to define the term SARGable. Before we started our discussion in my opinion SARGable criteria was one that has the potential of using seek operation without modifying the query's code. Of course by that definition you are correct when you say that my example had nothing to do with SARGability. As you showed in your example when we have a clustered index the server does a seek operation without modifying the query's code. I also wrote that the server would still do a seek operation with none clustered index that is a covering index and based on the columns that were compared to the variables, which again showed that seek operation can be done without modifying the query's code.

The problem is that if we think only about the potential it will be very hard to find criteria that is not SARGable. We have so many tools today that can be used to turn scan operation into seek operation. We can create many columns with lots of included columns. We can create an indexed view on a table. We can use computed columns and index those columns. In reality many of the criteria that we both agree that is not SARGable can use seek operation if this will be our main goal. Take for example your quote:

"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances

I completely agree that this type of criteria is not SARGable, but I know that I can have such a query and with some modifications to the table and indexes the server will do a seek operation. This of course contradicts the definition that I was using before our discussion and the definition that you wrote. The example bellow shows how the same condition that both of us agreed that is not SARGable, can use seek operation.:

CREATE TABLE dbo.SARGTest (
DT DATETIME NOT NULL, filler char(1) default ('a'));
GO

--Inserting the records
INSERT INTO dbo.SARGTest(DT)
SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())
FROM sys.objects
CROSS JOIN sys.objects as s2 CROSS JOIN sys.objects as s3

--Creating an index that won't be used
CREATE INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);
go

--As expected we got a table scan
SELECT * FROM dbo.SARGTest
WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)
go

--Adding a computed column to the table
alter table dbo.SARGTest add ConvertedDate as convert(varchar(25), DT, 112)
go

--Adding an index on the computed column
create index ix_SARGTest_DT_INCLUDE_filler on dbo.SARGTest(ConvertedDate) include (filler, DT)
go

--Getting an index seek
SELECT * FROM dbo.SARGTest
WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)

Maybe we should use the term SARGablity with connection of the database's current structure and not with the query's potential to use seek operation if I'll modify the database.

Adi



--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1398252
Posted Wednesday, December 19, 2012 6:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
Adi Cohn-120898 (12/19/2012)
I have to admit that it is very hard for me to define the term SARGable. Before we started our discussion in my opinion SARGable criteria was one that has the potential of using seek operation without modifying the query's code. Of course by that definition you are correct when you say that my example had nothing to do with SARGability. As you showed in your example when we have a clustered index the server does a seek operation without modifying the query's code. I also wrote that the server would still do a seek operation with none clustered index that is a covering index and based on the columns that were compared to the variables, which again showed that seek operation can be done without modifying the query's code.

The problem is that if we think only about the potential it will be very hard to find criteria that is not SARGable. We have so many tools today that can be used to turn scan operation into seek operation. We can create many columns with lots of included columns. We can create an indexed view on a table. We can use computed columns and index those columns. In reality many of the criteria that we both agree that is not SARGable can use seek operation if this will be our main goal. Take for example your quote:

"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances

I completely agree that this type of criteria is not SARGable, but I know that I can have such a query and with some modifications to the table and indexes the server will do a seek operation. This of course contradicts the definition that I was using before our discussion and the definition that you wrote. The example bellow shows how the same condition that both of us agreed that is not SARGable, can use seek operation.:

CREATE TABLE dbo.SARGTest (
DT DATETIME NOT NULL, filler char(1) default ('a'));
GO

--Inserting the records
INSERT INTO dbo.SARGTest(DT)
SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())
FROM sys.objects
CROSS JOIN sys.objects as s2 CROSS JOIN sys.objects as s3

--Creating an index that won't be used
CREATE INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);
go

--As expected we got a table scan
SELECT * FROM dbo.SARGTest
WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)
go

--Adding a computed column to the table
alter table dbo.SARGTest add ConvertedDate as convert(varchar(25), DT, 112)
go

--Adding an index on the computed column
create index ix_SARGTest_DT_INCLUDE_filler on dbo.SARGTest(ConvertedDate) include (filler, DT)
go

--Getting an index seek
SELECT * FROM dbo.SARGTest
WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), DT, 112)

Maybe we should use the term SARGablity with connection of the database's current structure and not with the query's potential to use seek operation if I'll modify the database.

Adi



I'm sorry, but I think you are going to extremes here in your argument discussion with GSquared.

Just because you CAN add a persisted computed column and index it force a seek when you have this where clause: "WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)", doesn't make it SARGable. Doing that, creating the index, just adds more overhead to system and does nothing to help someone learn better ways of writing code. You could say it treats the symtom, not the problem.

It the case of this WHERE clause, a simple rewrite is much easioer than changing the structure of the database to meet the query.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1398348
Posted Wednesday, December 19, 2012 6:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 2,112, Visits: 5,495

I'm sorry, but I think you are going to extremes here in your argument discussion with GSquared.

Just because you CAN add a persisted computed column and index it force a seek when you have this where clause: "WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)", doesn't make it SARGable. Doing that, creating the index, just adds more overhead to system and does nothing to help someone learn better ways of writing code. You could say it treats the symtom, not the problem.

It the case of this WHERE clause, a simple rewrite is much easioer than changing the structure of the database to meet the query.

I think that you misunderstood my message. Below is a quote from my message that says the exact thing that you claim- this is not a SARGable criteria:

Take for example your quote:

"WHERE CONVERT(VARCHAR(25), GETDATE(), 112) = CONVERT(VARCHAR(25), MyDateTimeColumn, 112)" is NOT SARGable, under any circumstances

I completely agree that this type of criteria is not SARGable


Maybe I wasn't clear in my writing, or maybe you took it out of context. It is clear to me and everyone that reads this thread that a query with this type of criteria can be improved by modifying the code and not by jumping hoops and loops as I did. GSquared gave his definition of SARGability and asked me for my definition. I explained that this discussion made me realize that I'm using a wrong definition (my definition was that the query is SARGable if the server can use seek operation without modifying the code) and showed this example as an explanation to show why my definition is wrong (e.g. According to the definition that I've used until this discussion a query like that should have been considered SARGable, but in reality it isn’t).

Adi




--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1398374
Posted Wednesday, December 19, 2012 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Well, maybe we need a term like "circumstantial SARGability", to indicate that edge cases can be found where "normal SARGability" fails but a clever DBA can get seeks to happen anyway.

Would cover the situation you posed, without necessarily reducing the usefulness of the term in training general query writing skills.

The most important aspect of SARGability as a term is being able to use it in training people. That means keeping it simple, and letting them know that, "For most applications you will build using T-SQL, it's important to keep function-use out of Where clauses and Join math, because of ..." and go from there. As with everything databases, there are caveats, loopholes, "it depends", and "most of the time"s.

The main reason I want to keep the general term closely tied to writing queries, instead of related to specific object properties or data properties, is because that keeps it most useful in teaching query-writing.

Keeping it that way also helps with teaching database architecture. "In order to best support the SARGable queries your devs will be writing, you should design tables with the following in mind ..." That kind of thing.

More advanced querying techniques and architectural techniques will usual be done by people who understand the rules well enough to know when to violate them. At least, that's the hope.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398375
Posted Wednesday, December 19, 2012 7:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
GSquared

SELECT *
FROM dbo.SARGTest
WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);



It is my belief that this query gets a seek (i.e. is SARGable) as a function-around-a-column because Microsoft KNEW it would be happening SO MUCH that they baked it into the optimizer/engine.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1398384
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse