datetime to integer

I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.Without knowing more my knee-jerk reaction would be to suggest sticking with the native datetime datatype. Can you give some more information describing what you are trying to accomplish?|||We're importing hundreds of thousands of records into hundreds of tables. All of it is time series data. The collected data is then analyzed using an external VB.NET program with dozens of custom analysis functions. The results of these calculations are saved into additional tables which are created on the fly. Some of the data in the resulting tables are reused in the custom functions. Results will eventually be compiled into a data warehouse from which additional analyses will be conducted (SQL Server Analysis Services, Data Mining, and SPSS). I should mention that the datetime values are unique (primary key candidate?); no table can have duplicate datetime values. Also, datetime values are essential in many of the analysis functions. I was told that if datetime values were converted to integers, the SQL Server 2005 performance improvement would more than outweigh the cost of conversion back and forth in the VB.NET functions.|||

Ahh, now we get down to it. When you say date/time is unique in all tables, my knee-jerk reaction is "Really? How are you assuring that" But know I am lead to believe that the source of the data is a data source that supplies more date/time precision than SQL Server records natively.

Key question: To what precision are you maintaining your date/time information?

Key question: How are you assuring that all date/time fields are unique? This is not a common guarantee in SQL Server.

|||

The data comes from several "clean" sources. The datetime field is set as a primary key. During import, checks for duplicate datetime values are made. After import, the data is cleaned again.

Most of the data is in one minute intervals.

When you say uniqueness is not a common guarantee, are you saying that setting a field to primary key or unique does not work properly in SQL Server?

|||If your granularity is finer than millisecond granularity then you will need an alternative to native datetime datatype. The native datetime datatype only tracks to 1/300th of a second.|||Most of the data is at one minute or higher (10 minute, hourly, etc.) levels. The possibility exists of sampling data as fine as one second intervals. Nothing will be done at the millisecond level.|||I think I would stick with native datetime; can we get additional opinions please?|||

If I recall correctly, internally SQL Server stores datetime values as a decimal number comprised of a (left side) four byte integer representing the number of days since Day 0 (Jan 01, 1900) , separating decimal, and a (right side) four byte integer representing the number of milliseconds since midnight.

So while not stored as an Integer, it is stored in the 'next best thing'.

|||Yes, however, that is NOT to the nearest millisecond but to the nearest 1/300 of a second; it is a legacy from Sybase.sql

Datetime function slowdowns

The function below is a bottleneck and I wonder if there is anything that can
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0--Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then--Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
Regards,
Jamie
Jamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie

Datetime function slowdowns

The function below is a bottleneck and I wonder if there is anything that ca
n
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that
can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate)
- 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie

Datetime function slowdowns

The function below is a bottleneck and I wonder if there is anything that can
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference = case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference => case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie