October 27, 2016 at 8:54 am
Here's the code I have, and perhaps someone can suggest a better way that will run in less time. This code takes 2 minutes and 19 seconds to run on my work laptop, which is a 64-bit Windows 7 Enterprise machine with 32 GB of ram and a quad-core Intel Core i7 @ 2.90 GHz.
DECLARE @START_DT AS datetime = '2015-01-01 00:00:00.000';
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
NUMBERS AS (
SELECT ROW_NUMBER() OVER(ORDER BY E1.N) AS N
FROM E1 AS E1, E1 AS E2, E1 AS E3, E1 AS E4, E1 AS E5, E1 AS E6, E1 AS E7, E1 AS E8
),
TALLY AS (
SELECT T.N, DATEADD(MINUTE, T.N * 15, @START_DT) AS DATE_TIME, CAST(T.N % 4 AS char(1)) AS QTR,
DATEADD(year, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_YEAR,
DATEADD(quarter, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_QUARTER,
DATEADD(month, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_MONTH,
DATEADD(week, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_WEEK
FROM NUMBERS AS T
WHERE T.N <= 2165184
)
--INSERT INTO DIM_Hour_Quarter (Hour_Quarter_ID, Hour_ID, Hour_Quarter_Start_Time, Hour_Quarter_End_Time, Last_Year_Hour_Quarter_ID, Last_Quarter_Hour_Quarter_ID,
--Last_Month_Hour_Quarter_ID, Last_Week_Hour_Quarter_ID)
SELECT
CAST(FORMAT(T.DATE_TIME, 'yyMMddHH') + T.QTR AS int) AS Hour_Quarter_ID,
CAST(FORMAT(T.DATE_TIME, 'yyMMddHH') AS int) AS Hour_ID,
T.DATE_TIME AS Hour_Quarter_Start_Time,
DATEADD(minute, 15, T.DATE_TIME) AS Hour_Quarter_End_Time,
CAST(FORMAT(T.LAST_YEAR, 'yyMMddHH') + T.QTR AS int) AS Last_Year_Hour_Quarter_ID,
CAST(FORMAT(T.LAST_QUARTER, 'yyMMddHH') + T.QTR AS int) AS Last_Quarter_Hour_Quarter_ID,
CAST(FORMAT(T.LAST_MONTH, 'yyMMddHH') + T.QTR AS int) AS Last_Month_Hour_Quarter_ID,
CAST(FORMAT(T.LAST_WEEK, 'yyMMddHH') + T.QTR AS int) AS Last_Week_Hour_Quarter_ID
FROM TALLY AS T
ORDER BY T.DATE_TIME;
The execution plan has a large number of nested loops, and I know why, but I just don't recall any of the other methods for getting a good 2 million plus records to put a ROW_NUMBER on at the moment... I'm doubtful that a recursive CTE would be faster, unless there's a better way to generate the rows. Any/all guidance appreciated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 27, 2016 at 9:15 am
Here is an article around that.
In the article, the numbers table is created and then worked on after.
October 27, 2016 at 9:21 am
One thing I've been told here, repeatedly(cause i keep using it), is that FORMAT (T-SQL) is awfully slow, which I agree it is; I'm just lazy.
On a silly dev PC (yes PC, it has a 3.87Ghz Dual Core AMD, and 16Gb of RAM, with SQL Server 2016 Developer installed), your query was running for over 5 minutes (I'll edit in an actual run time later). Using the below cut it down to 2 minutes 3 seconds.
USE AdventureWorks2012;
GO
DECLARE @START_DT AS datetime = '2015-01-01 00:00:00.000';
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
NUMBERS AS (
SELECT ROW_NUMBER() OVER(ORDER BY E1.N) AS N
FROM E1 AS E1, E1 AS E2, E1 AS E3, E1 AS E4, E1 AS E5, E1 AS E6, E1 AS E7, E1 AS E8
),
TALLY AS (
SELECT T.N, DATEADD(MINUTE, T.N * 15, @START_DT) AS DATE_TIME, CAST(T.N % 4 AS char(1)) AS QTR,
DATEADD(year, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_YEAR,
DATEADD(quarter, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_QUARTER,
DATEADD(month, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_MONTH,
DATEADD(week, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_WEEK
FROM NUMBERS AS T
WHERE T.N <= 2165184
)
SELECT
--CAST(FORMAT(T.DATE_TIME, 'yyMMddHH') + T.QTR AS int) AS Hour_Quarter_ID,
RIGHT(CAST(DATEPART(YEAR, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.DATE_TIME) AS VARCHAR(4)),2) + T.QTR AS Hour_Quarter_ID,
--CAST(FORMAT(T.DATE_TIME, 'yyMMddHH') AS int) AS Hour_ID,
RIGHT(CAST(DATEPART(YEAR, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.DATE_TIME) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.DATE_TIME) AS VARCHAR(4)),2) AS Hour_ID,
T.DATE_TIME AS Hour_Quarter_Start_Time,
DATEADD(minute, 15, T.DATE_TIME) AS Hour_Quarter_End_Time,
--CAST(FORMAT(T.LAST_YEAR, 'yyMMddHH') + T.QTR AS int) AS Last_Year_Hour_Quarter_ID,
RIGHT(CAST(DATEPART(YEAR, T.LAST_YEAR) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.LAST_YEAR) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.LAST_YEAR) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.LAST_YEAR) AS VARCHAR(4)),2) + T.QTR AS Last_Year_Hour_Quarter_ID,
--CAST(FORMAT(T.LAST_QUARTER, 'yyMMddHH') + T.QTR AS int) AS Last_Quarter_Hour_Quarter_ID,
RIGHT(CAST(DATEPART(YEAR, T.LAST_QUARTER) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.LAST_QUARTER) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.LAST_QUARTER) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.LAST_QUARTER) AS VARCHAR(4)),2) + T.QTR AS Last_Quarter_Hour_Quarter_ID,
--CAST(FORMAT(T.LAST_MONTH, 'yyMMddHH') + T.QTR AS int) AS Last_Month_Hour_Quarter_ID,
RIGHT(CAST(DATEPART(YEAR, T.LAST_MONTH) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.LAST_MONTH) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.LAST_MONTH) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.LAST_MONTH) AS VARCHAR(4)),2) + T.QTR AS Last_Month_Hour_Quarter_ID,
--CAST(FORMAT(T.LAST_WEEK, 'yyMMddHH') + T.QTR AS int) AS Last_Week_Hour_Quarter_ID
RIGHT(CAST(DATEPART(YEAR, T.LAST_WEEK) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(Month, T.LAST_WEEK) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(DAY, T.LAST_WEEK) AS VARCHAR(4)),2) +
RIGHT('0' + CAST(DATEPART(HOUR, T.LAST_WEEK) AS VARCHAR(4)),2) + T.QTR AS Last_Week_Hour_Quarter_ID
FROM TALLY AS T
ORDER BY T.DATE_TIME;
Note, I did have Live Query plan running on both of these, and the longest thing to do was the Sorting. On your query, the query was still running a lot of the query, as it sorted it, where as the above it finished everything very quickly, and just took the time to sort it.
EDIT: OK, so, it FINALLY finished running of the silly Dev PC.
Both times, as I said, are with Live Query Stats open, however, the SQL you provided took 12 minutes 49 seconds, compared to 2 minutes 3 seconds. That really does go to show how much slower FORMAT (T-SQL) can be.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2016 at 9:43 am
I agree that FORMAT is terrible for performance, but since the desired end result is INT for all these ID columns, why not just change them all to integer math instead of still doing string manipulations:
...
SELECT
(YEAR(T.DATE_TIME)%100) * 10000000 + MONTH(T.DATE_TIME) * 100000 + DAY(T.DATE_TIME) * 1000 + DATEPART(HOUR,T.DATE_TIME) * 10 + T.QTR AS Hour_Quarter_ID,
(YEAR(T.DATE_TIME)%100) * 1000000 + MONTH(T.DATE_TIME) * 10000 + DAY(T.DATE_TIME) * 100 + DATEPART(HOUR,T.DATE_TIME) AS Hour_ID,
T.DATE_TIME AS Hour_Quarter_Start_Time,
DATEADD(minute, 15, T.DATE_TIME) AS Hour_Quarter_End_Time,
(YEAR(T.LAST_YEAR)%100) * 10000000 + MONTH(T.LAST_YEAR) * 100000 + DAY(T.LAST_YEAR) * 1000 + DATEPART(HOUR,T.LAST_YEAR) * 10 + T.QTR AS Last_Year_Hour_Quarter_ID,
(YEAR(T.LAST_QUARTER)%100) * 10000000 + MONTH(T.LAST_QUARTER) * 100000 + DAY(T.LAST_QUARTER) * 1000 + DATEPART(HOUR,T.LAST_QUARTER) * 10 + T.QTR AS Last_Quarter_Hour_Quarter_ID,
(YEAR(T.LAST_MONTH)%100) * 10000000 + MONTH(T.LAST_MONTH) * 100000 + DAY(T.LAST_MONTH) * 1000 + DATEPART(HOUR,T.LAST_MONTH) * 10 + T.QTR AS Last_Month_Hour_Quarter_ID,
(YEAR(T.LAST_WEEK)%100) * 10000000 + MONTH(T.LAST_WEEK) * 100000 + DAY(T.LAST_WEEK) * 1000 + DATEPART(HOUR,T.LAST_WEEK) * 10 + T.QTR AS Last_Week_Hour_Quarter_ID
INTO #t1
FROM TALLY AS T
ORDER BY T.N;
ran in 10 seconds on my laptop, that has a i7-4600U CPU and 16 GB memory
October 27, 2016 at 9:45 am
This runs several times faster. I just can't understand why the FORMAT function is so slow.
The TOP also prevents generating all the row numbers which gives an additional boost.
DECLARE @Hour_Quarter_ID int,
@Hour_ID int,
@Hour_Quarter_Start_Time datetime,
@Hour_Quarter_End_Time datetime,
@Last_Year_Hour_Quarter_ID int,
@Last_Quarter_Hour_Quarter_ID int,
@Last_Month_Hour_Quarter_ID int,
@Last_Week_Hour_Quarter_ID int,
@START_DT AS datetime = '2015-01-01 00:00:00.000';
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
NUMBERS AS (
SELECT TOP( 2165184) ROW_NUMBER() OVER(ORDER BY E1.N) AS N
FROM E1 AS E1, E1 AS E2, E1 AS E3, E1 AS E4, E1 AS E5, E1 AS E6, E1 AS E7, E1 AS E8
),
TALLY AS (
SELECT T.N, DATEADD(MINUTE, T.N * 15, @START_DT) AS DATE_TIME, CAST(T.N % 4 AS char(1)) AS QTR,
DATEADD(year, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_YEAR,
DATEADD(quarter, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_QUARTER,
DATEADD(month, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_MONTH,
DATEADD(week, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_WEEK
FROM NUMBERS AS T
)
--INSERT INTO DIM_Hour_Quarter (Hour_Quarter_ID, Hour_ID, Hour_Quarter_Start_Time, Hour_Quarter_End_Time, Last_Year_Hour_Quarter_ID, Last_Quarter_Hour_Quarter_ID,
--Last_Month_Hour_Quarter_ID, Last_Week_Hour_Quarter_ID)
SELECT
Hour_Quarter_ID = CAST(CONVERT( char(6), T.DATE_TIME, 12) + CONVERT( char(2), T.DATE_TIME, 14) + T.QTR AS int),
Hour_ID = CAST(CONVERT( char(6), T.DATE_TIME, 12) + CONVERT( char(2), T.DATE_TIME, 14) AS int),
Hour_Quarter_Start_Time = T.DATE_TIME,
Hour_Quarter_End_Time = DATEADD(minute, 15, T.DATE_TIME),
Last_Year_Hour_Quarter_ID = CAST(CONVERT( char(6), T.LAST_YEAR, 12) + CONVERT( char(2), T.DATE_TIME, 14) + T.QTR AS int),
Last_Quarter_Hour_Quarter_ID = CAST(CONVERT( char(6), T.LAST_QUARTER, 12) + CONVERT( char(2), T.DATE_TIME, 14) + T.QTR AS int),
Last_Month_Hour_Quarter_ID = CAST(CONVERT( char(6), T.LAST_MONTH, 12) + CONVERT( char(2), T.DATE_TIME, 14) + T.QTR AS int),
Last_Week_Hour_Quarter_ID = CAST(CONVERT( char(6), T.LAST_WEEK, 12) + CONVERT( char(2), T.DATE_TIME, 14) + T.QTR AS int)
FROM TALLY AS T
ORDER BY T.DATE_TIME;
October 27, 2016 at 9:47 am
Luis Cazares (10/27/2016)
This runs several times faster. I just can't understand why the FORMAT function is so slow.
I really wish it wasn't. It's a lazy man's dream. :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2016 at 9:52 am
Chris Harshman (10/27/2016)
I agree that FORMAT is terrible for performance, but since the desired end result is INT for all these ID columns, why not just change them all to integer math instead of still doing string manipulations:
Because it's almost the end of the day, and I'm on holiday for 2 weeks soon. I can't be bothered to do maths in my head. ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2016 at 9:53 am
Thom A (10/27/2016)
Luis Cazares (10/27/2016)
This runs several times faster. I just can't understand why the FORMAT function is so slow.I really wish it wasn't. It's a lazy man's dream. :hehe:
I know. I wonder why MS hasn't fixed yet. (or if it's possible)
October 27, 2016 at 9:58 am
yes, that's the one thing I still miss from my Oracle days, was how easy TO_CHAR and TO_DATE worked for date datatype conversions
October 27, 2016 at 9:59 am
Actually, the problem is that you're converting int to char back to int. If you want integers keep them as integers.
DECLARE @START_DT AS datetime = '2015-01-01 00:00:00.000';
WITH E1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
NUMBERS AS (
SELECT ROW_NUMBER() OVER(ORDER BY E1.N) AS N
FROM E1 AS E1, E1 AS E2, E1 AS E3, E1 AS E4, E1 AS E5, E1 AS E6, E1 AS E7, E1 AS E8
),
TALLY AS (
SELECT T.N, DATEADD(MINUTE, T.N * 15, @START_DT) AS DATE_TIME, T.N % 4 AS QTR,
DATEADD(year, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_YEAR,
DATEADD(quarter, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_QUARTER,
DATEADD(month, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_MONTH,
DATEADD(week, -1, DATEADD(MINUTE, T.N * 15, @START_DT)) AS LAST_WEEK
FROM NUMBERS AS T
WHERE T.N <= 2165184
)
SELECT
YEAR(T.DATE_TIME) % 100 * 10000000 + MONTH(T.DATE_TIME) * 100000 + DAY(T.DATE_TIME) * 1000 + DATEPART(HOUR, T.DATE_TIME) * 10 + T.QTR AS Hour_Quarter_ID,
YEAR(T.DATE_TIME) % 100 * 1000000 + MONTH(T.DATE_TIME) * 10000 + DAY(T.DATE_TIME) * 100 + DATEPART(HOUR, T.DATE_TIME) * 1 AS Hour_ID,
T.DATE_TIME AS Hour_Quarter_Start_Time,
DATEADD(minute, 15, T.DATE_TIME) AS Hour_Quarter_End_Time,
YEAR(T.LAST_YEAR) % 100 * 10000000 + MONTH(T.LAST_YEAR) * 100000 + DAY(T.LAST_YEAR) * 1000 + DATEPART(HOUR, T.LAST_YEAR) * 10 + T.QTR AS Last_Year_Hour_Quarter_ID,
YEAR(T.LAST_QUARTER) % 100 * 10000000 + MONTH(T.LAST_QUARTER) * 100000 + DAY(T.LAST_QUARTER) * 1000 + DATEPART(HOUR, T.LAST_QUARTER) * 10 + T.QTR AS Last_Quarter_Hour_Quarter_ID,
YEAR(T.LAST_MONTH) % 100 * 10000000 + MONTH(T.LAST_MONTH) * 100000 + DAY(T.LAST_MONTH) * 1000 + DATEPART(HOUR, T.LAST_MONTH) * 10 + T.QTR AS LAST_MONTH_Hour_Quarter_ID,
YEAR(T.LAST_WEEK) % 100 * 10000000 + MONTH(T.LAST_WEEK) * 100000 + DAY(T.LAST_WEEK) * 1000 + DATEPART(HOUR, T.LAST_WEEK) * 10 + T.QTR AS LAST_WEEK_Hour_Quarter_ID
INTO #JDA
FROM TALLY AS T
ORDER BY T.DATE_TIME;
Here are the results:
-- Original
SQL Server Execution Times:
CPU time = 114422 ms, elapsed time = 121078 ms.
-- Thom
SQL Server Execution Times:
CPU time = 14172 ms, elapsed time = 14966 ms.
-- Drew
SQL Server Execution Times:
CPU time = 8563 ms, elapsed time = 8738 ms.
I also have to wonder why you are doing all of these calculations for previous periods for a dimension. The dimension will already calculate all of the previous values for each level of the hierarchy when you create your cube, so it seems that you are putting in a lot of extra work for little or no gain.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 27, 2016 at 10:58 am
Luis Cazares (10/27/2016)
I just can't understand why the FORMAT function is so slow.
Rumor has it that it's one of "those" RegEx thingies from the .NET environment and is actually an SQLCLR function to make the call.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2016 at 11:31 am
Yes, Jeff, I saw that bit about it being based on CLR later in the day (although not from your post), but figured if they used CLR then they "MUST" have made that decision because, perhaps not unlike your 2K8 CSV Splitter function, it's the only thing "faster"... Boy did that turn out to be a bad assumption. Anyway, there's a ton of stuff I've learned over the last couple of days that makes things a LOT more clear for what I need to do. Here's what I found, and why I'm now tossing this query into the trash heap:
1.) The problem I was running into was that the existing query to populate the dimension was making mistakes on start and end times for every single leap day, where it was tossing in a date portion of 2/28, even while correctly creating the values for Hour_Quarter_ID as well as various other elements.
2.) That problem was exacerbated by a query that was pretty darned ugly, so I figured it was easier to start from scratch... mostly...
3.) Once I realized the primary reason for the query only messing up on 2/29, and that it was how the date was being constructed, I was then "sure" that I was on the right track by starting mostly fresh. That's the point at which I constructed the "new" query that used FORMAT ... kinda because I could, and it seemed "easier"... how little I knew. I suppose it should have been a clue that the original query ran faster than my "new" one... anyway...
4.) The date construction in the original query was messing up because it was constructing the date from a series of DATEADD functions, where the innermost one added a DAY value to date 0 (aka 1/1/1900). Bingo! This morning I had the insight that I could just switch the innermost and outermost DATEADDs and now the YEAR value one would be innermost, with the DAY value one outermost, and the MONTH one in the middle. This actually fixed the original query, and after a few other cleanup items and a fix to ensure a different problem with the HOUR dimension as source data didn't create havoc, I had a query that instead of running in about a minute on the hardware it has to run on, it now runs in about 45 seconds.
5.) As that "fixes" the issue, I'm probably good, but I'm going to look at the various other posts here that refer to maintaining integer math and their timed performance runs and apply as much of that as I can to the "fixed" query. Some of the improvements over FORMAT are positively STUNNING!
6.) One person posted about whether such additional elements as those referencing prior periods are necessary. In my case, they actually are, as there are no actual cubes being built on these dimensions yet, and it may be quite a while if this DIM is any indicator of what else I might run into. A reporting tool is using the dimension tables for dashboard reporting, and I know it's not creating cubes, ... at least not in the traditional sense anyway.
Many thanks to everyone that provided posts here... Once again, I've learned a ton from this one.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply