Michael Dinowitz wrote: > I was trying to get all of the records for a specific day from a large DB > and it was taking forever even though the created (datetime) field was > indexed. DateDiff(d, created, @datein) took waaay too long.
It doesn't use indexes because that is not an indexable condition (and the execution plan should tell you that). A standard B+tree can resolve the operators =, <>, > and < but can not hope to resolve a function with 3 parameters of which 2 are not specific to the record. If you want this query to use indexes, you have to rewrite it to something like: WHERE created BETWEEN DateAdd(d, x, @datein) AND DateAdd(d, y, @datein) The database will resolve the 2 functions once during the planning or on the start of execution and then reduce the predicate to a simple BETWEEN which can be looked up usig an index (range) scan. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2442 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
