Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan. Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 10:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 7:07 AM
Points: 4,944, Visits: 4,754

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/

Post #1531684
Posted Thursday, January 16, 2014 10:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 14,300, Visits: 37,360
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!
Post #1531687
Posted Thursday, January 16, 2014 10:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 7:07 AM
Points: 4,944, Visits: 4,754
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/

Post #1531695
Posted Thursday, January 16, 2014 12:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 40,524, Visits: 37,752
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1531756
Posted Thursday, January 16, 2014 12:51 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 7:07 AM
Points: 4,944, Visits: 4,754
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/

Post #1531772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse