Problem with Datetime Function

  • 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

  • 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



    Clear Sky SQL
    My Blog[/url]

  • Yes it is being used in the where clause so what can be used instead of this fuction which will take less time

  • Did you read those articles ?

    Example 3 on the beyondrelational one is very close to what you require.



    Clear Sky SQL
    My Blog[/url]

  • mahesh.dasoni (12/16/2012)


    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

    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.

  • 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.

  • 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/

  • 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

  • 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/

  • 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/

  • 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

  • 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/

  • 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

  • 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/

  • 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