Optimizing WHERE clause with DATE fields
-
Hello,
Has anyone met this before? It appears to be an undocumented peculiarity when searching date fields between two values. Queries seem to run significantly faster if you first compare the date field to itself before entering the rest of the comparison.
Here is an example searching on a field called StatementDate. On my tests, irrespective of table size, table structure, or index configurations, the second syntax is always faster.
Perhaps the WHERE DateField = DateField syntax compels the optimizer to recognize a date field.
Standard slow syntax example:
SELECT TOP 500 Client_ID, StatementDate, Status
FROM Client
WHERE StatementDate IS NOT NULL
AND DateCreated BETWEEN CONVERT(VarChar(11),Getdate()-100,106) AND CONVERT(VarChar(11),Getdate(),106)
Speedy syntax example:
SELECT TOP 500 Client_ID, StatementDate, Status
FROM Client
WHERE StatementDate IS NOT NULL
AND (DateCreated = DateCreated AND DateCreated BETWEEN CONVERT(VarChar(11),Getdate()-100,106) AND CONVERT(VarChar(11),Getdate(),106))
-
Interesting. How much faster? Slightly, or realy worth looking into? etc?
Once you understand the BITs, all the pieces come together
-
I tested similar queries of yours with 500000 records and do not see any differences.
SELECT TOP 500000 *
FROM webproxylog
WHERE logdate BETWEEN CONVERT(VarChar(10),Getdate()-100,126) AND CONVERT(VarChar(10),Getdate(),126)
SELECT TOP 500000 *
FROM webproxylog
WHERE (logdate = logdate AND logdate BETWEEN CONVERT(VarChar(10),Getdate()-100,126) AND CONVERT(VarChar(10),Getdate(),126))
-
It is majorly faster in some cases ... but it makes only slight difference in other cases.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply