CODEWITHSUNDEEP
sql-serversql-server-2008
user1810575
user1810575

Reputation: 831

SQL Optimization where clause

Instead of using function in the where clause can we do something different.

DateAdd taking time poor performance i guess..

How to optimize this sql 

SELECT cust_id, order_date, price
FROM customers
WHERE DATEADD(DD,50,order_date)>=GETDATE()

Upvotes: 2

Views: 124

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Don't run your function on order_date, run the inverse on getdate() instead

select cust_id, order_date, price
from customers
where order_date>=dateadd(Day,-50,getdate())

Function calls on order_date are going to cause an index scan, if you instead run your function on the filter criteria getdate() you can preserve an index seek on this column. (If it has an index).

SARGable functions in SQL Server - Rob Farley

Upvotes: 7

Related Questions