It looks like, based on 4-part naming, that you are running this across a linked server. You may get some better performance by using OPENQUERY/OPENROWSET or creating and SP on the linked server. This offloads all the processing to the linked server.

You can improve performance by removing the conversion of the date column to a varchar as it not only causes at best an index scan but it also then does an implicit convert right back to datetime. For example if you run this code:

CREATE TABLE #test (date_col DATETIME PRIMARY KEY)

DECLARE @date1 DATETIME,

@date2 DATETIME

SELECT

@date1 = DATEADD(DAY, -100, GETDATE()),

@date2 = DATEADD(DAY, -60, GETDATE())

INSERT INTO #test

(

date_col

)

SELECT TOP 365

DATEADD(DAY, -(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), GETDATE())

FROM

sys.all_columns AS AC

SELECT

*

FROM

#test

WHERE

CONVERT(varchar, date_col, 101) BETWEEN @date1 AND @date2

SELECT

*

FROM

#test

WHERE

date_col BETWEEN @date1 AND @date2

DROP TABLE #test

If you look at the execution plans for the 2 selects from #test you see that the query with the conversion does a clustered index scan and has a predicate of

CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[tempdb].[dbo].[#test].[date_col],101),0)>=[@date1] AND

CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[tempdb].[dbo].[#test].[date_col],101),0)<=[@date2]

while the query without the conversion does a clustered index seek and the predicate is:

Start: [tempdb].[dbo].[#test].date_col >= Scalar Operator([@date1]),

End: [tempdb].[dbo].[#test].date_col <= Scalar Operator([@date2])