September 24, 2022 at 11:52 am
Gosh, Eirikur... now you've got me asking the same as what Thomas asked...
That's an awful lot of implicit (Reverse) and explicit conversions just to handle some negative time stamps only to resort to the same integer math as what was presented in the article, which also ultimately stored it as that same binary form behind the scenes. With that, I have to ask, is it faster? What is the advantage of the complexity here other than to impress the "assembly language affection-ado" in a fellow old fart like me or demonstrate a good knowledge of the "0001" Epoch and the underlying "Big Endian" mess MS made of things behind the scenes?
There is a method in the madness Jeff and I’m addressing one of my pet peeves, which is the big-endian construct of the DATETIME2 data type. I’m using REVERSE here as the most self-explanatory (simple) way to write the code, will replace that with proper bit-bashing when I get the time.
😎
The good thing is that there is a lot of room for improvement. A conversion from a binary value to any given data type is one of the most efficient operations in T-SQL, I’m confident that if one didn’t have to reverse the byte order, that would blow the socks off most other approaches to the problem 😉
September 24, 2022 at 12:06 pm
Slightly improved version, a test harness and the results from my modest little laptop.
😎
The function:
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVF_UXTS_TO_DT2B
(
@UXTimeStamp BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
/*
---------------------------------------------------------------------
TODO: Complete commenting!
---------------------------------------------------------------------
Constructing a DATETIME2(3) binary compatible value for a conversion
from BIGINT.
---------------------------------------------------------------------
The DATETIEM2(3) construct:
---------------------------------------------------------------------
HEX 0x | IGNORE
PRECISSION 03 | FIXED FOR (3), 1 byte added if precission is defined, 0x00 defaults to precission of (7)?
TIME B4B3B2B1 | BIGENDIAN 4 BYTES
DATE B3B2B1 | BIGENDIAN 3 BYTES
---------------------------------------------------------------------
Minimal input value: -62135596800000 0001-01-01 00:00:00:000
Maximum input value: 253402300799999 9999-12-31 23:59:59:999
---------------------------------------------------------------------
*/
RETURN
-- TEST VARIABLE
--DECLARE @UXTimeStamp BIGINT = -1;
WITH TIME_BASE(TIMEBASE) AS
---------------------------------------------------------------------
-- Shifting the base from the "UNIX Epoch" to the "Windows Epoch" by
-- using the difference between the two in milliseconds.
---------------------------------------------------------------------
(
SELECT
CONVERT(BIGINT,(@UXTimeStamp + CONVERT(BIGINT,62135596800000,0)),0) AS TIMEBASE
)
SELECT
---------------------------------------------------------------------
-- High level conversion of a big endian binary value to DATETIME2
---------------------------------------------------------------------
CONVERT
(
DATETIME2(3)
,CONVERT
(
VARBINARY(8)
,REVERSE
(
CONCAT
(
CONVERT
(
VARBINARY(3)
,((TB.TIMEBASE / 86400000))
,0
)
,CONVERT
(
VARBINARY(4)
,(TB.TIMEBASE % 86400000)
,0
)
,0x03
)
)
)
,0
)
AS WinDateTime2
FROM TIME_BASE TB
GO
The test harness:
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @SAMPLE_SIZE BIGINT = 1000;
-- Minimum value: -62135596800000
-- Maximum value: 253402300799999
DECLARE @TIMER TABLE (
T_TXT VARCHAR(50)
,T_TS DATETIME2(7) DEFAULT (SYSDATETIME())
,T_CPU FLOAT DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IDLE FLOAT DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IO FLOAT DEFAULT (@@IO_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
);
DECLARE @DT2BUCKET DATETIME2(3) = NULL;
INSERT INTO @TIMER(T_TXT) VALUES ('msUnixTStoDATETIME2');
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
@DT2BUCKET = U2W.TheDateTime
FROM NUMS NM
CROSS APPLY dbo.msUnixTStoDATETIME2(NM.N - (@SAMPLE_SIZE / 2)) U2W
ORDER BY NM.N ASC;
INSERT INTO @TIMER(T_TXT) VALUES ('msUnixTStoDATETIME2');
INSERT INTO @TIMER(T_TXT) VALUES ('ITVF_UXTS_TO_DT2');
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
@DT2BUCKET = U2W.WinDateTime2
FROM NUMS NM
CROSS APPLY dbo.ITVF_UXTS_TO_DT2(NM.N - (@SAMPLE_SIZE / 2)) U2W
ORDER BY NM.N ASC;
INSERT INTO @TIMER(T_TXT) VALUES ('ITVF_UXTS_TO_DT2');
INSERT INTO @TIMER(T_TXT) VALUES ('ITVF_UXTS_TO_DT2B');
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
@DT2BUCKET = U2W.WinDateTime2
FROM NUMS NM
CROSS APPLY dbo.ITVF_UXTS_TO_DT2B(NM.N - (@SAMPLE_SIZE / 2)) U2W
ORDER BY NM.N ASC;
INSERT INTO @TIMER(T_TXT) VALUES ('ITVF_UXTS_TO_DT2B');
SELECT
T.T_TXT
,MAX(@SAMPLE_SIZE) AS SAMPLE_SIZE
,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU
,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE
,MAX(T.T_IO) - MIN(T.T_IO) AS IO
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @TIMER T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;
The results:
T_TXT SAMPLE_SIZE CPU IDLE IO DURATION
--------------------- ------------- ----------- ------------ ------- -----------
ITVF_UXTS_TO_DT2 1000 0 0 0 0
ITVF_UXTS_TO_DT2B 1000 0 0 0 0
msUnixTStoDATETIME2 1000 0 0 0 0
--------------------- ------------- ----------- ------------ ------- -----------
ITVF_UXTS_TO_DT2B 10000 0 0 0 0
msUnixTStoDATETIME2 10000 0 0 0 0
ITVF_UXTS_TO_DT2 10000 0 0 0 9931
--------------------- ------------- ----------- ------------ ------- -----------
msUnixTStoDATETIME2 100000 31250 31250 0 18693
ITVF_UXTS_TO_DT2 100000 0 250000 0 29895
ITVF_UXTS_TO_DT2B 100000 31250 218750 0 31292
--------------------- ------------- ----------- ------------ ------- -----------
msUnixTStoDATETIME2 1000000 218750 1531250 0 246131
ITVF_UXTS_TO_DT2B 1000000 250000 1750000 0 268506
ITVF_UXTS_TO_DT2 1000000 281250 1968750 0 269379
--------------------- ------------- ----------- ------------ ------- -----------
msUnixTStoDATETIME2 10000000 2187500 15312500 0 2249760
ITVF_UXTS_TO_DT2B 10000000 2687500 18812500 0 2759611
ITVF_UXTS_TO_DT2 10000000 2718750 19031250 0 2770465
--------------------- ------------- ----------- ------------ ------- -----------
msUnixTStoDATETIME2 100000000 22625000 158375000 0 23191822
ITVF_UXTS_TO_DT2B 100000000 28687500 201031250 31250 29419572
ITVF_UXTS_TO_DT2 100000000 28781250 201718750 0 29480239
--------------------- ------------- ----------- ------------ ------- -----------
msUnixTStoDATETIME2 1000000000 234062500 1638656250 31250 239735936
ITVF_UXTS_TO_DT2B 1000000000 294468750 2063000000 31250 301775403
ITVF_UXTS_TO_DT2 1000000000 301937500 2114750000 62500 309312956
Given the performance improvements gained by removing one REVERSE instance, I think we got some opportunities for improvement and more importantly, spread some bit-bashing knowledge 😉
May 26, 2023 at 3:38 am
@Eirikur,
I keep looking at your timing results. You say the following...
Given the performance improvements gained by removing one REVERSE instance, I think we got some opportunities for improvement and more importantly, spread some bit-bashing knowledge
... and yet the msUnixTStoDATETIME2 (I'm assuming it's my original function) beats the other two functions in every case but one and, considering the other results, may have been an anomaly.
What am I missing here?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply