June 6, 2011 at 3:20 pm
Darn --
Here you go
Statistics:
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 = 2 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 parse and compile time:
CPU time = 542 ms, elapsed time = 542 ms.
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.
Table 'Worktable'. Scan count 79437, logical reads 543084, 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 'InvoiceTotal'. Scan count 79432, logical reads 239122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotalTax'. Scan count 79432, 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'. Scan count 2, logical reads 715740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RuleCategory'. Scan count 238296, logical reads 476592, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceLineItem'. Scan count 79432, logical reads 247626, 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 238296, 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_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 'RB_InvoicesToProcess'. Scan count 1, logical reads 409, 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 = 16218 ms, elapsed time = 17458 ms.
SQL Server Execution Times:
CPU time = 16890 ms, elapsed time = 18001 ms.
SQL Server Execution Times:
CPU time = 16890 ms, elapsed time = 18010 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.
Procedure:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
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;
SET @SQLCmd =
';
WITH cteMain AS
(
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
UNION
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess_Moved c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
), cteMain2 AS
(
SELECT cteMain.*,
fn.*,
IsAssociated = CASE cteMain.RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END
FROM cteMain
CROSS APPLY dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType, @TermsRuleType, @RuleId, cteMain.InvoiceKey) fn
)
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,
t.RebateUnits,
CASE WHEN t.TotalGross is null then 0 else t.TotalGross end ,
CASE WHEN t.TotalNet is null then 0 else t.TotalNet end,
CASE WHEN t.TotalUnits is null then 0 else t.TotalUnits end,
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cteMain2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.' + @Col2Use + '
AND PeriodEnding >= t.' + @Col2Use + '
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.' + @Col2Use + '
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
';
EXECUTE sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
Plan attached:
June 6, 2011 at 3:31 pm
Tony,
Okay, I don't think any additional indexes will help out... this looks to be about as efficient as possible as it is currently written.
I do see that the branch that calls the iTVF is the vast majority of this query. Inside that, we have 4 calls to the Invoice table and 3 calls to the RB_RuleCategory table. I'm looking to see if we can consolidate those... preferably, just one call to each. (Not sure if this is possible...)
Just to be complete, this is all of the code that I've got right now:
iTVF:
CREATE FUNCTION [dbo].[fnCalculateRuleInvoiceUnits_Single] (
@RuleUnitType NUMERIC(9,2),
@TermsRuleType NUMERIC(9,2),
@RuleId DECIMAL(18,0),
@InvoiceKey DECIMAL(18,0))
RETURNS TABLE
AS
RETURN
WITH cte AS
(
SELECT b.TermsDiscountAmount,
TermsPercent = b.TermsDiscountAmount / CASE WHEN @TermsRuleType = 1 THEN a.TotalInvoiceAmount ELSE a.InvoiceSubTotal END
FROM dbo.InvoiceTotal a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.InvoiceKey = @InvoiceKey
), cteTotals AS
(
SELECT TotalUnits = SUM(UnitsShipped),
TotalGross = SUM(GrossAmount),
TotalNet = SUM(NetAmount)
FROM (SELECT a.UnitsShipped,
a.GrossAmount,
a.NetAmount
FROM dbo.InvoiceLineItem a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN dbo.RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND c.CategoryNumber = a.ProductRebateCategory
WHERE a.InvoiceKey = @InvoiceKey
AND c.RB_RuleId = @RuleId
UNION ALL
SELECT 1 AS UnitsShipped,
a.TaxAmount AS GrossAmount,
a.TaxAmount AS NetAmount
FROM dbo.InvoiceTotalTax a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN dbo.RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE a.InvoiceKey = @InvoiceKey
AND c.RB_RuleId = @RuleId) DerivedTableAlias
), cteTotalCharge AS
(
SELECT TotalChargeUnits = SUM(1),
TotalChargeGross = SUM(CASE WHEN a.[Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END),
TotalChargeNet = SUM(CASE WHEN a.[Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END)
FROM dbo.InvoiceTotalAdditionalCosts a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN dbo.RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE c.RB_RuleID = @RuleId
AND b.InvoiceKey = @InvoiceKey
)
SELECT RebateUnits = CONVERT(DECIMAL(18,6),
ISNULL(CASE WHEN cte.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND cte.TermsPercent <> 0
THEN caTotals.RebateUnits - caCharge.UnitsToDeductFromTerms
- ((caCharge.UnitsToDeductFromTerms) * cte.TermsPercent)
+ caCharge.UnitsToDeductFromTerms
ELSE caTotals.RebateUnits
END
, 0)),
TotalUnits = CONVERT(DECIMAL(18,6), caTotals.TotalUnits + ISNULL(caCharge.TotalChargeUnits,0)),
TotalGross = CONVERT(DECIMAL(18,6), caTotals.TotalGross + ISNULL(caCharge.TotalChargeGross,0)),
TotalNet = CONVERT(DECIMAL(18,6), caTotals.TotalNet + ISNULL(caCharge.TotalChargeNet,0))
FROM cte
CROSS APPLY (SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN TotalGross
WHEN 2 THEN TotalNet
WHEN 3 THEN TotalUnits
END
FROM cteTotals) caTotals
CROSS APPLY (SELECT *, UnitsToDeductFromTerms =
CASE (@RuleUnitType)
WHEN 1 THEN TotalChargeGross
WHEN 2 THEN TotalChargeNet
WHEN 3 THEN TotalChargeUnits
END
FROM cteTotalCharge) caCharge;
Main proc:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
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;
SET @SQLCmd =
';
WITH cteMain AS
(
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
UNION
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess_Moved c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
), cteMain2 AS
(
SELECT cteMain.*,
fn.*,
IsAssociated = CASE cteMain.RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END
FROM cteMain
CROSS APPLY dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType, @TermsRuleType, @RuleId, cteMain.InvoiceKey) fn
)
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,
t.RebateUnits,
t.TotalGross,
t.TotalNet,
t.TotalUnits,
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cteMain2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.' + @Col2Use + '
AND PeriodEnding >= t.' + @Col2Use + '
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.' + @Col2Use + '
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
';
EXECUTE sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
GO
"Single" proc:
CREATE PROCEDURE [dbo].[spCalculateRuleInvoiceUnits_Single] @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0),@InvoiceKey DECIMAL(18,0),@RebateUnits decimal(18,6) OUTPUT,@TotalGross decimal(18,6) OUTPUT,@TotalNet decimal(18,6) OUTPUT,@TotalUnits decimal(18,6) OUTPUT
AS
SELECT @RebateUnits = RebateUnits,
@TotalUnits = TotalUnits,
@TotalGross = TotalGross,
@TotalNet = TotalNet
FROM dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType,@TermsRuleType,@RuleId,@InvoiceKey);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 3:39 pm
Wayne,
Check out my last post --- the "main" proc is slightly different as it has a case statement in the insert to trap for null values...i found that just this afternoon.
I was playing with the repeated queries to the rb_rulecategory table but even if I had code the value -- it seems to make no difference. (I thought i might be able to pass a list into the query instead of linking another table)....
TF
June 6, 2011 at 9:11 pm
Are you talking about the TotalGross,TotalNet, TotalUnits?
If so, try this instead:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd NVARCHAR(MAX);
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;
SET @SQLCmd =
';
WITH cteMain AS
(
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
UNION
SELECT c.InvoiceKey,
b.ShipToKey,
c.RulePeriodId,
b.' + @Col2Use + '
FROM dbo.RB_InvoicesToProcess_Moved c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
), cteMain2 AS
(
SELECT cteMain.*,
fn.*,
IsAssociated = CASE cteMain.RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END
FROM cteMain
CROSS APPLY dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType, @TermsRuleType, @RuleId, cteMain.InvoiceKey) fn
)
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,
t.RebateUnits,
IsNull(t.TotalGross,0),
IsNull(t.TotalNet,0),
IsNull(t.TotalUnits,0),
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cteMain2 t
CROSS APPLY (SELECT RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting <= t.' + @Col2Use + '
AND PeriodEnding >= t.' + @Col2Use + '
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.' + @Col2Use + '
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
';
EXECUTE sp_executesql @SQLCmd, N'@RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)', @RuleUnitType, @TermsRuleType, @RuleId;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 7:31 am
I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going through the execution plan, analyzing the data, are all things beginners or people who are not DBAs don't do, really think about, or even know how to approach. This thread has really been enlightening. Again, thank you. To the OP, good luck!
June 7, 2011 at 8:37 am
cgrammont (6/7/2011)
I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going through the execution plan, analyzing the data, are all things beginners or people who are not DBAs don't do, really think about, or even know how to approach. This thread has really been enlightening. Again, thank you. To the OP, good luck!
Yep, for the overall good of everyone, this is a good thread, but it really covers a lot of territory:
1. Shows that, when you post your DDL, sample data, and code, that people will look at it.
2. While it doesn't go step-by-step, by comparing the before and after code you can see at least one method for how to make c.u.r.s.o.r.s into a more set-based method.
3. Shows the importance of getting the actual execution plans.
4. Shows how different data types can cause hidden issues (implicit conversions).
... and we're not done yet!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 8:52 am
Wayne,
Here you go...this has the latest "isnull" change..sqlpplan is attached..
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 parse and compile time:
CPU time = 0 ms, elapsed time = 12 ms.
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.
Table 'Worktable'. Scan count 79437, logical reads 543084, 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 'InvoiceTotal'. Scan count 79432, logical reads 239122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceTotalTax'. Scan count 79432, 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'. Scan count 2, logical reads 715740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RB_RuleCategory'. Scan count 238296, logical reads 476592, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InvoiceLineItem'. Scan count 79432, logical reads 247626, 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 238296, 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_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 'RB_InvoicesToProcess'. Scan count 1, logical reads 409, 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 = 15141 ms, elapsed time = 15859 ms.
SQL Server Execution Times:
CPU time = 15141 ms, elapsed time = 15872 ms.
SQL Server Execution Times:
CPU time = 15141 ms, elapsed time = 15874 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.
Every run seems to yeild about 20 seconds per 80,000 invoices /1.2 million item rows
Tony
June 7, 2011 at 10:04 am
WayneS (6/7/2011)
cgrammont (6/7/2011)
I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going through the execution plan, analyzing the data, are all things beginners or people who are not DBAs don't do, really think about, or even know how to approach. This thread has really been enlightening. Again, thank you. To the OP, good luck!Yep, for the overall good of everyone, this is a good thread, but it really covers a lot of territory:
1. Shows that, when you post your DDL, sample data, and code, that people will look at it.
2. While it doesn't go step-by-step, by comparing the before and after code you can see at least one method for how to make c.u.r.s.o.r.s into a more set-based method.
3. Shows the importance of getting the actual execution plans.
4. Shows how different data types can cause hidden issues (implicit conversions).
... and we're not done yet!
Not sure I agree with the statement that this is a good learning thread. Using the correct datatypes is ABSOLUTELY something EVERYONE should be doing for sure. And knowing that query plans are important for tuning. Outside of that I have the following concerns:
a) You have added what, SIX indexes to a single table (not sure of other indexing) just to tune a SINGLE QUERY at an unknown cost on DML operations to maintain those indexes potentially leading to increased blocking, concurrency issues and application stalls.
b) The chosen solution has very similar constructs to RBAR in that it has SEVEN spools and a HUGE number of nested loop joins and bookmark lookups over VERY large numbers of rows
c) It is aggressively pursuing CTEs which are in part leading to item b) and hitting a brick wall in scalability. CTEs can be problematic constructs in current editions of SQL Server
d) It is leading people to think that extraordinarily complex solutions can be adequately achieved via forum threads. They simply cannot. Most people who may stumble on this thread in the future likely won't notice how many DAYS it has been going on. Frustration will likely ensue when someone thinks they can knock out a 10 to 20 hour refactoring job in an hour of back-and-forths on the forum.
YMMV, and I truly hope you guys are successful in your pursuits.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2011 at 12:50 pm
Tony,
I'm trying to redo the iTVF, and I have a few questions:
1. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotal for that InvoiceId?
2. For any Invoice.SupplierKey value, will there be one and only one row in RB_RuleCategory for that SupplierId?
3. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotalTax for that InvoiceId?
4. With the InvoiceTotalAdditionalCosts table, will there be either zero or one row for a given invoice #, or can there be > 1 row?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 12:54 pm
I'm trying to redo the iTVF, and I have a few questions:
1. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotal for that InvoiceId?
** Correct only 1 row
2. For any Invoice.SupplierKey value, will there be one and only one row in RB_RuleCategory for that SupplierId?
*** No - there are 1 to N categories per supplier - per rule
3. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotalTax for that InvoiceId?
*** No - there could be 0 to N entries in the tax table for an invoice
4. With the InvoiceTotalAdditionalCosts table, will there be either zero or one row for a given invoice #, or can there be > 1 row?
*** No - there could be 0 to N entries in the InvoiceTotalAdditionalCosts for an invoice
June 7, 2011 at 2:18 pm
Tony,
Here's a new TVF to try out. It is no longer an inline TVF; it's now a multi-statement TVF. I'm trying to hit some of the larger tables only once, vs. 4 times.
IF object_id('fnCalculateRuleInvoiceUnits_Single') IS NOT NULL DROP FUNCTION fnCalculateRuleInvoiceUnits_Single;
GO
CREATE FUNCTION fnCalculateRuleInvoiceUnits_Single (
@RuleUnitType NUMERIC(9,2),
@TermsRuleType NUMERIC(9,2),
@RuleId DECIMAL(18,0),
@InvoiceKey DECIMAL(18,0))
RETURNS @Rules TABLE (
RebateUnits DECIMAL(18,6),
TotalUnits DECIMAL(18,6),
TotalGross DECIMAL(18,6),
TotalNet DECIMAL(18,6))
AS
BEGIN
-- need a table for temporary storage
DECLARE @Invoice TABLE (
InvoiceKey DECIMAL(18,0),
CategoryNumber NVARCHAR(50),
TermsDiscountAmount DECIMAL(18,6),
TermsPercent DECIMAL(18,6),
PRIMARY KEY CLUSTERED (InvoiceKey, CategoryNumber));
-- get all the invoice info, plus all the categories.
INSERT INTO @Invoice
(InvoiceKey,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT i.InvoiceKey,
rc.CategoryNumber,
i.TermsDiscountAmount,
TermsPercent = 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 dbo.Invoice i
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 i.InvoiceKey = @InvoiceKey;
WITH cteTotals (Units, Gross, Net) AS
(
-- add up all the invoice line items and taxes
SELECT TotalUnits = SUM(UnitsShipped),
TotalGross = SUM(GrossAmount),
TotalNet = SUM(NetAmount)
FROM (
SELECT t2.UnitsShipped,
t2.GrossAmount,
t2.NetAmount
FROM dbo.InvoiceLineItem t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber
UNION ALL
SELECT 1,
t2.TaxAmount,
t2.TaxAmount
FROM dbo.InvoiceTotalTax t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber) a
), cteCharge (Units, Gross, Net) AS
(
-- add up all the additional costs
SELECT SUM(1),
SUM(t2.Amount),
SUM(t2.Amount)
FROM (SELECT InvoiceKey,
ProductRebateCategory,
Amount = CASE WHEN [Type] = 'C' THEN ABS(Amount)
ELSE Amount
END
FROM dbo.InvoiceTotalAdditionalCosts) t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber
)
INSERT INTO @Rules (RebateUnits, TotalUnits, TotalGross, TotalNet)
SELECT RebateUnits = CONVERT(DECIMAL(18,6),
ISNULL(CASE WHEN cte.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND cte.TermsPercent <> 0
THEN caTotals.RebateUnits - caCharge.UnitsToDeductFromTerms
- ((caCharge.UnitsToDeductFromTerms) * cte.TermsPercent)
+ caCharge.UnitsToDeductFromTerms
ELSE caTotals.RebateUnits
END
, 0)),
TotalUnits = CONVERT(DECIMAL(18,6), caTotals.Units + ISNULL(caCharge.Units,0)),
TotalGross = CONVERT(DECIMAL(18,6), caTotals.Gross + ISNULL(caCharge.Gross,0)),
TotalNet = CONVERT(DECIMAL(18,6), caTotals.Net + ISNULL(caCharge.Net,0))
FROM (SELECT TermsDiscountAmount = MAX(TermsDiscountAmount),
TermsPercent = MAX(TermsPercent)
FROM @Invoice) cte
CROSS APPLY (SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN Gross
WHEN 2 THEN Net
WHEN 3 THEN Units
END
FROM cteTotals) caTotals
CROSS APPLY (SELECT *, UnitsToDeductFromTerms =
CASE (@RuleUnitType)
WHEN 1 THEN Gross
WHEN 2 THEN Net
WHEN 3 THEN Units
END
FROM cteCharge) caCharge;
RETURN;
END;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 3:51 pm
I applaud you Wayne for thinking outside the box on this with a switch to an mTVF. I am sure you aware that the optimizer can no integrage the mTVF within the query plan like an iTVF nor can it get accurate estimates about what is gonna pop out of it, but perhaps reducing table hits may be worth those penalties. Two additional points you are also likely aware of: I can probably count on 1 hand the number of times where a clustered index on a temp object was actually beneficial. And table variables can result in horrible query plans due to lack of estimates on them. I would guess that your clustered PK is an attempt to thwart said limitation.
It will be interesting to see the results of this attempt. I would like to see it with a temp table instead of table var and also a table var without PK too, for posterity's sake. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2011 at 4:06 pm
TheSQLGuru (6/7/2011)
I applaud you Wayne for thinking outside the box on this with a switch to an mTVF. I am sure you aware that the optimizer can no integrage the mTVF within the query plan like an iTVF nor can it get accurate estimates about what is gonna pop out of it, but perhaps reducing table hits may be worth those penalties. Two additional points you are also likely aware of: I can probably count on 1 hand the number of times where a clustered index on a temp object was actually beneficial. And table variables can result in horrible query plans due to lack of estimates on them. I would guess that your clustered PK is an attempt to thwart said limitation.It will be interesting to see the results of this attempt. I would like to see it with a temp table instead of table var and also a table var without PK too, for posterity's sake. 🙂
Thanks Kevin,
Yep, aware of all of that. #temp table is not an option in a function; must use table vars. 🙁 Gonna have to depend on the IO/TIME stats a lot more now. That iTVF is 85% of the query, with 4 hits against one table, and 3 against another. This reduces it to just one hit per table. Good ole "Divide'N'Conquer" attempt.
The table var is filling for just one invoice, so it will have just one record per category for that invoice. I would think that this would be a relatively low row count, so a plan for that should be viable. I figure that the PK on the table var won't hurt, and just might help somehow. Since it's being built at declare time, shouldn't be any additional overhead - but it's easy enough to take it out and see what the difference is.
IMHO, the lack of stats is the bane of table vars. (Should have seen my presentation @ sqlsat77 this past weekend...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 6:18 pm
WayneS (6/7/2011)
TheSQLGuru (6/7/2011)
I applaud you Wayne for thinking outside the box on this with a switch to an mTVF. I am sure you aware that the optimizer can no integrage the mTVF within the query plan like an iTVF nor can it get accurate estimates about what is gonna pop out of it, but perhaps reducing table hits may be worth those penalties. Two additional points you are also likely aware of: I can probably count on 1 hand the number of times where a clustered index on a temp object was actually beneficial. And table variables can result in horrible query plans due to lack of estimates on them. I would guess that your clustered PK is an attempt to thwart said limitation.It will be interesting to see the results of this attempt. I would like to see it with a temp table instead of table var and also a table var without PK too, for posterity's sake. 🙂
Thanks Kevin,
Yep, aware of all of that. #temp table is not an option in a function; must use table vars. 🙁 Gonna have to depend on the IO/TIME stats a lot more now. That iTVF is 85% of the query, with 4 hits against one table, and 3 against another. This reduces it to just one hit per table. Good ole "Divide'N'Conquer" attempt.
The table var is filling for just one invoice, so it will have just one record per category for that invoice. I would think that this would be a relatively low row count, so a plan for that should be viable. I figure that the PK on the table var won't hurt, and just might help somehow. Since it's being built at declare time, shouldn't be any additional overhead - but it's easy enough to take it out and see what the difference is.
IMHO, the lack of stats is the bane of table vars. (Should have seen my presentation @ sqlsat77 this past weekend...)
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.
I was giving my own presentation @ sqlsat77 while yours was in progress. 😀
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.
I admire you for taking this one on (and for sticking with it). Its unquestionably a paid gig IMHO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2011 at 7:30 pm
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;"
I ended up stopping the query after 10 minutes --
IF object_id('fnCalculateRuleInvoiceUnits_Single') IS NOT NULL DROP FUNCTION fnCalculateRuleInvoiceUnits_Single;
GO
CREATE FUNCTION fnCalculateRuleInvoiceUnits_Single (
@RuleUnitType NUMERIC(9,2),
@TermsRuleType NUMERIC(9,2),
@RuleId DECIMAL(18,0),
@InvoiceKey DECIMAL(18,0))
RETURNS @Rules TABLE (
RebateUnits DECIMAL(18,6),
TotalUnits DECIMAL(18,6),
TotalGross DECIMAL(18,6),
TotalNet DECIMAL(18,6))
AS
BEGIN
-- need a table for temporary storage
DECLARE @Invoice TABLE (
InvoiceKey DECIMAL(18,0),
CategoryNumber NVARCHAR(50),
TermsDiscountAmount DECIMAL(18,6),
TermsPercent DECIMAL(18,6),
PRIMARY KEY CLUSTERED (InvoiceKey, CategoryNumber));
-- get all the invoice info, plus all the categories.
INSERT INTO @Invoice
(InvoiceKey,
CategoryNumber,
TermsDiscountAmount,
TermsPercent)
SELECT i.InvoiceKey,
rc.CategoryNumber,
i.TermsDiscountAmount,
TermsPercent = 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 dbo.Invoice i
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 i.InvoiceKey = @InvoiceKey
and @RuleId = rc.rb_ruleid;
WITH cteTotals (Units, Gross, Net) AS
(
-- add up all the invoice line items and taxes
SELECT TotalUnits = SUM(UnitsShipped),
TotalGross = SUM(GrossAmount),
TotalNet = SUM(NetAmount)
FROM (
SELECT t2.UnitsShipped,
t2.GrossAmount,
t2.NetAmount
FROM dbo.InvoiceLineItem t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber
UNION ALL
SELECT 1,
t2.TaxAmount,
t2.TaxAmount
FROM dbo.InvoiceTotalTax t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber) a
), cteCharge (Units, Gross, Net) AS
(
-- add up all the additional costs
SELECT SUM(1),
SUM(t2.Amount),
SUM(t2.Amount)
FROM (SELECT InvoiceKey,
ProductRebateCategory,
Amount = CASE WHEN [Type] = 'C' THEN ABS(Amount)
ELSE Amount
END
FROM dbo.InvoiceTotalAdditionalCosts) t2
JOIN @Invoice t
ON t2.InvoiceKey = t.InvoiceKey
AND t2.ProductRebateCategory = t.CategoryNumber
)
INSERT INTO @Rules (RebateUnits, TotalUnits, TotalGross, TotalNet)
SELECT RebateUnits = CONVERT(DECIMAL(18,6),
ISNULL(CASE WHEN cte.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND cte.TermsPercent <> 0
THEN caTotals.RebateUnits - caCharge.UnitsToDeductFromTerms
- ((caCharge.UnitsToDeductFromTerms) * cte.TermsPercent)
+ caCharge.UnitsToDeductFromTerms
ELSE caTotals.RebateUnits
END
, 0)),
TotalUnits = CONVERT(DECIMAL(18,6), caTotals.Units + ISNULL(caCharge.Units,0)),
TotalGross = CONVERT(DECIMAL(18,6), caTotals.Gross + ISNULL(caCharge.Gross,0)),
TotalNet = CONVERT(DECIMAL(18,6), caTotals.Net + ISNULL(caCharge.Net,0))
FROM (SELECT TermsDiscountAmount = MAX(TermsDiscountAmount),
TermsPercent = MAX(TermsPercent)
FROM @Invoice) cte
CROSS APPLY (SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN Gross
WHEN 2 THEN Net
WHEN 3 THEN Units
END
FROM cteTotals) caTotals
CROSS APPLY (SELECT *, UnitsToDeductFromTerms =
CASE (@RuleUnitType)
WHEN 1 THEN Gross
WHEN 2 THEN Net
WHEN 3 THEN Units
END
FROM cteCharge) caCharge;
RETURN;
END;
this was the stats at the time i stopped it...
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 = 5 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.
The statement has been terminated.
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.
Query was cancelled by user.
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
Viewing 15 posts - 91 through 105 (of 117 total)
You must be logged in to reply to this topic. Login to reply