Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290

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: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
dateadd is not deprecated.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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