December 16, 2012 at 11:53 pm
I have SP which contains function
convert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112)
this takes lots of time during the Market hours and it is very essential as well.
Is there any alternate which we can use for this function
December 17, 2012 at 12:20 am
So this is in a WHERE clause ??
Assuming so, this is what is known as a non-sargable condition.
Basically, as you have wrapped the datetime column in a function then SQL Server has to look at and process every row rather than use the more optimal route of an index.
Definition :
http://en.wikipedia.org/wiki/Sargable
One of the many blog articles available:
http://beyondrelational.com/modules/2/blogs/66/posts/9925/sargable-predicates.aspx
December 17, 2012 at 12:43 am
Yes it is being used in the where clause so what can be used instead of this fuction which will take less time
December 17, 2012 at 2:19 am
Did you read those articles ?
Example 3 on the beyondrelational one is very close to what you require.
December 17, 2012 at 6:27 am
mahesh.dasoni (12/16/2012)
I have SP which contains functionconvert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112)
this takes lots of time during the Market hours and it is very essential as well.
Is there any alternate which we can use for this function
Try this in your where clause:
([datetime] >= dateadd(dd, datediff(dd, 0, getdate()), 0) and
[datetime] < dateadd(dd, datediff(dd, 0, getdate()) + 1, 0))
Also, please note that your column name datetime is a very poor choice as it is also a reserved word.
The reason your current criteria is taking a long time to process is that SQL Server has to apply the convert function to every value of datetime in your table.
December 17, 2012 at 7:33 am
if the left side of the equal sign is a calculation, then the WHERE clause is "non sargeable", meaning that the optimizer can't use simple search arguments and must instead build an efficient query plan and must calculate every field in your table in this matter.
Maybe something like
-- midnight today
DECLARE @dFrom datetime = dateadd(dd, datediff(dd, 0, getdate()), 0)
-- 23:59:59 today
DECLARE @dTo datetime = DATEADD(dd, 1, @dFrom)
Then, in your WHERE clause:
[datetime] >= @dFrom AND [datetime] < @dTo
See if you get a better execution plan with this.
December 17, 2012 at 7:46 am
Andre Ranieri (12/17/2012)
if the left side of the equal sign is a calculation, then the WHERE clause is "non sargeable", meaning that the optimizer can't use simple search arguments and must instead build an efficient query plan and must calculate every field in your table in this matter.Maybe something like
-- midnight today
DECLARE @dFrom datetime = dateadd(dd, datediff(dd, 0, getdate()), 0)
-- 23:59:59 today
DECLARE @dTo datetime = DATEADD(dd, 1, @dFrom)
Then, in your WHERE clause:
[datetime] >= @dFrom AND [datetime] < @dTo
See if you get a better execution plan with this.
There is a very good chance that this will cause a table scan. A query plan is created only for data access statements (e.g. select, insert, update, delete, etc'). It completely ignores a variables assignment which is done in memory. In your example, the server will create a query plan without knowing the values of @dFrom and @dTo, so it will guess that 20% of the table's records will be selected. In the vast majority of times this will cause a table scan. It can work if you'll have the select statement in a different procedure and you'll send the values of @dFrom and @dTo as parameters to the procedure. This is because in the case of procedures and parameters, the server works with parameter sniffing.
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/
December 17, 2012 at 8:07 am
Something that many don't know about SARGability is that some functions, regardless of side of equation in a Where clause, are SARGable.
Try this:
SET NOCOUNT ON;
USE ProofOfConcept;
GO
CREATE TABLE dbo.SARGTest (
DT DATETIME NOT NULL);
GO
CREATE CLUSTERED INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);
GO
INSERT INTO dbo.SARGTest(DT)
SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())
FROM ProofOfConcept.dbo.Numbers AS N1
CROSS JOIN ProofOfConcept.dbo.Numbers AS N2;
GO
SELECT *
FROM dbo.SARGTest
WHERE DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DT < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1);
SELECT *
FROM dbo.SARGTest
WHERE CAST(DT AS DATE) = CAST(GETDATE() AS DATE);
Check the execution plans on both of the final queries. We know the first query will result in an index seek. It follows the usual rules for SARGability. What's surprising to many is that the second one, with CAST() on the left (and right) of the Where clause, also results in an index seek.
(Actual execution plans attached.)
Also tested:
DECLARE @s-2 DATE = GETDATE(), @E DATE = DATEADD(DAY, 1, GETDATE());
SELECT *
FROM dbo.SARGTest
WHERE DT >= @s-2 AND DT < @E;
Still get a seek. (See Plan2.sqlplan, attached.)
- 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
December 17, 2012 at 9:27 am
Andre Ranieri (12/17/2012)
if the left side of the equal sign is a calculation, then the WHERE clause is "non sargeable", meaning that the optimizer can't use simple search arguments and must instead build an efficient query plan and must calculate every field in your table in this matter.
That just simply isn't true. The sql engine does not care at all which side of the equal sign a given predicate is located. If it were that simple to make it sargable you could switch the order of the equals predicate. I know that even the wiki article mentions the left side of the equation but consider this from the wiki example.
Non-Sargable: Select ... WHERE Year(date) = 2012
If it were try that to make it sargable the function can't be on the left side then that is like saying that the following is sargable
WHERE 2012 = Year(date)
The above is no more sargable than the first but the left side of the equation is a constant and not a function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2012 at 12:15 pm
GSquared (12/17/2012)
Something that many don't know about SARGability is that some functions, regardless of side of equation in a Where clause, are SARGable.Try this:
SET NOCOUNT ON;USE ProofOfConcept;
GO
CREATE TABLE dbo.SARGTest (
DT DATETIME NOT NULL);
GO
CREATE CLUSTERED INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);
GO
INSERT INTO dbo.SARGTest(DT)
SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())
FROM ProofOfConcept.dbo.Numbers AS N1
CROSS JOIN ProofOfConcept.dbo.Numbers AS N2;
GO
Also tested:
DECLARE @s-2 DATE = GETDATE(), @E DATE = DATEADD(DAY, 1, GETDATE());SELECT *
FROM dbo.SARGTest
WHERE DT >= @s-2 AND DT < @E;
Still get a seek. (See Plan2.sqlplan, attached.)
I guess that the quoted part was written because of my previous message about the fact that using variables, assign them values and then use them in the same scope in the where clause will cause the server to do a table scan instead of seek operation. If I'm correct, then let be clearer about it. If you use a clustered index, then seek operation will be used. If you'll use an appropriate none clustered index that is also a covering index to this query, then you'll get seek operation. If you'll use none clustered index that is not covering, then most times you will get scan operation. In your example you had a table with one column and a clustered that is based on this column. Here is an example that is based on the code that you've added to your message:
CREATE TABLE dbo.SARGTest (
DT DATETIME NOT NULL, filler char(1) default ('a'));
GO
INSERT INTO dbo.SARGTest(DT)
SELECT TOP (1000000) DATEADD(DAY, CHECKSUM(NEWID())%10000, GETDATE())
FROM sys.objects
CROSS JOIN sys.objects as s2
CREATE INDEX CID_SARGTest_DT ON dbo.SARGTest(DT);
DECLARE @s-2 datetime
declare @E datetime
SET @s-2 = GETDATE()
SET @E = DATEADD(DAY, 1, GETDATE())
--Using the varibles cause table scan
SELECT *
FROM dbo.SARGTest
WHERE DT >= @s-2 AND DT < @E;
--Using the same values but without the varibles use a table seek
SELECT *
FROM dbo.SARGTest
where DT > GETDATE() AND DT < DATEADD(DAY, 1, GETDATE())
go
drop table SARGTest
--------------------------------------------------------------
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/
December 17, 2012 at 12:25 pm
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
December 17, 2012 at 1:06 pm
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/
December 17, 2012 at 1:23 pm
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
December 17, 2012 at 2:04 pm
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/
December 18, 2012 at 6:47 am
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].
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply