SQL Query running Slow

Question

I have a customer table in sql server 2005 with 6588395 records, I need to retreive records from customer table based on creation date. I have created clustered index on creation date. Through linked server , I am retrieving the records from this table. The query I have used is

@StartDate=N'01 Jan 2016',@EndDate=N'31 Mar 2016'

select 
   firstname, 
   lastname,
   Address,
   Addrss1,
   city    
from  
   [ABC\SQCENTRE].[Reports].[dbo].[tbl_Customer]  
where      
   creation_date   BETWEEN  @startdate AND  @EndDate

This query is taking 30 minutes to retrieve the records. Can you advise how to optimise the query.

I have tried openquery also, that also did not makes any difference. Creation_date is a datetime field

Any help appreciated.

How fast/slow does the query run directly on the other server? Do you really save the dates as nvarchar? — swe, Commented Sep 22, 2016 at 5:36
How long does the query take when running locally on the remote server? How many records does the query return? Need to determine whether the problem is due to bandwidth or query optimisation. Since you say OPENQUERY doesn't help then it's either one of these. If you can rule out bandwidth, then look at the execution plan on the remote server. — innomatics, Commented Sep 22, 2016 at 5:54
provide execution plan as xml,schema of tables involved and count — TheGameiswar, Commented Sep 22, 2016 at 6:09

swe · Accepted Answer · 2016-09-22 07:39:05Z

1

So if createdate is datetime set your vars to a datetime value not varchar...

declare @Startdate datetime; @Startdate = convert(datetime, 'mydate')

SQLServer will compare the value in the table to your comparison-values. So I don't know if he will cast your searchvalue to datetime or cast 7 million datetimes to varchar to compare. So thats why i would be sure to EXPLICIT cast my search expression to datetime to be sure, the server does NOT cast 7 million date values to varchar.

The 7million casts in RAM oder tempdb could explain why the openquery (which is for sure local evaluation of the where-clause) needs 30 minutes. This would be easier to say if we had an execution plan.

edited Sep 22, 2016 at 7:39

answered Sep 22, 2016 at 5:45

swe

1,45516 silver badges28 bronze badges

Shouldn't SQL Server do this automatically?
– qxg
Commented Sep 22, 2016 at 7:33

Add a comment |

Collectives™ on Stack Overflow

SQL Query running Slow

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related