January 21, 2014 at 4:00 pm
gcresse (1/21/2014)
Fantastic! I have never used ROWS UNBOUNDED PRECEDING but I'm going to start reading up on that functionality. Very useful.Thanks, again!
Gina
Here's a link:-) http://technet.microsoft.com/en-us/library/ms189461.aspx
January 21, 2014 at 6:57 pm
Since the ROWS (window frame) facility added in SQL 2012 supports doing the running totals you need, many would suggest that you use it instead of a Quirky Update (QU), since performance isn't too bad - at least not when you compare it to other methods available in earlier versions of SQL - specifically because you don't need to rely on the "undocumented" (but generally consistent) behavior of a QU.
Here's my take. I've made the assumption that you might have multiple transactions within a month, hence the initial SumByMonth CTE.
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM @InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[Balance]=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM @InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
ORDER BY a.IntLotKey, [Year], [Period];
In reality, it is not much different than the approaches suggested prior.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 7:03 pm
Ooops! I didn't see the second page of posts! Guess I got too into the fun problem.
Here's how to use LAG to get the Beginning Balance (from my query):
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM @InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT LotKey, [Year], [Period]
,BeginningBalance=LAG(EndingBalance, 1, 0) OVER (PARTITION BY LotKey ORDER BY IntTranDate)
,EndingBalance
FROM
(
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[EndingBalance]=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,IntTranDate
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM @InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
) a
ORDER BY LotKey, [Year], [Period];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 7:04 pm
mickyT (1/21/2014)
gcresse (1/21/2014)
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.You can change the select clause to the following
...
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) BalanceBefore,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING ) BalanceAfter
...
I am quite curious how this "double lookback" to all prior rows performs. I know if you use LAG multiple times it doesn't perform worth a hoot.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 7:11 pm
dwain.c (1/21/2014)
mickyT (1/21/2014)
gcresse (1/21/2014)
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.You can change the select clause to the following
...
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) BalanceBefore,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING ) BalanceAfter
...
I am quite curious how this "double lookback" to all prior rows performs. I know if you use LAG multiple times it doesn't perform worth a hoot.
If I get some time I'll look at putting together a large test set and see what happens
January 21, 2014 at 7:30 pm
mickyT (1/21/2014)
dwain.c (1/21/2014)
mickyT (1/21/2014)
gcresse (1/21/2014)
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.You can change the select clause to the following
...
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) BalanceBefore,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING ) BalanceAfter
...
I am quite curious how this "double lookback" to all prior rows performs. I know if you use LAG multiple times it doesn't perform worth a hoot.
If I get some time I'll look at putting together a large test set and see what happens
While you're at it, you might double check that ROWS UNBOUNDED PRECEDING and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW return different things. I think that they return the same thing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 8:08 pm
OK I set up the following as a test
CREATE TABLE #InventoryTran (
IntLotKey INT NOT NULL,
IntTranDate DATE,
IntTranQty INT
)
INSERT INTO #InventoryTran
SELECT TOP 100000
IntLotKey = FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 30000)
,IntTranDate =
CAST(2000 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14) AS VARCHAR(4)) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 12) + 1 AS VARCHAR(2)),2) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 27) + 1 AS VARCHAR(2)),2)
,IntTranQty = (1 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14)) * 10
FROM TallyPlus
DECLARE @scrapInt INT
WITH cteSmallTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM #InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT @scrapInt = f.IntLotKey,
@scrapInt = YEAR(f.cDate) , @scrapInt = MONTH(f.cDate) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
FROM FillGaps f
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM #InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i
ORDER BY f.IntLotKey, f.cDate;
WITH cteSmallTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM #InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT @scrapInt = f.IntLotKey,
@scrapInt = YEAR(f.cDate) , @scrapInt = MONTH(f.cDate) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
FROM FillGaps f
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM #InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i
ORDER BY f.IntLotKey, f.cDate;
The Execution plans differ in that there is an extra Segment, Sequence Project, Segment, Window Spool and Stream Aggregate set in it.
The Statistics were as follows
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#InventoryTran_00000000000B'. Scan count 10, logical reads 496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1993038, logical reads 10219315, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 57175 ms, elapsed time = 9062 ms.
Table '#InventoryTran_00000000000B'. Scan count 10, logical reads 496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1993038, logical reads 10219315, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 48282 ms, elapsed time = 7850 ms.
I also think that ROWS UNBOUNDED PRECEDING and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW return the same and a quick test on them confirms that.
January 21, 2014 at 8:42 pm
MickyT - Using only your query that returns ending balance against mine that does the same, our two queries return a different number of rows Here's my modification of your test harness:
CREATE TABLE #InventoryTran (
IntLotKey INT NOT NULL,
IntTranDate DATE,
IntTranQty INT
);
WITH Tally (n) AS
(
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO #InventoryTran
SELECT TOP 100000
IntLotKey = FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 30000)
,IntTranDate =
CAST(2000 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14) AS VARCHAR(4)) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 12) + 1 AS VARCHAR(2)),2) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 27) + 1 AS VARCHAR(2)),2)
,IntTranQty = (1 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14)) * 10
FROM Tally;
DECLARE @scrapInt INT;
SET STATISTICS TIME ON;
WITH cteSmallTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM #InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT f.IntLotKey,
YEAR(f.cDate) , MONTH(f.cDate) ,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
FROM FillGaps f
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM #InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i;
SET STATISTICS TIME OFF;
SET STATISTICS TIME ON;
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM #InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
--SELECT @scrapInt=IntLotKey
-- ,@scrapInt=DATEPART(year, IntTranDate)
-- ,@scrapInt=DATEPART(month, IntTranDate)
-- ,@scrapInt=SUM(IntTranQty) OVER (
-- PARTITION BY IntLotKey ORDER BY IntTranDate
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[Balance]=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM #InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a;
SET STATISTICS TIME OFF;
GO
DROP TABLE #InventoryTran;
I expanded my in-line Tally table thinking that might be the issue but it was not. Mine seems to return more rows faster.
(100000 row(s) affected)
SQL Server parse and compile time:
CPU time = 314 ms, elapsed time = 314 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1993297 row(s) affected)
SQL Server Execution Times:
CPU time = 39295 ms, elapsed time = 19991 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 19 ms.
(2204396 row(s) affected)
SQL Server Execution Times:
CPU time = 12307 ms, elapsed time = 14033 ms.
Putting the results into your @scrapInt holder variable, the results look like this:
(100000 row(s) affected)
SQL Server parse and compile time:
CPU time = 310 ms, elapsed time = 310 ms.
SQL Server Execution Times:
CPU time = 38938 ms, elapsed time = 10914 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server parse and compile time:
CPU time = 20 ms, elapsed time = 20 ms.
SQL Server Execution Times:
CPU time = 11810 ms, elapsed time = 4075 ms.
I'm curious now as to which is right and about what is causing the difference in speed.
Edit: Now that I look a little closer, I think the difference in row counts is due to my in-line Tally generating 11x11+1 rows while yours generates 10x10. I'm thinking that perhaps neither is enough given the way you've set up the test data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 8:52 pm
dwain.c (1/21/2014)
mickyT (1/21/2014)
dwain.c (1/21/2014)
mickyT (1/21/2014)
gcresse (1/21/2014)
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.You can change the select clause to the following
...
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) BalanceBefore,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING ) BalanceAfter
...
I am quite curious how this "double lookback" to all prior rows performs. I know if you use LAG multiple times it doesn't perform worth a hoot.
If I get some time I'll look at putting together a large test set and see what happens
While you're at it, you might double check that ROWS UNBOUNDED PRECEDING and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW return different things. I think that they return the same thing.
And apologies for this. I see now you were using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, instead of what my brain was telling me I thought you were using.
[doh][/doh]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 8:57 pm
dwain.c (1/21/2014)
Ooops! I didn't see the second page of posts! Guess I got too into the fun problem.Here's how to use LAG to get the Beginning Balance (from my query):
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM @InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT LotKey, [Year], [Period]
,BeginningBalance=LAG(EndingBalance, 1, 0) OVER (PARTITION BY LotKey ORDER BY IntTranDate)
,EndingBalance
FROM
(
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[EndingBalance]=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,IntTranDate
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM @InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
) a
ORDER BY LotKey, [Year], [Period];
Mind you, I am just guessing but I think this will be a faster way to get both Beginning and Ending balances than the version using LAG that I posted above.
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM @InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT LotKey, [Year], [Period]
,BeginningBalance
,EndingBalance=IntTranQty + BeginningBalance
FROM
(
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[BeginningBalance]=ISNULL(SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
,IntTranDate
,IntTranQty
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM @InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
) a
ORDER BY LotKey, [Year], [Period];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 9:38 pm
So, I fixed the issue with the in-line Tally tables generating different row counts (they all now return 101 rows) and each of the following 5 queries returns the exact same row count.
CREATE TABLE #InventoryTran (
IntLotKey INT NOT NULL,
IntTranDate DATE,
IntTranQty INT
);
WITH Tally (n) AS
(
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO #InventoryTran
SELECT TOP 100000
IntLotKey = FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 30000)
,IntTranDate =
CAST(2000 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14) AS VARCHAR(4)) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 12) + 1 AS VARCHAR(2)),2) +
RIGHT('0' + CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 27) + 1 AS VARCHAR(2)),2)
,IntTranQty = (1 + FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 14)) * 10
FROM Tally;
DECLARE @scrapInt INT;
PRINT 'MickyT Ending Balance only';
SET STATISTICS TIME ON;
WITH cteSmallTally AS (
SELECT 0 N UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM #InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT @scrapInt = f.IntLotKey,
@scrapInt = YEAR(f.cDate) , @scrapInt = MONTH(f.cDate) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
--SELECT f.IntLotKey,
--YEAR(f.cDate) , MONTH(f.cDate) ,
--SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
FROM FillGaps f
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM #InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i;
SET STATISTICS TIME OFF;
PRINT 'MickyT Starting and Ending Balances';
SET STATISTICS TIME ON;
WITH cteSmallTally AS (
SELECT 0 N UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM #InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
--SELECT f.IntLotKey,
--YEAR(f.cDate) , MONTH(f.cDate) ,
--SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ,
--SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
SELECT @scrapInt = f.IntLotKey,
@scrapInt = YEAR(f.cDate) , @scrapInt = MONTH(f.cDate) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ,
@scrapInt = SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING )
FROM FillGaps f
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM #InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i;
--ORDER BY f.IntLotKey, f.cDate;
SET STATISTICS TIME OFF;
PRINT 'Dwain.C Ending Balance only';
SET STATISTICS TIME ON;
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM #InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT @scrapInt=IntLotKey
,@scrapInt=DATEPART(year, IntTranDate)
,@scrapInt=DATEPART(month, IntTranDate)
,@scrapInt=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
--SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
-- ,[Period]=DATEPART(month, IntTranDate)
-- ,[Balance]=SUM(IntTranQty) OVER (
-- PARTITION BY IntLotKey ORDER BY IntTranDate
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM #InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a;
SET STATISTICS TIME OFF;
PRINT 'Dwain.C Starting and Ending Balances using LAG to calculate Starting Balance';
SET STATISTICS TIME ON;
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM #InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
SELECT @scrapINT = LotKey, @scrapINT = [Year], @scrapINT = [Period]
,@scrapINT = LAG(EndingBalance, 1, 0) OVER (PARTITION BY LotKey ORDER BY IntTranDate)
,@scrapINT = EndingBalance
--SELECT LotKey, [Year], [Period]
-- ,BeginningBalance=LAG(EndingBalance, 1, 0) OVER (PARTITION BY LotKey ORDER BY IntTranDate)
-- ,EndingBalance
FROM
(
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[EndingBalance]=SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,IntTranDate
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM #InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
) a;
SET STATISTICS TIME OFF;
PRINT 'Dwain.C Starting and Ending Balances (without LAG)';
SET STATISTICS TIME ON;
WITH SumByMonth AS
(
SELECT IntLotKey, IntTranDate=d, IntTranQty=SUM(IntTranQty)
FROM #InventoryTran a
CROSS APPLY
(
SELECT d=DATEADD(month, DATEDIFF(month, 0, IntTranDate), 0)
) b
GROUP BY IntLotKey, d
)
--SELECT LotKey, [Year], [Period]
-- ,BeginningBalance
-- ,EndingBalance=IntTranQty + BeginningBalance
SELECT @scrapINT = LotKey, @scrapINT = [Year], @scrapINT = [Period]
,@scrapINT = BeginningBalance
,@scrapINT = IntTranQty + BeginningBalance
FROM
(
SELECT LotKey=IntLotKey, [Year]=DATEPART(year, IntTranDate)
,[Period]=DATEPART(month, IntTranDate)
,[BeginningBalance]=ISNULL(SUM(IntTranQty) OVER (
PARTITION BY IntLotKey ORDER BY IntTranDate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
,IntTranDate
,IntTranQty
FROM
(
SELECT a.IntLotKey, IntTranDate=d2
,IntTranQty=ISNULL(c.IntTranQty, 0)
FROM
(
SELECT IntLotKey, IntTranDate=MIN(IntTranDate)
FROM SumByMonth
GROUP BY IntLotKey
) a
CROSS APPLY
(
-- Create a calendar table on the fly to fill the gaps
SELECT d2=DATEADD(month, n, IntTranDate)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (
(
SELECT DATEDIFF(month, MIN(IntTranDate), MAX(IntTranDate))
FROM #InventoryTran b
WHERE a.IntLotKey = b.IntLotKey
))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- Make sure you've got enough Tally entries in the VALUES list
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
) a
) b
LEFT JOIN SumByMonth c ON a.IntLotKey = c.IntLotKey AND
c.IntTranDate = d2
) a
) a
ORDER BY LotKey, [Year], [Period];
SET STATISTICS TIME OFF;
GO
DROP TABLE #InventoryTran;
After shunting the results to @scrapINT, the results are as follows (eliminating the message about SQL Parse and Compile time for clarity):
(100000 row(s) affected)
MickyT Ending Balance only
SQL Server Execution Times:
CPU time = 39030 ms, elapsed time = 10929 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
MickyT Starting and Ending Balances
SQL Server Execution Times:
CPU time = 45365 ms, elapsed time = 13212 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
Dwain.C Ending Balance only
SQL Server Execution Times:
CPU time = 10998 ms, elapsed time = 3916 ms.
Dwain.C Starting and Ending Balances using LAG to calculate Starting Balance
SQL Server Execution Times:
CPU time = 17612 ms, elapsed time = 5522 ms.
Dwain.C Starting and Ending Balances (without LAG)
SQL Server Execution Times:
CPU time = 14882 ms, elapsed time = 4680 ms.
Some of the results I expected, like my version with Starting and Ending balances not using LAG was faster than using LAG. I kind of thought that my version with Starting and Ending balances (no LAG) might be faster than my version with just Ending Balance (thinking that fewer rows in the window frame might make it better) but that was not to be.
Please check me. Had to make lots of mods here but I think I did them all correctly.
Note that for this test data harness, neither of the in-line Tally tables is generating enough rows to cover the entire set of data. But I did make that comment in my first submission (probably still remains in my code).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2014 at 10:19 pm
I'll try and have another look at this tomorrow and thinking about it, the tally tables should have at least (14 * 12) 168 rows in it for this test set.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply