November 10, 2018 at 9:41 pm
frederico_fonseca - Saturday, November 10, 2018 2:06 PMsimilar results for meAs the results I had given before only related to the date part I've also run your code with just the date bit.
and also without the statistics time as they can mess up function call
Thanks for running the tests.
Shifting gears a bit, STATISTICS TIME or I/O will certainly have a bad effect on things that use Scalar or mTVF functions. They won't have such an effect on built in functions because they're a totally different mechanism.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2018 at 9:44 pm
irehman - Saturday, November 10, 2018 1:32 PMJeff Moden - Saturday, November 10, 2018 1:24 PMirehman - Saturday, November 10, 2018 12:49 PMJeff Moden - Saturday, November 10, 2018 9:23 AMThis should do it for you
--===== Create a sample using variables.
DECLARE @OrderDate NUMERIC(18,0) = 118063
,@OrderTime FLOAT = 11456
;
--===== This solves for the two separate columns and a combination of the two columns.
-- Replace the @OrderDate and @OrderTime variables with column names if you want to play this against a table.
SELECT OrderDate = CONVERT(DATE,DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0)))
,OrderTime = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0))))
,OrderDateTime = DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0))
+ DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0)))
;so Jeff, I just replaced
@OrderDate NUMERIC(18,0) with @OrderDate NUMERIC(18,0) FROM Order?
@OrderTime FLOAT with @OrderTime FLOAT FROM Order?
Sorry, I'm new to SQL.No. You have to use the formulas as given. Like this...
SELECT OrderDate = CONVERT(DATE
,DATEADD(dy,OrderDate%1000-1 --Parse and convert the days of the year to add to the...
,DATEADD(yy,OrderDate/1000,0))) --Parsed and converted year. "0" is date of 1900-01-01.
,OrderTime = CONVERT(TIME(0)
,DATEADD(hh,CONVERT(INT,OrderTime)/10000%100 --Parse and convert the hour portion
,DATEADD(mi,CONVERT(INT,OrderTime)/100%100 --Parse and convert the minutes portion
,DATEADD(ss,CONVERT(INT,OrderTime)%100,0)))) --Parse and convert the seconds portion
FROM dbo.Order
;Notice that no variables are involved this time... just column names from the table and new column names (they're called "column Aliases" when used to create "derived" columns like this) to the left of the "=" sign (or after an "AS" if you like things the other way better)
You should also always use the 2 part (SchemaName.TableName) naming convention for objects (tables, functions, stored procedures, views, etc) in SQL Server.
In the above, I also "wrapped" the formulas around at "logical breaks" to make it easier to read instead of having to scroll to the right to read the whole code. It also makes it easier to comment the code so that new people can figure out what's going on. 😀
WORKED .... WORKED .... WORKED.... Thanks
Cool. Glad you got it up and running. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2018 at 3:27 am
Couldn't resist comparing the methods to the DATE/TIME-FROMPARTS functions
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE BIGINT = 2000000;
DECLARE @TIME_RANGE INT = 86400;
DECLARE @FIRST_TIME TIME = CONVERT(TIME,'00:00:00',0);
--/* UNCOMMENT THIS LINE TO SKIP THE TEST SET GENERATION
IF OBJECT_ID(N'dbo.TBL_TEST_JD_DATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_JD_DATE;
CREATE TABLE dbo.TBL_TEST_JD_DATE
(
TJD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_JD_DATE_TDA_ID PRIMARY KEY CLUSTERED (TJD_ID ASC)
,JD_DATE NUMERIC(18,0) NOT NULL
,JD_TIME FLOAT NOT NULL
);
;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)
INSERT INTO dbo.TBL_TEST_JD_DATE WITH (TABLOCKX) (JD_DATE,JD_TIME)
SELECT
((ABS(CHECKSUM(NEWID())) % 200) * 1000) + (ABS(CHECKSUM(NEWID())) % 366)
,CONVERT(FLOAT,REPLACE(LEFT(DATEADD(SECOND,ABS(CHECKSUM(NEWID())) % @TIME_RANGE,@FIRST_TIME),8),CHAR(58),''),0)
FROM NUMS NM;
-- */
DECLARE @NUMERIC_BUCKET NUMERIC(18,0) = 0;
DECLARE @FLOAT_BUCKET FLOAT = 0;
DECLARE @DATETIME_BUCKET DATETIME = 0;
DECLARE @DATE_BUCKET DATE = CONVERT(DATE,'20180101',112);
DECLARE @TIME_BUCKET TIME = CONVERT(TIME,'00:00:00',0);
DECLARE @timer TABLE (T_TXT VARCHAR(50) NULL, T_TD DATETIME2(7) NULL DEFAULT (SYSDATETIME()));
INSERT INTO @timer (T_TXT) VALUES ('DRY RUN NUMERIC');
SELECT
@NUMERIC_BUCKET = JD.JD_DATE
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DRY RUN NUMERIC');
INSERT INTO @timer (T_TXT) VALUES ('CONVERT DATE');
SELECT
@DATE_BUCKET = dateadd(day, JD.JD_DATE % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, JD.JD_DATE) / 1000, 0)) + '1231', 112))
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('CONVERT DATE');
INSERT INTO @timer (T_TXT) VALUES ('DATEFROMPARTS DATE');
SELECT
@DATE_BUCKET = DATEADD(DAY,(JD.JD_DATE % 1000) - 1,DATEFROMPARTS(FLOOR(1900 + (JD.JD_DATE/1000)),1,1))
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DATEFROMPARTS DATE');
INSERT INTO @timer (T_TXT) VALUES ('DATEADD DATE');
SELECT
@DATE_BUCKET = CONVERT(DATE,DATEADD(dy,JD.JD_DATE%1000-1,DATEADD(yy,JD.JD_DATE/1000,0)))
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DATEADD DATE');
INSERT INTO @timer (T_TXT) VALUES ('DRY RUN FLOAT');
SELECT
@FLOAT_BUCKET = JD.JD_TIME
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DRY RUN FLOAT');
INSERT INTO @timer (T_TXT) VALUES ('CONVERT TIME');
SELECT
@TIME_BUCKET = convert(time, stuff(stuff(right('000000' + convert(varchar(6), JD.JD_TIME), 6), 5, 0, ':'), 3, 0, ':'))
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('CONVERT TIME');
INSERT INTO @timer (T_TXT) VALUES ('DATEADD TIME');
SELECT
@TIME_BUCKET = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,JD.JD_TIME)/10000%100,DATEADD(mi,CONVERT(INT,JD.JD_TIME)/100%100,DATEADD(ss,CONVERT(INT,JD.JD_TIME)%100,0))))
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DATEADD TIME');
INSERT INTO @timer (T_TXT) VALUES ('TIMEFROMPARTS');
SELECT
@TIME_BUCKET = TIMEFROMPARTS((CONVERT(INT,JD.JD_TIME,0) / 10000),((CONVERT(INT,JD.JD_TIME,0) / 100) % 100),((CONVERT(INT,JD.JD_TIME,0) % 100)),0,0)
FROM dbo.TBL_TEST_JD_DATE JD
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('TIMEFROMPARTS');
SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TD),MAX(T.T_TD)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;
Results on i5 laptop
T_TXT DURATION
------------------- -----------
DRY RUN NUMERIC 278490
DRY RUN FLOAT 353845
TIMEFROMPARTS 516492
DATEADD TIME 752876
DATEADD DATE 1254353
DATEFROMPARTS DATE 1333188
CONVERT DATE 1486955
CONVERT TIME 3199977
November 11, 2018 at 8:50 am
Jeff Moden - Saturday, November 10, 2018 9:41 PMfrederico_fonseca - Saturday, November 10, 2018 2:06 PMsimilar results for meAs the results I had given before only related to the date part I've also run your code with just the date bit.
and also without the statistics time as they can mess up function callThanks for running the tests.
Shifting gears a bit, STATISTICS TIME or I/O will certainly have a bad effect on things that use Scalar or mTVF functions. They won't have such an effect on built in functions because they're a totally different mechanism.
I was thinking about your comment about built-in functions and Statistics Time and the claim that Statistics Time skews time comparisons where built-in functions are used. Remember, we're not talking about Scalar or mTVF functions here. We're talking about built-in functions like DATEADD. It all made me realize that I've not tested for such an impact in a very long time and, although you tested it, I had to know for myself. So I changed the code I wrote to do a simple start/end time. Here's the entire code... same as before but with the start/end time measurement instead of using either STATISTICS TIME or STATISTICS IO.
RAISERROR('
--=====================================================================================================================
-- PRESETS
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== If the test table already exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#DateTest','u') IS NOT NULL
DROP TABLE #DateTest
;
GO
RAISERROR('
--=====================================================================================================================
-- Create the table and populate it with random data on the fly.
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== Create a million row test table according to the OP's original column specifications.
-- The generated dates and times in RandDT column are included for sanity checks.
-- The dates and times vary from 01 Jan 1900 up to and not including 01 Jan 2100.
-- This code takes about 2.5 seconds on my i5 powered laptop using SQL Server 2008 Developers Edition.
-- For more information on how to rapidly generate random test data, please see the following articles.
-- http://www.sqlservercentral.com/articles/Data+Generation/87901/
-- http://www.sqlservercentral.com/articles/Test+Data/88964/
DECLARE @StartDate DATETIME = GETDATE()
;
WITH cteDT AS
(
SELECT TOP 1000000
RandDT = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,0)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT RandDT
,OrderDate = CONVERT(NUMERIC(18,0),DATEDIFF(yy,0,RandDT)*1000 + DATEPART(dy,RandDT))
,OrderTime = CONVERT(FLOAT,DATEPART(hh,RandDT)*10000+DATEPART(mi,RandDT)*100+DATEPART(ss,RandDT))
INTO #DateTest
FROM cteDT
;
--===== Display the duration.
PRINT 'Duration: '+CONVERT(CHAR(12),GETDATE()-@StartDate,114);
;
GO
/*
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
1. In the following tests, we'll declare some "dump" variables for use during each test.
We don't want saving to disk or displaying to the screen to obfuscate the performance differences in the code so
we'll dump all results in variables, which is memory and that's very fast.
2. In each test...
2.1 Proccache is cleared.
2.2 Clean buffers are dropped.
2.3 Each test is executed 5 times.
2.3.1 The first execution inherently includes "Physical Read and Read Ahead" time.
2.3.2 The other four executions inherently doesn't include "Physical Read or Read Ahead" because the data is
already in memory.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
*/
GO
RAISERROR('
--=====================================================================================================================
-- Test #1: Integer Math
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== Clear cache and drop clean buffers. The time to do this is NOT included in the output.
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS
;
--===== Print a run separation line for readability
PRINT REPLICATE('-',119)
;
GO
--===== Create the "Dump" and Timer variables. The time to do this is not included in the output.
DECLARE @OrderDate DATE
,@OrderTime TIME
,@StartDate DATETIME = GETDATE()
;
--===== Run the test
SELECT @OrderDate = CONVERT(DATE
,DATEADD(dy,OrderDate%1000-1 --Parse and convert the days of the year to add to the...
,DATEADD(yy,OrderDate/1000,0))) --Parsed and converrted year. "0" is date of 1900-01-01.
,@OrderTime = CONVERT(TIME(0)
,DATEADD(hh,CONVERT(INT,OrderTime)/10000%100 --Parse and convert the hour portion
,DATEADD(mi,CONVERT(INT,OrderTime)/100%100 --Parse and convert the minutes portion
,DATEADD(ss,CONVERT(INT,OrderTime)%100,0)))) --Parse and convert the seconds portion
FROM #DateTest
;
--===== Display the duration.
PRINT 'Duration: '+CONVERT(CHAR(12),GETDATE()-@StartDate,114);
;
--===== Print a run separation line for readability
PRINT REPLICATE('-',119)
;
--===== Execute the test a total of 5 times
GO 5
... and here are the run results ... there's virtually no difference between the direct method of using start/end time and using STATISTICS TIME here.
--=====================================================================================================================
-- PRESETS
--=====================================================================================================================
The only bad part about it is that it seems to obfuscate the bit of extra time that the first "cold cache" run sometimes takes.
Anyway, thanks for bring the bit of doubt to my mind... I'd been meaning to retest the effects of the use of STATISTICS measurements for code where neither Scalar nor mTVF functions were being used and this was a good opportunity.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2018 at 8:52 am
p.s. And sorry about the mess the forum software made with the code and the results. I posted it the same way I did in similar posts above but it decided to act up this time.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2018 at 8:28 am
For the time, I'd so a single DATEADD with only SECONDs being added, something like this:
DECLARE @order_time float
SET @order_time = 11456
SELECT CAST(DATEADD(SECOND, order_time_int/10000*3600 + order_time_int/100%100*60 +
order_time_int%100, 0) AS time(0))
FROM (
SELECT CAST(@order_time AS int) AS order_time_int
) AS alias1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply