Sql-server – Advice with date column SQL Server 2005

performancequerysql-server-2005

I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):

WITH Dates AS ( 
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
) 
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Now this results in a full Clustered Index Scan (surprise, surprise). I am trying to think of ways of making this faster (the actual query takes 3 mins :O). I have thought of doing the following but haven't tried any yet as I ran out of time earlier:

Use a computed column with just the date part
Index said computed column (Not sure if this is even possible?)
Use an Indexed view (Again, not sure if this is possible, will work?)

Easiest way would be to update the column and remove all time information, but I cannot do this 🙁

Any ideas?

Update

Thanks for all of the answers so far. I think the point of the question was missed because I was slightly unclear on what I wanted. My bad. I was only aiming to optimise the date conversion part of the query as the amount of data i'm dealing with is tiny in reality ( < 500,000 after the cross apply with a year long date range). Sorry for the confusion on this.

For those optimising the rest of the query for me, I can see what people are saying by using < > but consider the following:

The parameters that are passed in is a date range (eg. 1st to the end of this month)
The start date in the table can appear before or during the parameters date range (eg. only the end date is in the date range)
The end date in the table can appear during or after the parameters date range (eg. only the start date is in the date range)
Lastly, the start and end date in the table is in the parameters date range.

Personally, given the above, I could never get a < > solution to work. The only way I could get it to work properly and not miss anything is by using a CTE and saying where d.[Date] BETWEEN t.StartDate AND t.EndDate.

I hope this clears things up. Thanks again.

Best Answer

You can do steps 1 and 2: but use the DATEADD/DATEDIFF technique as per this: How to the get current date without the time part

You will most likely be unable to index the computed column because it won't be deterministic with the varchar method

Related Solutions

Sql-server – more concise way to convert a UTC datetime into a local date only

If you're not averse to having a function do the dirty work, this helps make the statement cleaner:

CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

You could then do something like:

SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);

This does, of course, make the statement non-SARGable.

If sargability is of primary concern due to the large number of records you may have, you could create a materialized view with an index on the calculated field.

EDIT:

Per @RichardThekiwi the SARGability of this particular statement is not affected by use of the function since the function is not part of either a JOIN or a WHERE clause. Where the function is used, it is ran after any indexes would have been used.

ALSO, not the above code will truncate any time portion of the date input to the function (this is by design). Therefore, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45)

Sql-server – Clustered Index and Primary Key on table with unique identifier and date

The main characteristic of the clustered index is that the data is all in that order. That allows SQL to "read ahead". So for example if you created your CI on the Date column and ran queries pulling summary info for a week or a month then SQL could pull the data more quickly.

If on the other hand you are having to do seeks (a single Id for example) then the CI is no different than a covering NCI. Note the covering part of that.

Here is something you could try. Place your CI on the Date, OrderId combination. Do this because you are more likely to pull range data on a date than an Id. Also because you said that 80% of your queries use the date. Some portion of these will presumably also use the OrderId.

Then see what columns the queries that just use OrderId use and add those columns to an OrderId NCI using the INCLUDE clause. And just to save time if the answer is "they are using all of the columns on these queries" then a) you need to look at those queries and see if they really need all that data and b) yes you could INCLUDE all of the columns in the table.

I realize that would be a HUGE INCLUDE. In fact it would double the size of your table and you'll want to carefully test that you aren't going to have a serious negative impact on write operations. However for reads it should work just fine.

Best Answer

Related Solutions

Sql-server – more concise way to convert a UTC datetime into a local date only

Sql-server – Clustered Index and Primary Key on table with unique identifier and date

Related Question