June 7, 2011 at 8:15 pm
tfeuz (6/7/2011)
Wayne,I had to make a fix to the code -- you did not constrain by the rule id when you create the temp table -- it caused duplicate values...
Note the addition of the clause "and @RuleId = rc.rb_ruleid;"
Good catch on this.
I ended up stopping the query after 10 minutes --
Wayne --- you have been more than genrous with your time and at 16 seconds per 1 million rows i think i am well within the range of "acceptable" -- do you still wish to continue? If so, I am happy to keep testing stuff....
Tony
I'd kinda like to see the query finish, and get the stats and execution plan. Plus, Kevin thinks it will do better without the CI on the table var... this ought to be a good test to check also.
I'm kinda confused by the 10 minutes & 16 secs/1 million rows part... they don't seem to line up. At that rate, it would be pushing close to 40 million rows in 10 minutes... how many rows are we talking about here again? And wasn't it working in 16 seconds before?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 8:29 pm
TheSQLGuru (6/7/2011)
I was giving my own presentation @ sqlsat77 while yours was in progress. 😀
Ah, so you were. I heard it went pretty well.
As long as they have fairly uniform distribution of values the table var should be ok I suppose. Still bet it will be more efficient without the PK though.
My money's on the optimal solution having no spools, probably 1-2 intermediary temp tables, no CTEs and likely a hash join or two. Lotta work to get from here to there though.
Right now, I have the TVF because there are two procedures that call it - one for just getting a single result set, and the other is this monster to get all of the unprocessed invoices. If I'm reading you correctly, you're suggesting to merge the TVF into the calling procedure - which would end up with duplicate code in multiple procedures. Am I reading you correctly?
You've mentioned a couple of times about getting rid of the CTEs. I'm curious as to the rational for this... they are effectively just a sub-query, and they are only being called once. The only way I can see this improving things is in combination with the above part of merging the code into its own procedure for the massive run. Then I can see something like this. Am I missing something here?
I admire you for taking this one on (and for sticking with it). Its unquestionably a paid gig IMHO.
Thanks. And it true. But, earlier in the thread, I badgered Tony to get the DDL/sample data posted so that we could help. Once he did so, I sorta felt obligated to stick around and see it through.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 9:03 pm
Tony:
Is RB_InvoicesToProcess 1:1 with Invoices?
Is RB_InvoicesToProcess_Moved 1:1 with Invoices?
Just to verify, you can have the same invoice in both of those tables?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2011 at 4:34 am
WayneS (6/7/2011)
tfeuz (6/7/2011)
Wayne,I had to make a fix to the code -- you did not constrain by the rule id when you create the temp table -- it caused duplicate values...
Note the addition of the clause "and @RuleId = rc.rb_ruleid;"
Good catch on this.
I ended up stopping the query after 10 minutes --
Wayne --- you have been more than genrous with your time and at 16 seconds per 1 million rows i think i am well within the range of "acceptable" -- do you still wish to continue? If so, I am happy to keep testing stuff....
Tony
I'd kinda like to see the query finish, and get the stats and execution plan. Plus, Kevin thinks it will do better without the CI on the table var... this ought to be a good test to check also.
I'm kinda confused by the 10 minutes & 16 secs/1 million rows part... they don't seem to line up. At that rate, it would be pushing close to 40 million rows in 10 minutes... how many rows are we talking about here again? And wasn't it working in 16 seconds before?
Wayne -- we did have previous versions running in 16 seconds -- this is the first one that has taken longer.
There are 80,000 Invoice rows and 1.2 million line item rows
June 8, 2011 at 4:36 am
WayneS (6/7/2011)
Tony:Is RB_InvoicesToProcess 1:1 with Invoices?
Is RB_InvoicesToProcess_Moved 1:1 with Invoices?
Just to verify, you can have the same invoice in both of those tables?
RB_InvoicesToProcess 1:1 with Invoices but you have to throw Rule Id in there as well. There will only be one entry for a given invoice for a given rule.
Same applies to RB_InvoicesToProcess_Moved
Now that beng said, my test data is only working on one rule so technically your statement
"RB_InvoicesToProcess 1:1 with Invoices?" is correct at the present time.
Tony
June 8, 2011 at 5:54 am
tfeuz (6/8/2011)
WayneS (6/7/2011)
tfeuz (6/7/2011)
Wayne,I had to make a fix to the code -- you did not constrain by the rule id when you create the temp table -- it caused duplicate values...
Note the addition of the clause "and @RuleId = rc.rb_ruleid;"
Good catch on this.
I ended up stopping the query after 10 minutes --
Wayne --- you have been more than genrous with your time and at 16 seconds per 1 million rows i think i am well within the range of "acceptable" -- do you still wish to continue? If so, I am happy to keep testing stuff....
Tony
I'd kinda like to see the query finish, and get the stats and execution plan. Plus, Kevin thinks it will do better without the CI on the table var... this ought to be a good test to check also.
I'm kinda confused by the 10 minutes & 16 secs/1 million rows part... they don't seem to line up. At that rate, it would be pushing close to 40 million rows in 10 minutes... how many rows are we talking about here again? And wasn't it working in 16 seconds before?
Wayne -- we did have previous versions running in 16 seconds -- this is the first one that has taken longer.
There are 80,000 Invoice rows and 1.2 million line item rows
Gotcha! So, the mTVF didn't work out then. 🙁 :crying:
Well, back to the drawing board then...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2011 at 10:59 am
Tony,
Starting over. The main (not single) procedure is totally rewritten to combine the TVF with it. If this works, we'll combine the TVF into the single procedure also.
Start off by dropping those six indexes created on the Invoice table. We'll re-evaluate what is needed there based upon the execution plan that this creates.
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
-- Determine which column to use
SELECT @Col2Use = CASE (CalcDateType)
WHEN 1 THEN 'ShipToDiscountDueDate'
WHEN 2 THEN 'ShipToNetDueDate'
WHEN 3 THEN 'TermsDiscountDueDate'
WHEN 4 THEN 'TermsNetDueDate'
ELSE 'InvoiceDate'
END
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
-- Create a temp table to hold all needed data.
-- Will probably need to modify the PK as we test this.
CREATE TABLE #Invoice (
InvoiceKey DECIMAL(18,0),
ShipToKey DECIMAL(18,0),
RulePeriodId DECIMAL(18,0),
MyDateCol DATETIME,
CategoryNumber NVARCHAR(50),
TermsDiscountAmount DECIMAL(18,6),
TermsPercent DECIMAL(18,6),
IsAssociated AS CASE RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END,
PRIMARY KEY CLUSTERED (InvoiceKey, CategoryNumber)
);
-- Build a string to get the data from the appropriate column
-- Are you sure that we can't do a UNION ALL here?
-- Why would you want to process an InvoiceKey / RulePeriodId twice?
SET @SQLCmd =
';WITH cte AS
(
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
)
INSERT INTO #Invoice
(InvoiceKey,
ShipToKey,
RulePeriodId,
MyDateCol,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT cte.InvoiceKey,
i.ShipToKey,
cte.RulePeriodId,
i.' + @Col2Use + ',
rc.CategoryNumber,
i.TermsDiscountAmount,
CASE WHEN i.TermsDiscountAmount = 0 OR @TermsRuleType = 0 THEN 0
ELSE i.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN it.TotalInvoiceAmount
ELSE it.InvoiceSubTotal
END
END
FROM cte
JOIN dbo.Invoice i -- 1:1
ON cte.InvoiceKey = i.InvoiceKey
JOIN dbo.InvoiceTotal it -- 1:1 per invoice
ON i.InvoiceKey = it.InvoiceKey
JOIN dbo.RB_RuleCategory rc -- 1:N per supplier/category
ON i.SupplierKey = rc.SupplierId;
';
-- Execute the string. Pass in the necessary parameters.
EXEC sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
WITH cte AS
(
-- Get data from the temp table. Combine with aggregate info from other tables.
SELECT i.InvoiceKey,
i.ShipToKey,
i.RulePeriodId,
i.MyDateCol,
i.CategoryNumber,
i.TermsDiscountAmount,
i.TermsPercent,
i.IsAssociated,
UnitsShipped = SUM(ili.UnitsShipped),
GrossAmount = SUM(ili.GrossAmount),
NetAmount = SUM(ili.NetAmount),
TaxUnits = COUNT(itt.InvoiceKey),
TaxAmount = SUM(itt.TaxAmount),
AddUnits = COUNT(itac.InvoiceKey),
AddAmount = SUM(CASE WHEN itac.[Type] = 'C' THEN ABS(itac.Amount) ELSE itac.amount END)
FROM #Invoice i
LEFT JOIN dbo.InvoiceLineItem ili
ON i.InvoiceKey = ili.InvoiceKey
AND ili.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalTax itt
ON i.InvoiceKey = itt.InvoiceKey
AND itt.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalAdditionalCosts itac
ON i.InvoiceKey = itac.InvoiceKey
AND itac.ProductRebateCategory = i.CategoryNumber
GROUP BY i.InvoiceKey, i.ShipToKey, i.RulePeriodId, i.MyDateCol,
i.CategoryNumber, i.TermsDiscountAmount, i.TermsPercent, i.IsAssociated
), cte2 AS
(
-- Add the RebateUnits and ChargeUnits columns
SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(GrossAmount,0) + ISNULL(TaxAmount,0) -- Gross
WHEN 2 THEN ISNULL(NetAmount,0) + ISNULL(TaxAmount,0) -- Net
WHEN 3 THEN ISNULL(UnitsShipped,0) + ISNULL(TaxUnits,0) -- Units
END,
ChargeUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(AddAmount,0) -- Gross
WHEN 2 THEN ISNULL(AddAmount,0) -- Net
WHEN 3 THEN ISNULL(AddUnits,0) -- Units
END
FROM cte
)
-- Insert everything into the RB_RuleInvoice table
INSERT INTO dbo.[RB_RuleInvoice]
([RB_RuleId],
[RB_RulePeriodId],
[InvoiceKey],
[IsAssociated],
[ForcastData],
[RebateUnits],
[GrossAmount],
[NetAmount],
[Units],
[DealerId],
[RebateAmount],
[RebateAmountPaid],
[RebateAmountActual],
[ReallocationAmount],
[ReallocationAmountPaid],
[ReallocationPaidFlag],
[PostPeriodAdjustAmount],
[TotalInvoiceNotPaid],
[TotalReallocation],
[PercentPayable])
SELECT @RuleId,
CASE WHEN t.RulePeriodId IS NULL OR t.RulePeriodId IN (-1,-3) THEN ISNULL(caAll.RB_RulePeriodId, caNext.RB_RulePeriodId)
ELSE t.RulePeriodId
END,
t.InvoiceKey,
t.IsAssociated,
0,
RebateUnits = ISNULL(CASE WHEN t.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND t.TermsPercent <> 0
THEN t.RebateUnits - (t.ChargeUnits * t.TermsPercent)
ELSE t.RebateUnits
END,0),
TotalGross = IsNull(t.GrossAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalNet = IsNull(t.NetAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalUnits = IsNull(t.UnitsShipped,0) + ISNULL(t.TaxUnits,0) + ISNULL(t.AddUnits,0),
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cte2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.MyDateCol
AND PeriodEnding >= t.MyDateCol
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.MyDateCol
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2011 at 11:09 am
WayneS (6/8/2011)
Tony,Starting over. The main (not single) procedure is totally rewritten to combine the TVF with it. If this works, we'll combine the TVF into the single procedure also.
Start off by dropping those six indexes created on the Invoice table. We'll re-evaluate what is needed there based upon the execution plan that this creates.
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
-- Determine which column to use
SELECT @Col2Use = CASE (CalcDateType)
WHEN 1 THEN 'ShipToDiscountDueDate'
WHEN 2 THEN 'ShipToNetDueDate'
WHEN 3 THEN 'TermsDiscountDueDate'
WHEN 4 THEN 'TermsNetDueDate'
ELSE 'InvoiceDate'
END
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
-- Create a temp table to hold all needed data.
-- Will probably need to modify the PK as we test this.
CREATE TABLE #Invoice (
InvoiceKey DECIMAL(18,0),
ShipToKey DECIMAL(18,0),
RulePeriodId DECIMAL(18,0),
MyDateCol DATETIME,
CategoryNumber NVARCHAR(50),
TermsDiscountAmount DECIMAL(18,6),
TermsPercent DECIMAL(18,6),
IsAssociated AS CASE RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END,
PRIMARY KEY CLUSTERED (InvoiceKey, CategoryNumber)
);
-- Build a string to get the data from the appropriate column
-- Are you sure that we can't do a UNION ALL here?
-- Why would you want to process an InvoiceKey / RulePeriodId twice?
SET @SQLCmd =
';WITH cte AS
(
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
)
INSERT INTO #Invoice
(InvoiceKey,
ShipToKey,
RulePeriodId,
MyDateCol,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT cte.InvoiceKey,
i.ShipToKey,
cte.RulePeriodId,
i.' + @Col2Use + ',
rc.CategoryNumber,
i.TermsDiscountAmount,
CASE WHEN i.TermsDiscountAmount = 0 OR @TermsRuleType = 0 THEN 0
ELSE i.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN it.TotalInvoiceAmount
ELSE it.InvoiceSubTotal
END
END
FROM cte
JOIN dbo.Invoice i -- 1:1
ON cte.InvoiceKey = i.InvoiceKey
JOIN dbo.InvoiceTotal it -- 1:1 per invoice
ON i.InvoiceKey = it.InvoiceKey
JOIN dbo.RB_RuleCategory rc -- 1:N per supplier/category
ON i.SupplierKey = rc.SupplierId;
';
-- Execute the string. Pass in the necessary parameters.
EXEC sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
WITH cte AS
(
-- Get data from the temp table. Combine with aggregate info from other tables.
SELECT i.InvoiceKey,
i.ShipToKey,
i.RulePeriodId,
i.MyDateCol,
i.CategoryNumber,
i.TermsDiscountAmount,
i.TermsPercent,
i.IsAssociated,
UnitsShipped = SUM(ili.UnitsShipped),
GrossAmount = SUM(ili.GrossAmount),
NetAmount = SUM(ili.NetAmount),
TaxUnits = COUNT(itt.InvoiceKey),
TaxAmount = SUM(itt.TaxAmount),
AddUnits = COUNT(itac.InvoiceKey),
AddAmount = SUM(CASE WHEN itac.[Type] = 'C' THEN ABS(itac.Amount) ELSE itac.amount END)
FROM #Invoice i
LEFT JOIN dbo.InvoiceLineItem ili
ON i.InvoiceKey = ili.InvoiceKey
AND ili.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalTax itt
ON i.InvoiceKey = itt.InvoiceKey
AND itt.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalAdditionalCosts itac
ON i.InvoiceKey = itac.InvoiceKey
AND itac.ProductRebateCategory = i.CategoryNumber
GROUP BY i.InvoiceKey, i.ShipToKey, i.RulePeriodId, i.MyDateCol,
i.CategoryNumber, i.TermsDiscountAmount, i.TermsPercent, i.IsAssociated
), cte2 AS
(
-- Add the RebateUnits and ChargeUnits columns
SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(GrossAmount,0) + ISNULL(TaxAmount,0) -- Gross
WHEN 2 THEN ISNULL(NetAmount,0) + ISNULL(TaxAmount,0) -- Net
WHEN 3 THEN ISNULL(UnitsShipped,0) + ISNULL(TaxUnits,0) -- Units
END,
ChargeUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(AddAmount,0) -- Gross
WHEN 2 THEN ISNULL(AddAmount,0) -- Net
WHEN 3 THEN ISNULL(AddUnits,0) -- Units
END
FROM cte
)
-- Insert everything into the RB_RuleInvoice table
INSERT INTO dbo.[RB_RuleInvoice]
([RB_RuleId],
[RB_RulePeriodId],
[InvoiceKey],
[IsAssociated],
[ForcastData],
[RebateUnits],
[GrossAmount],
[NetAmount],
[Units],
[DealerId],
[RebateAmount],
[RebateAmountPaid],
[RebateAmountActual],
[ReallocationAmount],
[ReallocationAmountPaid],
[ReallocationPaidFlag],
[PostPeriodAdjustAmount],
[TotalInvoiceNotPaid],
[TotalReallocation],
[PercentPayable])
SELECT @RuleId,
CASE WHEN t.RulePeriodId IS NULL OR t.RulePeriodId IN (-1,-3) THEN ISNULL(caAll.RB_RulePeriodId, caNext.RB_RulePeriodId)
ELSE t.RulePeriodId
END,
t.InvoiceKey,
t.IsAssociated,
0,
RebateUnits = ISNULL(CASE WHEN t.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND t.TermsPercent <> 0
THEN t.RebateUnits - (t.ChargeUnits * t.TermsPercent)
ELSE t.RebateUnits
END,0),
TotalGross = IsNull(t.GrossAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalNet = IsNull(t.NetAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalUnits = IsNull(t.UnitsShipped,0) + ISNULL(t.TaxUnits,0) + ISNULL(t.AddUnits,0),
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cte2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.MyDateCol
AND PeriodEnding >= t.MyDateCol
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.MyDateCol
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
LOL - you missed the constraint on Rb_Ruleid again -- I added it back in:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
-- Determine which column to use
SELECT @Col2Use = CASE (CalcDateType)
WHEN 1 THEN 'ShipToDiscountDueDate'
WHEN 2 THEN 'ShipToNetDueDate'
WHEN 3 THEN 'TermsDiscountDueDate'
WHEN 4 THEN 'TermsNetDueDate'
ELSE 'InvoiceDate'
END
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
-- Create a temp table to hold all needed data.
-- Will probably need to modify the PK as we test this.
CREATE TABLE #Invoice (
InvoiceKey DECIMAL(18,0),
ShipToKey DECIMAL(18,0),
RulePeriodId DECIMAL(18,0),
MyDateCol DATETIME,
CategoryNumber NVARCHAR(50),
TermsDiscountAmount DECIMAL(18,6),
TermsPercent DECIMAL(18,6),
IsAssociated AS CASE RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END,
PRIMARY KEY CLUSTERED (InvoiceKey, CategoryNumber)
);
-- Build a string to get the data from the appropriate column
-- Are you sure that we can't do a UNION ALL here?
-- Why would you want to process an InvoiceKey / RulePeriodId twice?
SET @SQLCmd =
';WITH cte AS
(
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
)
INSERT INTO #Invoice
(InvoiceKey,
ShipToKey,
RulePeriodId,
MyDateCol,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT cte.InvoiceKey,
i.ShipToKey,
cte.RulePeriodId,
i.' + @Col2Use + ',
rc.CategoryNumber,
i.TermsDiscountAmount,
CASE WHEN i.TermsDiscountAmount = 0 OR @TermsRuleType = 0 THEN 0
ELSE i.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN it.TotalInvoiceAmount
ELSE it.InvoiceSubTotal
END
END
FROM cte
JOIN dbo.Invoice i -- 1:1
ON cte.InvoiceKey = i.InvoiceKey
JOIN dbo.InvoiceTotal it -- 1:1 per invoice
ON i.InvoiceKey = it.InvoiceKey
JOIN dbo.RB_RuleCategory rc -- 1:N per supplier/category
ON i.SupplierKey = rc.SupplierId
where rc.rb_ruleid = @RuleId;
';
-- Execute the string. Pass in the necessary parameters.
print @SQLCmd
EXEC sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
WITH cte AS
(
-- Get data from the temp table. Combine with aggregate info from other tables.
SELECT i.InvoiceKey,
i.ShipToKey,
i.RulePeriodId,
i.MyDateCol,
i.CategoryNumber,
i.TermsDiscountAmount,
i.TermsPercent,
i.IsAssociated,
UnitsShipped = SUM(ili.UnitsShipped),
GrossAmount = SUM(ili.GrossAmount),
NetAmount = SUM(ili.NetAmount),
TaxUnits = COUNT(itt.InvoiceKey),
TaxAmount = SUM(itt.TaxAmount),
AddUnits = COUNT(itac.InvoiceKey),
AddAmount = SUM(CASE WHEN itac.[Type] = 'C' THEN ABS(itac.Amount) ELSE itac.amount END)
FROM #Invoice i
LEFT JOIN dbo.InvoiceLineItem ili
ON i.InvoiceKey = ili.InvoiceKey
AND ili.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalTax itt
ON i.InvoiceKey = itt.InvoiceKey
AND itt.ProductRebateCategory = i.CategoryNumber
LEFT JOIN dbo.InvoiceTotalAdditionalCosts itac
ON i.InvoiceKey = itac.InvoiceKey
AND itac.ProductRebateCategory = i.CategoryNumber
GROUP BY i.InvoiceKey, i.ShipToKey, i.RulePeriodId, i.MyDateCol,
i.CategoryNumber, i.TermsDiscountAmount, i.TermsPercent, i.IsAssociated
), cte2 AS
(
-- Add the RebateUnits and ChargeUnits columns
SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(GrossAmount,0) + ISNULL(TaxAmount,0) -- Gross
WHEN 2 THEN ISNULL(NetAmount,0) + ISNULL(TaxAmount,0) -- Net
WHEN 3 THEN ISNULL(UnitsShipped,0) + ISNULL(TaxUnits,0) -- Units
END,
ChargeUnits = CASE (@RuleUnitType)
WHEN 1 THEN ISNULL(AddAmount,0) -- Gross
WHEN 2 THEN ISNULL(AddAmount,0) -- Net
WHEN 3 THEN ISNULL(AddUnits,0) -- Units
END
FROM cte
)
-- Insert everything into the RB_RuleInvoice table
INSERT INTO dbo.[RB_RuleInvoice]
([RB_RuleId],
[RB_RulePeriodId],
[InvoiceKey],
[IsAssociated],
[ForcastData],
[RebateUnits],
[GrossAmount],
[NetAmount],
[Units],
[DealerId],
[RebateAmount],
[RebateAmountPaid],
[RebateAmountActual],
[ReallocationAmount],
[ReallocationAmountPaid],
[ReallocationPaidFlag],
[PostPeriodAdjustAmount],
[TotalInvoiceNotPaid],
[TotalReallocation],
[PercentPayable])
SELECT @RuleId,
CASE WHEN t.RulePeriodId IS NULL OR t.RulePeriodId IN (-1,-3) THEN ISNULL(caAll.RB_RulePeriodId, caNext.RB_RulePeriodId)
ELSE t.RulePeriodId
END,
t.InvoiceKey,
t.IsAssociated,
0,
RebateUnits = ISNULL(CASE WHEN t.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND t.TermsPercent <> 0
THEN t.RebateUnits - (t.ChargeUnits * t.TermsPercent)
ELSE t.RebateUnits
END,0),
TotalGross = IsNull(t.GrossAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalNet = IsNull(t.NetAmount,0) + ISNULL(t.TaxAmount,0) + ISNULL(t.AddAmount,0),
TotalUnits = IsNull(t.UnitsShipped,0) + ISNULL(t.TaxUnits,0) + ISNULL(t.AddUnits,0),
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cte2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.MyDateCol
AND PeriodEnding >= t.MyDateCol
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.MyDateCol
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
We are back to 14-16 seconds with the indexes removed...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'RB_Rule'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
;WITH cte AS
(
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
)
INSERT INTO #Invoice
(InvoiceKey,
ShipToKey,
RulePeriodId,
MyDateCol,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT cte.InvoiceKey,
i.ShipToKey,
cte.RulePeriodId,
i.InvoiceDate,
rc.CategoryNumber,
i.TermsDiscountAmount,
CASE WHEN i.TermsDiscountAmount = 0 OR @TermsRuleType = 0 THEN 0
ELSE i.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN it.TotalInvoiceAmount
ELSE it.InvoiceSubTotal
END
END
FROM cte
JOIN dbo.Invoice i -- 1:1
ON cte.InvoiceKey = i.InvoiceKey
JOIN dbo.InvoiceTotal it -- 1:1 per invoice
ON i.InvoiceKey = it.InvoiceKey
JOIN dbo.RB_RuleCategory rc -- 1:N per supplier/category
ON i.SupplierKey = rc.SupplierId
where rc.rb_ruleid = @RuleId;
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 9 ms.
Table 'RB_RuleCategory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_InvoicesToProcess'. Scan count 3, logical reads 1622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_InvoicesToProcess_Moved'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotal'. Scan count 3, logical reads 802, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Invoice'. Scan count 3, logical reads 51162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 3045 ms, elapsed time = 1683 ms.
SQL Server Execution Times:
CPU time = 3530 ms, elapsed time = 2174 ms.
Table 'InvoiceTotalTax'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Invoice____________________________________________________________________________________________________________00000000065F'. Scan count 3, logical reads 785, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotalAdditionalCosts'. Scan count 79432, logical reads 253242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceLineItem'. Scan count 3, logical reads 8698, physical reads 0, read-ahead reads 112, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RulePeriod'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RuleInvoice'. Scan count 0, logical reads 81317, 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.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 20471 ms, elapsed time = 12635 ms.
SQL Server Execution Times:
CPU time = 24001 ms, elapsed time = 14814 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
June 8, 2011 at 12:12 pm
tfeuz (6/8/2011)
LOL - you missed the constraint on Rb_Ruleid again -- I added it back in:
Just making sure you're looking at the code as a sanity check! :w00t:
Does this index improve anything?
CREATE INDEX IX_1
ON dbo.RB_RulePeriod (RB_RuleId, Reconciled, PeriodStarting, PeriodEnding);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2011 at 12:16 pm
WayneS (6/8/2011)
tfeuz (6/8/2011)
LOL - you missed the constraint on Rb_Ruleid again -- I added it back in:Just making sure you're looking at the code as a sanity check! :w00t:
Does this index improve anything?
CREATE INDEX IX_1ON dbo.RB_RulePeriod (RB_RuleId, Reconciled, PeriodStarting, PeriodEnding);
15 seconds...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'RB_Rule'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
;WITH cte AS
(
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
)
INSERT INTO #Invoice
(InvoiceKey,
ShipToKey,
RulePeriodId,
MyDateCol,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT cte.InvoiceKey,
i.ShipToKey,
cte.RulePeriodId,
i.InvoiceDate,
rc.CategoryNumber,
i.TermsDiscountAmount,
CASE WHEN i.TermsDiscountAmount = 0 OR @TermsRuleType = 0 THEN 0
ELSE i.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN it.TotalInvoiceAmount
ELSE it.InvoiceSubTotal
END
END
FROM cte
JOIN dbo.Invoice i -- 1:1
ON cte.InvoiceKey = i.InvoiceKey
JOIN dbo.InvoiceTotal it -- 1:1 per invoice
ON i.InvoiceKey = it.InvoiceKey
JOIN dbo.RB_RuleCategory rc -- 1:N per supplier/category
ON i.SupplierKey = rc.SupplierId
where rc.rb_ruleid = @RuleId;
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 487 ms, elapsed time = 487 ms.
Table 'RB_RuleCategory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_InvoicesToProcess'. Scan count 3, logical reads 1622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_InvoicesToProcess_Moved'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotal'. Scan count 3, logical reads 802, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Invoice'. Scan count 3, logical reads 51162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 3171 ms, elapsed time = 1721 ms.
SQL Server Execution Times:
CPU time = 3671 ms, elapsed time = 2209 ms.
Table 'InvoiceTotalTax'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Invoice____________________________________________________________________________________________________________00000000065F'. Scan count 3, logical reads 785, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotalAdditionalCosts'. Scan count 79432, logical reads 253242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceLineItem'. Scan count 3, logical reads 8698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RulePeriod'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RuleInvoice'. Scan count 0, logical reads 81317, 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.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 20531 ms, elapsed time = 12129 ms.
SQL Server Execution Times:
CPU time = 25499 ms, elapsed time = 15351 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Wayne -- just a quick note -- I am currently working on another query in an attempt to optimize. Following your examples you have laid out here is a tremendous help!
June 8, 2011 at 1:02 pm
Tony,
Well, I can't think of any other way to optimize this. I think that this leaves you with two choices:
1. Use the iTVF. Pros: same code is used by two procs; just as fast. Cons: 6 new indexes, with ramifications they incur on insert/update/delete statements.
2. Use this last method. Pros: just as fast, no new indexes. Cons: Duplicate functionality in two procedures.
It comes down to: Indexes, and code reuse. Or, no indexes and duplicate code.
I don't know your system or data. If the indexes are not an issue, I'd probably go with the iTVF method, just to keep that code in one place. (Plus, the two code pieces don't even look alike - they would each require consideration to implement changes.)
Glad to hear that you've been able to take some of this and apply it to other code.... THAT is why I do this (to teach others, so that they can do it themselves!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 8, 2011 at 1:24 pm
WayneS (6/7/2011)
TheSQLGuru (6/7/2011)
I was giving my own presentation @ sqlsat77 while yours was in progress. 😀Ah, so you were. I heard it went pretty well.
As long as they have fairly uniform distribution of values the table var should be ok I suppose. Still bet it will be more efficient without the PK though.
My money's on the optimal solution having no spools, probably 1-2 intermediary temp tables, no CTEs and likely a hash join or two. Lotta work to get from here to there though.
Right now, I have the TVF because there are two procedures that call it - one for just getting a single result set, and the other is this monster to get all of the unprocessed invoices. If I'm reading you correctly, you're suggesting to merge the TVF into the calling procedure - which would end up with duplicate code in multiple procedures. Am I reading you correctly?
You've mentioned a couple of times about getting rid of the CTEs. I'm curious as to the rational for this... they are effectively just a sub-query, and they are only being called once. The only way I can see this improving things is in combination with the above part of merging the code into its own procedure for the massive run. Then I can see something like this. Am I missing something here?
I admire you for taking this one on (and for sticking with it). Its unquestionably a paid gig IMHO.
Thanks. And it true. But, earlier in the thread, I badgered Tony to get the DDL/sample data posted so that we could help. Once he did so, I sorta felt obligated to stick around and see it through.
Like I said - I believe the optimal solution will involve some intermediate temp tables to help the optimizer out. Too much going on for it to have a good chance at success, certainly for widely disparate inputs.
CTEs can lead people to do that too - put too much into one query. It is my belief that people can wrap more up into a single CTE-based query than they could without CTEs and thus they get overly complex queries where the optimization proccess isn't successful. Without the CTE construct they would often be 'forced' into the best solution - which could well be intermediary temp tables. Like you mentioned CTEs if referenced more than once (which can be done unintentionally with multi-level queries) cause multiple instantiations of the result. It is my belief they are also often coded to lead to spooling, although I have no empiracle evidence to support that belief. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 8, 2011 at 1:36 pm
1) try queries with OPTION (MAXDOP 1), just for posterity's sake
2) if this query can't possibly result in duplicates, use UNION ALL instead. that will avoid a SORT/DISTINCT operation:
SELECT itp.InvoiceKey, itp.RulePeriodId
FROM dbo.RB_InvoicesToProcess itp
WHERE itp.RB_RuleId = @RuleId
UNION
SELECT itpm.InvoiceKey, itpm.RulePeriodId
FROM dbo.RB_InvoicesToProcess_Moved itpm
WHERE itpm.RB_RuleId = @RuleId
3) Try removing the clustered PK from the temp table (unless you REALLY want to ensure uniqueness and throw an error if you don't have it)
4) Did you guys ever find a useful index for the RB_RulePeriod table?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 106 through 117 (of 117 total)
You must be logged in to reply to this topic. Login to reply