June 6, 2011 at 9:48 am
The image of a plan is useless... we always need the actual .sqlplan file.
June 6, 2011 at 9:50 am
Thanks Ninja --
The plan was included in the previous post -- I was simply posting a pitcture to get Wayne's comfirmation that I was looking in the same area of the plan as he was...
Tony
June 6, 2011 at 9:54 am
Wayne,
Missed one: Yes I tried the index order both ways -- the invoicekey is currently first.
June 6, 2011 at 9:57 am
tfeuz (6/6/2011)
Thanks Ninja --The plan was included in the previous post -- I was simply posting a pitcture to get Wayne's comfirmation that I was looking in the same area of the plan as he was...
Tony
Ya missed it... I'm more skimming on this thread more than anything else.
June 6, 2011 at 10:36 am
tfeuz (6/6/2011)
Wayne...Here is index #1
USE [TBR3]
GO
/****** Object: Index [_dta_index_InvoiceLineItem_43_800057936__K2_K28_K17_K21_K1_10] Script Date: 06/06/2011 11:24:37 ******/
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceLineItem_43_800057936__K2_K28_K17_K21_K1_10] ON [dbo].[InvoiceLineItem]
(
[InvoiceKey] ASC,
[ProductRebateCategory] ASC,
[GrossAmount] ASC,
[NetAmount] ASC,
[InvoiceLineKey] ASC
)
INCLUDE ( [UnitsShipped]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Here is index #2
USE [TBR3]
GO
/****** Object: Index [PK_InvoiceLineItem] Script Date: 06/06/2011 11:25:11 ******/
ALTER TABLE [dbo].[InvoiceLineItem] ADD CONSTRAINT [PK_InvoiceLineItem] PRIMARY KEY CLUSTERED
(
[InvoiceLineKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I looked at plan (see attached image) -- I assume the problem area is what you are looking at?
I was going to try and attempt to generate the "missing index" myself....
I think it should be on invoicekey and include grossamount, netamount and unitsshipped.
It appears that Index #1 has 2 of the 3 (it is missing units)....
Am I on the right track?
1. Yes... but your image is showing it as 4%; I'm showing it as 40%.
2. What "missing index" were you going to generate?
3. Actually, Index #1 has the UnitsShipped column as an included column already.
tfeuz (6/6/2011)
Wayne,Missed one: Yes I tried the index order both ways -- the invoicekey is currently first.
Is this the way that the "slow" query plan is for?
Can you build the index the other way, run it, and attach that query plan?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 10:50 am
Wayne,
I have attached the 2 query plans - one name invoicekeyfirst and one named invoicekeysecond....
Ignore my other questions -- I was off down a wrong path...
Tony
Just so we are on the same page -- this is the current SP we are working with. The ones using string concatentation took at least 2-3 times longer to execute:
-- fastest so far
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
c.InvoiceKey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
FROM dbo.RB_InvoicesToProcess c
JOIN dbo.Invoice b
ON c.InvoiceKey = b.InvoiceKey
WHERE c.RB_RuleId = @RuleId
UNION
SELECT
c.InvoiceKey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
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;
June 6, 2011 at 11:31 am
tfeuz (6/6/2011)
Wayne,Just so we are on the same page -- this is the current SP we are working with. The ones using string concatentation took at least 2-3 times longer to execute:
Tony,
I really think that this is the wrong track to take - tackling that next area could be the thing that makes this shine. Sometimes, you end up taking a step back to make that big leap forward, which is where I think we're at. As it is, the approach you're rejecting is reducing the Invoice table activity branch to < 1%, where the code you want to use has it at 74%. At 1%, it is clearly showing the next areas of improvement.
But, if that's what you want, then you need to do the 6 procedure approach as I detailed in this post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 11:42 am
Tony,
Can you add SET STATISTICS IO,TIME ON; to the test that you're running (not to the procedures), and send the results?
Please do it for "slow" and "InvoiceKeyFirst" methods.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 11:43 am
Wayne,
I am certainly not going to change taking your advice at this point 😉
That also explains why my plan was show operations on the invoice table of over 20%
But I am glad I did clarify where we are at....
Here is the stored 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,
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;
I have attached the 2 query plans using the above procedure and changing the indexes as you requested....
TOny
June 6, 2011 at 11:48 am
Wayne,
Just saw your last post ---
Here are the attached plans and the sql output...
InvoiceKeyFirst:
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 = 6 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.
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 317733, logical reads 2172231, 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 '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 'Invoice'. Scan count 5, logical reads 61999, 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 408, 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 = 20109 ms, elapsed time = 20457 ms.
SQL Server Execution Times:
CPU time = 20109 ms, elapsed time = 20457 ms.
SQL Server Execution Times:
CPU time = 20109 ms, elapsed time = 20464 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.
InvoiceKeySecond:
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 = 0 ms.
Table 'RB_InvoicesToProcess'. Scan count 3, logical reads 1620, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Invoice'. Scan count 7, logical reads 63648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 317733, logical reads 2172231, 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 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 '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 '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 '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.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 21890 ms, elapsed time = 20470 ms.
SQL Server Execution Times:
CPU time = 22093 ms, elapsed time = 20676 ms.
SQL Server Execution Times:
CPU time = 22093 ms, elapsed time = 20678 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 6, 2011 at 12:07 pm
Here is the slower one (although they all seem to be running about same now)
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd VARCHAR(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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
), 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(' + CAST(@RuleUnitType as VARCHAR(MAX)) + ',' + CAST(@TermsRuleType as VARCHAR(MAX)) + ',' + CAST(@RuleId as VARCHAR(MAX)) + ', 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 ' + CAST(@RuleId as VARCHAR(MAX)) + ',
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
AND PeriodStarting >= t.' + @Col2Use + '
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
';
--print @SQLCmd
EXECUTE (@SQLCmd);
With the invoicekey first:
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 = 0 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 317733, logical reads 2172231, 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 '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 'Invoice'. Scan count 5, logical reads 61999, 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 408, 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 = 18875 ms, elapsed time = 19381 ms.
SQL Server Execution Times:
CPU time = 19062 ms, elapsed time = 19577 ms.
SQL Server Execution Times:
CPU time = 19062 ms, elapsed time = 19579 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.
With the invoicekey second
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 = 0 ms, elapsed time = 0 ms.
Table 'RB_InvoicesToProcess'. Scan count 3, logical reads 1620, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Invoice'. Scan count 7, logical reads 63648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 317733, logical reads 2172231, 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 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 '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 '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 '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.
(79432 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 21860 ms, elapsed time = 20493 ms.
SQL Server Execution Times:
CPU time = 22063 ms, elapsed time = 20700 ms.
SQL Server Execution Times:
CPU time = 22063 ms, elapsed time = 20705 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 6, 2011 at 2:38 pm
I just spent a few minutes looking at one of your later plans. It is my belief that you have no hope of significant improvements based on your current approach to this solution. You cannot win with that many nested-loops-on-many-rows and spools (SEVEN of those!). You have painted yourself into a performance dead end with those CTEs and CROSS APPLYs. And I sure hope the function you have listed is an inline TVF or it gets worse (although that is actually a good thing because you can refactor that out for a potential perf gain). Oh, and you may well have a SORT/DISTINCT going on for no reason due to the user of UNION instead of UNION ALL (assuming there cannot be dupes between the two invoices to process tables.
You could try forcing some non-loop (hash most likely?) joins but that may not be allowed with the query forms you currently have. Look to InvoiceLineItem first for this.
Most likely you will need to break down into intermediate results to get any reasonable performance. The out-of-the-box part of my brain says to evaluate a cursor-based solution too, but since it isn't a running total I will ignore that recommendation. 🙂
P.S. Stop worrying about percentages between refactors. It is useless to say table A is now 3% of total when it used to be 68%. You either improved the query as a whole by some metric that is important to you (duration, CPU, IO, etc) or you didn't.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 2:40 pm
tfeuz (6/6/2011)
Wayne,I am certainly not going to change taking your advice at this point 😉
That also explains why my plan was show operations on the invoice table of over 20%
But I am glad I did clarify where we are at....
Here is the stored 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,
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;
I have attached the 2 query plans using the above procedure and changing the indexes as you requested....
TOny
Tony,
1. The InvoiceKey definitely needs to be the first column in those 5 indexes. So, let's only use that from here on.
2. Lets add another index to the Invoice table: InvoiceKey, SupplierKey INCLUDE(TermsDiscountAmount) (Yikes... that's 6 indexes being added to this table - but this should take care of 3 index scans and 1 table (CI) sc)
Make sure you're using this parametrized dynamic sql procedure, and please include the statistics
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 2:57 pm
As requested....
Stats
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 = 0 ms.
Table 'RB_RuleInvoice'. Scan count 0, logical reads 81318, 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 = 16047 ms, elapsed time = 16751 ms.
SQL Server Execution Times:
CPU time = 16047 ms, elapsed time = 16751 ms.
SQL Server Execution Times:
CPU time = 16047 ms, elapsed time = 16753 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 -- just so we are on the same page
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,0)
AS
DECLARE @Col2Use sysname,
@SQLCmd VARCHAR(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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
), 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(' + CAST(@RuleUnitType as VARCHAR(MAX)) + ',' + CAST(@TermsRuleType as VARCHAR(MAX)) + ',' + CAST(@RuleId as VARCHAR(MAX)) + ', 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 ' + CAST(@RuleId as VARCHAR(MAX)) + ',
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
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 = ' + CAST(@RuleId as VARCHAR(MAX)) + '
AND PeriodStarting >= t.' + @Col2Use + '
AND Reconciled = 0
ORDER BY RB_RulePeriodId ASC
) caNext;
';
EXECUTE (@SQLCmd);
SQL plan attached...
June 6, 2011 at 3:14 pm
FYI: Wrong procedure. Need the one that has the parametrized string, calling sp_executesql at the end.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 76 through 90 (of 117 total)
You must be logged in to reply to this topic. Login to reply