CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlsql-server-2008-r2
user2721874
user2721874

Reputation:

Select TOP not completing the query?

I have 440 rows in table which matches with my following query 

  SELECT RecordID 
  FROM  
      [tblRules]
  WHERE 
       DATEDIFF(MONTH,CreationDate,GETDATE()) >=  6 
   AND YEAR(CreationDate) = 2013 
  ORDER BY  RecordID , BR_ID

When i execute this query then i get 440 rows in 4 sec

when i do the top like below ,

  SELECT TOP 440 RecordID 
  FROM  
      [tblRules]
  WHERE 
       DATEDIFF(MONTH,CreationDate,GETDATE()) >=  6 
   AND YEAR(CreationDate) = 2013 
  ORDER BY  RecordID , BR_ID

it returns the 440 rows in 7 sec

but when i set high top number then it runs forever without returning the result i give it a full runtime it run till the memory buffer of the server got full that is for 5 hours the query i run was 

  SELECT TOP 500 RecordID 
  FROM  
      [tblRules]
  WHERE 
       DATEDIFF(MONTH,CreationDate,GETDATE()) >=  6 
   AND YEAR(CreationDate) = 2013 
  ORDER BY  RecordID , BR_ID

even when i replace 500 with anything more than 440 then it goes running and never returns the result. I struggled with a quit lot can any one give me any solution or reason behind why this is happening.All other tables in that database are working fine.And the table tblRules have more than 1 billion records in it.

Upvotes: 2

Views: 273

Answers (1)

Satheesh Variath
Satheesh Variath

Reputation: 680

My first observation is the functions used in the where clause. That would prevent the optimizer to use any available index on CreationDate.

To enable the optimizer to efficiently use the index the query should be SARGable

What makes a SQL statement sargable?

declare @yeartoget varchar(4)='2013'
SELECT TOP 440 RecordID 
FROM                         
    [tblRules]
WHERE 
    CreationDate >=  DATEADD(MONTH,-6 ,getdate())
    AND CreationDate 
 between convert(datetime,convert(varchar(4),@yeartoget)+'0101') and          
         convert(datetime,convert(varchar(4),@yeartoget)+'1231')
 ORDER BY  RecordID , BR_ID

does this version make any difference ?

Upvotes: 3

Related Questions