SQL Server Efficiency – Which SQL Piece is Most Efficient?

datesql serversql-server-2012time

I need to add the date part of GetDate() to the time part of a stored DateTime.

Naively I thought I could do the following:

declare @testTime DateTime = '2013-04-23 13:55:06'
select cast(getdate() as date) + cast(@testTime as time)

but this gives the following error:

Operand data type date is invalid for add operator.

I did some more research and have come up with three possible ways of doing the calculation:

declare @testTime DateTime = '2013-04-23 13:55:06'

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

All three produce the same result but I'm not 100% happy about implementing any of them, though the third way seems to be a better way – though I have no evidence for this.

Which (if any) of the three is the most efficient?

Is there a better way?

Best Answer

You could use DATETIMEFROMPARTS (Transact-SQL)

select datetimefromparts(
                        datepart(year, getdate()), 
                        datepart(month, getdate()), 
                        datepart(day, getdate()), 
                        datepart(hour, @testTime), 
                        datepart(minute, @testTime), 
                        datepart(second, @testTime),
                        datepart(millisecond, @testTime)
                        )

Update:

The first two of your queries.

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

require that the database compatibility level is SQL Server 2008(100). If they are executed in compatibility level (110) or (120) you will get

Msg 402, Level 16, State 1, Line 3 The data types datetime and time are incompatible in the add operator.

so you should not consider those at all.

The last query

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

works just fine.

I did a test over 1000000 rows in SQL Server 2014 and the datetimefromparts version took 650 ms and dateadd/datediff took 350 ms.

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 – Constructing datetime from 2 fields: one integer and a datetime

So, you're going to be parsing strings and ints and doing all kinds of conversions, so I don't think you're going to really save anything by seeking out a "more efficient" way to turn crap data into good data - even on billions of rows. An alternative might be to avoid converting anything to a string at all (which is notoriously bad for performance):

DECLARE @dt DATETIME = '20130904', @t INT = 192423998;

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  @dt);

Of course, keep in mind that the milliseconds your trading system passes in can round in either direction when using datetime (.998 isn't possible and becomes .997, for example), so you may want instead:

SELECT DATEADD(MILLISECOND, @t % 100000 -- milliseconds
  + 60000 * (@t / 100000 % 100) -- minutes converted to milliseconds
  + 60000 * 60 * (@t / 10000000), -- hours converted to milliseconds
  CONVERT(DATETIME2(3), @dt));

I ran these each 100,000 times, and compared to your existing approach. The difference?

Your approach:               41.8 seconds
My approach (no convert):    40.5 seconds
My approach (with convert):  38.9 seconds

Now, this should not be extrapolated to mean that on 1 billion rows my method will save you (13,000 or 31,000) seconds, as the translation of 100,000 batches to operating on 100,000 rows in a single statement will not be linear. But it could be worth testing (you have the data necessary to test this; I would just be making stuff up, and I also probably have processors that have different performance characteristics when it comes to floating point operations etc., so anything else I could offer would not necessarily be relevant).

I still think you're better off taking the hit, fixing the design, and intercepting the incoming trading data and correcting it. As this system gets bigger, these translations and conversions are going to hurt more, and more, and more...

Best Answer

Related Solutions

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

Sql-server – Constructing datetime from 2 fields: one integer and a datetime

Related Question