SQL Server Performance

Using Cast will affect performance?

Discussion in 'T-SQL Performance Tuning for Developers' started by Balasundaram, Mar 23, 2005.

  1. Balasundaram New Member

    Hi,<br /><br />I have a syntax below to specify a condition...<br /><br />and (<b>cast</b>(left(stat_period,4)+'-'+substring(<b>cast</b>(stat_period as varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />),5,2)+'-01'as datetime)) <b>&gt;=</b> <br />dateadd(mm,-25,(dateadd(dd,-(day(getdate())-1), DATEADD(d,DATEDIFF(d,0,getdate()),0))))<br /><br />In the query the left hand side has two cast function and on the right hand side i am not using the cast instead using available date functions in sql server.<br /><br />My question, if I use available date functions on left hand side by removing cast, will the performance increase? <br /><br />Please suggest me as I need to implement this in production.<br /><br />Also suggest any articles regarding using date,cast & convert functions.<br /><br />Thanks & Regards,<br />Bala
  2. ranjitjain New Member

    I feel replacing cast with another sql date function will not affect your code performance by much.
  3. Adriaan New Member

    One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn't need casting in your query, as it produces a true date:
    DATEADD(m, <month> - 1, DATEADD(d, <day>, DATEADD(yyyy, <year> - 1900, 0)))

    The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC).

    Since you're looking for the first of the month, you can do just the month and year:
    DATEADD(m, RIGHT(@stat_period, 2) - 1, DATEADD(yyyy, LEFT(@stat_period, 4) - 1900, 0))

    Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query.

    Seeing that your stat_period is a formatted string like 'yyyy-mm', you could probably also use this:

    DECLARE @calc_date CHAR(10)
    SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
    ................................................
    WHERE (stat_period + '-01') >= @calc_date

    You'll have to see which one performs better.
  4. Balasundaram New Member

    Adrian,

    Thanks a lot,

    I am using the following syntax as you suggested. It does make sense to use this way.
    DECLARE @calc_date CHAR(10)
    SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)

    -Bala





    quote:Originally posted by Adriaan

    One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn't need casting in your query, as it produces a true date:
    DATEADD(m, <month> - 1, DATEADD(d, <day>, DATEADD(yyyy, <year> - 1900, 0)))

    The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC).

    Since you're looking for the first of the month, you can do just the month and year:
    DATEADD(m, RIGHT(@stat_period, 2) - 1, DATEADD(yyyy, LEFT(@stat_period, 4) - 1900, 0))

    Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query.

    Seeing that your stat_period is a formatted string like 'yyyy-mm', you could probably also use this:

    DECLARE @calc_date CHAR(10)
    SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
    ................................................
    WHERE (stat_period + '-01') >= @calc_date

    You'll have to see which one performs better.
  5. Balasundaram New Member

    Hi Adrian,

    Left hand side I have date time and the right hand side we have converted into char(10)...while checking the condition does it affect any performance?


    DateTime >= Char(10)

    Regards,
    Balasundaram



    quote:Originally posted by Adriaan

    One way of constructing a date from bits of information is by nesting a few DATEADDS. This doesn't need casting in your query, as it produces a true date:
    DATEADD(m, <month> - 1, DATEADD(d, <day>, DATEADD(yyyy, <year> - 1900, 0)))

    The only precaution with this method is that you cannot have the day-part as the innermost DATEADD item, because the whole thing will fail somewhat unpredictably (on leap days, IIRC).

    Since you're looking for the first of the month, you can do just the month and year:
    DATEADD(m, RIGHT(@stat_period, 2) - 1, DATEADD(yyyy, LEFT(@stat_period, 4) - 1900, 0))

    Your query may cover a large number of rows in your base table, and so you should take out the whole second part of the criteria: the calculation will return a single value regardless of the data in the row, but it would still be repeated for each row in your base table. So do that calculation before the main query, store the result in a date variable, and use the variable in your query.

    Seeing that your stat_period is a formatted string like 'yyyy-mm', you could probably also use this:

    DECLARE @calc_date CHAR(10)
    SET @calc_date = CONVERT(CHAR(10), DATEADD(mm,-25,(DATEADD(dd,-(DAY(GETDATE())-1), DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))), 120)
    ................................................
    WHERE (stat_period + '-01') >= @calc_date

    You'll have to see which one performs better.
  6. Adriaan New Member

    Hi Balasundaram,

    As I said, you'll have to see which one performs better. The options involve either date calculation or string concatenation, neither of which is ideal when used in criteria.

Share This Page