June 3, 2011 at 8:36 am
Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
tfeuz (6/3/2011)
Odd---I dropped the redundant index
Added the new index to the rb_invoicestoprocess table (invoicekey,ruleid)
Made the change to the SP as you suggested....
It actually went from 23 seconds up to 29 seconds....
Not sure what I missed...
Ahh, I think we added a bookmark lookup - need to include RulePeriodId as an Include column on the RB_InvoicesToProcess index.
We need to see a new execution plan (.sqlplan) to see what all is going on now.
Did the change of removing the InvoiceTotal table get implemented? If so, is the proc still producing the correct results?
Did you try reversing the columns in the RB_InvoicesToProcess index? (RuleId + InvoiceKey)
The new plan has been edited in. There's a distinct / sort operation at almost 61% that wasn't there.
tfeuz, just fyi we get e-mail notification on new message on the threads, but not message edits.
The RB_InvoicesToProcess still did a table scan... I think we definitely need to reverse the columns in that index to RuleId + InvoiceKey + include the RulePeriodId column.
That sort is coming from the iTVF function - the correlated subquery. Try this to see if this gets rid of it (moved the subqueries up to be a joined table):
CREATE FUNCTION dbo.fnCalculateRuleInvoiceUnits_Single (
@RuleUnitType numeric(9,2),
@TermsRuleType numeric(9,2),
@RuleId decimal(18,6),
@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 UnitsShipped AS unitsshipped,
GrossAmount AS grossamount,
NetAmount AS 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
), cteTotalCharge AS
(
SELECT TotalChargeUnits = SUM(1),
TotalChargeGross = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END),
TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END)
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN 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;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 8:45 am
In both procedures, and the function, change the @RuleId parameter from decimal(18,6) to decimal(18,0) to match the actual datatype of the columns.
Edit: In the RB_InvoicesToProcess and the RB_InvoicesToProcess_Moved tables, the InvoiceKey column is defined as varchar(50). In the other tables, this column is a decimal(18,0).
In RB_RuleCategory, the SupplierId is defined as varchar(50); it's being joined to the Invoice.SupplierKey column which is a decimal(18,0).
All of these cause implicit conversions, which can result in indexes not being utilized.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 8:50 am
Working through all the suggested changes....
but the function has this syntax error:
Msg 102, Level 15, State 1, Procedure fnCalculateRuleInvoiceUnits_Single, Line 46
Incorrect syntax near ','.
I cannot really see the issue?
June 3, 2011 at 8:57 am
tfeuz (6/3/2011)
Working through all the suggested changes....but the function has this syntax error:
Msg 102, Level 15, State 1, Procedure fnCalculateRuleInvoiceUnits_Single, Line 46
Incorrect syntax near ','.
I cannot really see the issue?
Whoops, I forgot the ") DerivedTableAlias" part on the code just substituted in. Use this...
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 UnitsShipped AS unitsshipped,
GrossAmount AS grossamount,
NetAmount AS 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 [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END),
TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END)
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN 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;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 9:12 am
I made all the changes requested...
Altered the index order and included the column
Changed the data types from 18,6 to 18,0
Change the actual table definitions to use decimal(18,0) instead of nvarchar(50) to remove the implicit conversions
Updated all the indexes
Ran it several times and it is still taking about 25 seconds for the data set
New Execution plan attached...
June 3, 2011 at 9:32 am
tfeuz (6/3/2011)
I made all the changes requested...Altered the index order and included the column
This got rid of the table scan.
Changed the data types from 18,6 to 18,0
This got rid of the implicit conversions.
Change the actual table definitions to use decimal(18,0) instead of nvarchar(50) to remove the implicit conversions
Updated all the indexes
Ran it several times and it is still taking about 25 seconds for the data set
New Execution plan attached...
It doesn't look like you're running the code that helped out with the RB_Rule table (moved to one call into a variable, then use that variable in the main query):
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @CalcDateType INT;
SELECT @CalcDateType = CalcDateType
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
;
WITH cteMain AS
(
SELECT --a.InvoiceKey,
c.InvoiceKey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
--FROM dbo.InvoiceTotal a
-- JOIN dbo.Invoice b
-- ON a.InvoiceKey = b.InvoiceKey
-- JOIN dbo.RB_InvoicesToProcess c
-- ON a.InvoiceKey = c.InvoiceKey
FROM dbo.RB_InvoicesToProcess c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
UNION
SELECT --a.InvoiceKey,
c.InvoiceKey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
--FROM dbo.InvoiceTotal a
-- JOIN dbo.Invoice b
-- ON a.InvoiceKey = b.InvoiceKey
-- JOIN dbo.RB_InvoicesToProcess_Moved c
-- ON a.InvoiceKey = c.InvoiceKey
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.*,
InvoiceDate2 = CASE (@CalcDateType)
WHEN 1 THEN cteMain.ShipToDiscountDueDate
WHEN 2 THEN cteMain.ShipToNetDueDate
WHEN 3 THEN cteMain.TermsDiscountDueDate
WHEN 4 THEN cteMain.TermsNetDueDate
ELSE cteMain.InvoiceDate
END,
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.InvoiceDate2
AND PeriodEnding >= t.InvoiceDate2
AND Reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
RB_RulePeriodId
FROM dbo.RB_RulePeriod
WHERE RB_RuleId = @RuleId
AND PeriodStarting >= t.InvoiceDate2
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 9:48 am
Well that go it down to about 18 seconds....
Is it possible that what I am trying to do can not be optimized down to just a few seconds and we have taken it as far as we can go?
I am going to take the weekend to review the code and digest your changes and I am sure I will have questions...I really want to understand what you did so I can avoid mistakes in the future....
I have attached the latest plan just in case you want to review....
June 3, 2011 at 10:04 am
On dbo.InvoiceTotalAdditionalCosts, do you have any indexes? It would benefit from:
CREATE INDEX IX_1 ON dbo.InvoiceTotalAdditionalCosts (InvoiceKey, ProductRebateCategory) INCLUDE(Amount, Type)
What is the definition of the _dta_index_Invoice_24_1044198770__K9_K1_K2_K92_K42_K17_3_5_11_18_19_23_43_44_117 index on the dbo.Invoice table?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 10:20 am
tfeuz (6/3/2011)
Well that go it down to about 18 seconds....Is it possible that what I am trying to do can not be optimized down to just a few seconds and we have taken it as far as we can go?
Well, the latest plan shows that 46% of the cost is involved in an clustered index scan (aka table scan) of the invoice table, and the Nested Loops operator taking 15%. That's 61% of the query... reducing that should make a noticeable affect on the time.
On the InvoiceTotal table, the _dta_index_InvoiceTotal_43_832058050__K2 index... can you add the InvoiceSubTotal and TotalInvoiceAmount columns as included columns on this index? This would remove a Key Lookup accounting for 2.9%.
On the InvoiceTotalAdditionalCosts table, the _dta_index_InvoiceTotalAdditionalCosts_43_1010102639__K3_K9_1 index, can you add the Amount and Type columns as included columns to this index? This would remove a Key Lookup for 3.4%.
It all adds up. Those three things account for 2/3 of the query... 12 of those 18 seconds. We still have opportunities to make things better!
I am going to take the weekend to review the code and digest your changes and I am sure I will have questions...I really want to understand what you did so I can avoid mistakes in the future....
I have attached the latest plan just in case you want to review....
Ask away - though I won't be online very much more today... getting ready to take off to go to SQL Saturday 77 in Pensacola, FL in about an hour.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 10:26 am
WayneS (6/3/2011)
On dbo.InvoiceTotalAdditionalCosts, do you have any indexes? It would benefit from:
CREATE INDEX IX_1 ON dbo.InvoiceTotalAdditionalCosts (InvoiceKey, ProductRebateCategory) INCLUDE(Amount, Type)
Don't add this one... add those INCLUDE columns to the existing index mentioned in the last post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 10:26 am
Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 10:35 am
WayneS (6/3/2011)
Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?
Nothing major... at this point I'd also be whacking the 60 off % scan by changing the indexes.
I don't think I have much to teach you in that regard ;-).
June 3, 2011 at 10:42 am
Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?Nothing major... at this point I'd also be whacking the 60 off % scan by changing the indexes.
I don't think I have much to teach you in that regard ;-).
There's always something else cool to learn that makes a difference... and you never know who you're going to learn it from! 😉
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 10:54 am
WayneS (6/3/2011)
Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
Mr. Ninja... I know you're lurking... do you see any other things to do, or that you would do differently?Nothing major... at this point I'd also be whacking the 60 off % scan by changing the indexes.
I don't think I have much to teach you in that regard ;-).
There's always something else cool to learn that makes a difference... and you never know who you're going to learn it from! 😉
Not wanting to hijack... but got any idea on my other problem :w00t:.
June 3, 2011 at 3:52 pm
All;
I did the requested cumulative changes and it actually got worse -- it is now at 25 seconds instead of 18....
Execution plan attached
TF
Viewing 15 posts - 46 through 60 (of 117 total)
You must be logged in to reply to this topic. Login to reply