Jay Jay - 3 months ago 9
SQL Question

DATEADD function have severe performance degradation on linked server query

I am running a query from our on premise SQL server which is linked to an azure database via a linked server and I run the below query in milliseconds:

SELECT *
FROM [Cloud].[website].[dbo].[Topup]
WHERE CAST(senttovpn AS DATE) = '7 aug 2016'
GO


Due to the server time being 1 hour behind our current time I want to add an hour to the timestamp and so run the following query:

SELECT *
FROM [Cloud].[website].[dbo].[Topup]
WHERE CAST(DATEADD(hour, 1, senttovpn) AS DATE) = '7 aug 2016'
GO


This however takes 1 minute and 19 seconds to run. Can anyone tell me why this degradation is so severe a what is the best SQL solution to this?

Answer

Use SARGable predicates and ISO-8601 time format to defind the range values.

SELECT *
  FROM [Cloud].[website].[dbo].[Topup]
  WHERE senttovpn >= cast('20160806 23:00:00.000' as datetime)
    AND senttovpn < cast('20160807 23:00:00.000' as datetime)
GO
Comments