CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1554650
user1554650

Reputation: 47

SQL Query running Slow

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.

Upvotes: 1

Views: 107

Answers (1)

swe
swe

Reputation: 1455

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.

Upvotes: 1

Related Questions