How do I make this query sargeable? 1

[logidude]

Hi everyone,

I recently read up on making query sargeable to improve performance. Up until now I really never considered this.

I have the following query, nothing complex but it will be run against many rows.

SELECT iRecordNumber, iRecordTypeID, COUNT(iRecordID) AS nbRecord
FROM  tblRecord  
WHERE     (fkCreatedBy = 1234) 

--Date filter
AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, dCreated))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getDate())))

GROUP BY iRecordNumber, iRecordTypeID
ORDER BY iRecordNumber

Now if I understood sargeable and I was to use an index on dCreated for that query it would totally ignore the index. But I'm not sure how to improve it, I just need to get todays record from a datetime ignoring the 'time' part on both side.

 

[gmmastros]

SELECT iRecordNumber, iRecordTypeID, COUNT(iRecordID) AS nbRecord
FROM  tblRecord  
WHERE     (fkCreatedBy = 1234)

--Date filter
AND dCreated >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
AND dCreated < DateAdd(Day, DateDiff(Day, 0, GetDate()), 1)

GROUP BY iRecordNumber, iRecordTypeID
ORDER BY iRecordNumber

It seems weird that an extra where clause condition would be faster, but it is. Trust me.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[logidude]

Thank you so much, exactly what I was trying to achieve.

It improved the speed quite a bit and also helped my understanding of sargeable.