Post reply

Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan.

  • January 16, 2014 at 10:17 am

    #302857

    The DATEADD function is Deprecated.

    What is the best way to achieve the same results without adversely impacting on performance?

    WHERE CD_DW_INSERT_DATE > DATEADD(DAY, -7,@Date);

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    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/

  • January 16, 2014 at 10:20 am

    #1680756

    dateadd is not deprecated.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 16, 2014 at 10:31 am

    #1680764

    Lowell (1/16/2014)

    dateadd is not deprecated.

    I run it through ubitsoft's T-SQL Analyzer and I get the following:

    SA0028 : [Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan.

    I'm not sure why I got that error.

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    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/

  • January 16, 2014 at 12:06 pm

    #1680814

    Welsh Corgi (1/16/2014)

    Lowell (1/16/2014)

    dateadd is not deprecated.

    I run it through ubitsoft's T-SQL Analyzer and I get the following:

    SA0028 : [Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan.

    I'm not sure why I got that error.

    Thanks.

    Must be a bug with ubitsoft's reporting because, I agree, DATEADD is not deprecated.

    Removal of functions wrapped around columns in the WHERE clause is, however, a great idea.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 16, 2014 at 12:51 pm

    #1680830

    I removed the function from the WHERE and replaced it with a local variable:

    AND CD_DW_INSERT_DATE > @Date

    I got another problem on a JOIN.

    Found filtering columns wrapped inside User-Defined Function call.

    AND PTD.Ptd_trans_process_date = CypressStaging.dbo.Fncconvertas400date(ExtCov.Last_change_date)

    The function dbo.Fncconvertas400date converts AS400 Date format to SQL Server Date.

    I do not know if and what I could do for this?

    Thanks again.

    For better, quicker answers on T-SQL questions, click on the following...
    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/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply