January 7, 2016 at 8:27 am
SELECT @@version
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Whilst examining a chunk of code for tuning possibilities, I noticed that the number of rows it returned was varying from run to run - which is strange, because the query reads from a couple of #temporary tables which are populated up front and remain unchanged between runs.
The query allocates credits from a payment table to a table of debits on a first come first served basis including fractional debits and fractional credits.
Here's the code for creating the sample data temp tables:
;WITH
n1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1 a, n1 b),
iTally AS (SELECT TOP(26316) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM n2 a, n2 b, n1 c)
SELECT
Supplier = i.n,
DebitID = ROW_NUMBER() OVER(PARTITION BY i.n ORDER BY (SELECT NULL)),
x.Debit
INTO #Debits -- (657900 row(s) affected)
FROM iTally i
CROSS JOIN (VALUES
(1000),(200),(50),(600),(2500),(5),(1000),(100),(400),(200),(500),
(500),(200),(400),(100),(1000),(5),(2500),(600),(50),(200),(990),(10),(10),(10)
) x (Debit);
;WITH
n1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1 a, n1 b),
iTally AS (SELECT TOP(26316) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM n2 a, n2 b, n1 c)
SELECT
Supplier = i.n,
CreditID = ROW_NUMBER() OVER(PARTITION BY i.n ORDER BY (SELECT NULL)),
x.Credit
INTO #Credits -- (578952 row(s) affected)
FROM iTally i
CROSS JOIN (VALUES
(500),(200),(400),(100),(1000),(5),(2500),(600),(50),(200),(1000),
(1000),(200),(50),(600),(2500),(5),(1000),(100),(400),(200),(500)
) x (Credit);
Since it's all hard-coded there's no variation in the rowcounts but in any case I don't recreate these tables between runs.
The functional query is this:
WITH ProcessedDebits AS ( --657,900 rows
SELECT Supplier, DebitID, Debit, [from] = ([to] - Debit), [to]
FROM (SELECT *, [to] = SUM(Debit) OVER (PARTITION BY Supplier ORDER BY DebitID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Debits) d
),
ProcessedCredits AS ( -- 578,952 rows
SELECT Supplier, CreditID, Credit, [from] = ([to] - Credit), [to]
FROM (SELECT *, [to] = SUM(Credit) OVER (PARTITION BY Supplier ORDER BY CreditID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Credits) d
)
SELECT --rn = ROW_NUMBER() OVER(PARTITION BY dr.Supplier ORDER BY dr.DebitID, cr.CreditID),
Supplier = COALESCE(dr.Supplier, cr.supplier),
DebitID, Debit,
DebitBalance = CASE
WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to])
WHEN dr.[to] < cr.[to] THEN 0
ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY dr.Supplier ORDER BY dr.DebitID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END,
CreditID, Credit,
CreditBalance = CASE
WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to])
WHEN cr.[to] < dr.[to] THEN 0
ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY cr.Supplier ORDER BY cr.CreditID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
ON cr.Supplier = dr.Supplier
AND cr.[from] < dr.[to]
AND cr.[to] > dr.[from]
It takes about 11s to execute completely on this dev server and it returns 1,078,956 rows. Every time, as you would expect.
If you add clustered indexes to the two #temp tables
CREATE CLUSTERED INDEX cx_Stuff ON #Debits (Supplier, DebitID)
CREATE CLUSTERED INDEX cx_Stuff ON #Credits (Supplier, CreditID)
then each execution of the functional query returns a rowcount of anything between 1079013 and 1079056 and it can be different on every run.
If you drop the two clustered indexes on the temp tables, then the rowcount for each execution returns to 1,078,956. Every time.
It doesn't matter if the clustered indexes are unique or not.
With those clustered indexes in place, the query returns extra rows in addition to the expected 1,078,956. These spurious rows are always CreditID = 22, the last row per partition by Supplier - but only some of them (there are 26,316 suppliers).
I'm baffled now. Any suggestions?
Cheers, ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2016 at 9:16 am
There have been a few problems like this that seem tied to parallelism. Running with the clustered index and OPTION (MAXDOP 1) yields deterministic results again.
There was this early bug in 2012 https://connect.microsoft.com/SQLServer/feedback/details/728932/parallelism-bug-with-sum-over-and-range, and I remember seeing a couple other similar connect items, but don't have the links handy.
Looks like not all of the issues have been ironed out.
Cheers!
January 7, 2016 at 9:44 am
Jacob Wilkins (1/7/2016)
There have been a few problems like this that seem tied to parallelism. Running with the clustered index and OPTION (MAXDOP 1) yields deterministic results again.There was this early bug in 2012 https://connect.microsoft.com/SQLServer/feedback/details/728932/parallelism-bug-with-sum-over-and-range, and I remember seeing a couple other similar connect items, but don't have the links handy.
Looks like not all of the issues have been ironed out.
Cheers!
Hey Jacob, this looks to be it - and clears Aaron Bertrand's point "You're probably tempted to suggest ROWS instead of RANGE." It affects ROWS too.
Many thanks
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2016 at 9:57 am
Here you appear to be selecting TOP X rows without an explicit sort order. I'm assuming that ORDER BY (SELECT NULL) is essentially nullifying the sort order, which could potentially result in an unpredicable set of rows being selected.
iTally AS (SELECT TOP(26316) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2016 at 10:04 am
Eric M Russell (1/7/2016)
Here you appear to be selecting TOP X rows without an explicit sort order. I'm assuming that ORDER BY (SELECT NULL) is essentially nullifying the sort order, which could potentially result in an unpredicable set of rows being selected.
iTally AS (SELECT TOP(26316) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
That's just the code run once to populate the temporary tables. The code that yields variable results from run to run when allowed to run in parallel with a clustered index in place doesn't have any nondeterministic constructs like that.
Cheers!
January 7, 2016 at 10:05 am
Eric M Russell (1/7/2016)
Here you appear to be selecting TOP X rows without an explicit sort order. I'm assuming that ORDER BY (SELECT NULL) is essentially nullifying the sort order, which could potentially result in an unpredicable set of rows being selected.
iTally AS (SELECT TOP(26316) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
This is commonly-used, tried and tested syntax for inline tally tables. I don't care which rows are returned or in what order, I want 26316 of them as quickly as possible. The FROM list is a fast row generator.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply