Select TOP not completing the query?

Question

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.

I think it is a hardware problem, SQL can query that in less than a sec even without an index for 440 records unless you have millions/billions. — Jade, Feb 21 '14 at 05:46
@PreetSangha already it is indexed it works ok when `top` number of records are within it's reange but not when it exceeds the range — , Feb 21 '14 at 05:46
@Jade yes my records are over billions in that table if it was hardware problem it should have given that on the other queries also — , Feb 21 '14 at 05:47
You should ask this question on dba.stackexchange.com the guru's there will know what diagnoses you should be doing. — Preet Sangha, Feb 21 '14 at 05:52
Yes, now its clearer, I think you should do table partitioning, or add new field that hold the yearOfcreation and make an index on it, the following function in your where condition YEAR(CreationDate) and DATEDIFF(MONTH,CreationDate,GETDATE()) >= 6 will defeat your index and optimizer — Jade, Feb 21 '14 at 05:52
@Jade yes now when I read about the `SARGable` as suggested by `Satheesh` i think that might be the problem i will change the conditions and check it out but i feel now it will work. Thanks for the Help. — , Feb 21 '14 at 05:56
@PreetSangha Thanks mate for suggesting but `Satheesh` solution is working the issue was the functions in where conditions are defeating my indexing and optimizer so it was taking the forever to run.Thanks for help — , Feb 21 '14 at 05:58

score 3 · Accepted Answer · edited May 23 '17 at 11:57

3

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 ?

edited May 23 '17 at 11:57

Community

1
1

answered Feb 21 '14 at 05:36

Satheesh Variath

680
4
10

I will try it and let you know – Feb 21 '14 at 05:48
Hey thanks mate i dont even know the world `SARGable` and thanks for that word it's my word for today. And yes your query worked thanks – Feb 21 '14 at 05:59

Select TOP not completing the query?

1 Answers1