June 3, 2011 at 6:56 am
I haven't read the whole thread, I just skimmed at the exec plan.
RB_Rule
RB_RulePeriod
The stats seem way off. expected rows is 1 vs 80K for actual.
You also get a timeout for reason for early terminaison. That means that while the query runs pretty fast, there's likely a faster way to run it... the server just doesn't have the query to allow him to find it!
June 3, 2011 at 7:03 am
The 2 table you refer to have only 1 row in them ---
The 80K of invoices is in the rb_invoicestoprocess table --- this is the list of items that I need to iterate through and recalculate
June 3, 2011 at 7:09 am
tfeuz (6/3/2011)
The 2 table you refer to have only 1 row in them ---The 80K of invoices is in the rb_invoicestoprocess table --- this is the list of items that I need to iterate through and recalculate
Ok, but the server usually estimate that pretty good.
I'd still look closely to find query that the server is able to optimize (divide and conquer).
June 3, 2011 at 7:20 am
Looking at the execution plan in SQL Sentry Plan Explorer, I see that two of the biggest operations are a table scan on the RB_InvoicesToProcess table, and an index scan on the InvoiceTable table, using one of the _dta indexes. The table scan says that there is no clustered index on this table. Can you provide the index definitions for all indexes on these two tables? (If they are in your earlier post, let me know and I'll get them from there.)
In the RB_InvoicesToProcess table, are you always going to process all records in this table? If so, then why are we concerned with matching on the rb_ruleid column? It really looks like this could benefit from an index on InvoiceKey + RuleID (assuming that InvoiceKey is more selective).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 7:26 am
The rb_invoicestoprocess table has no indexes on it.
The idea is that the table will have many rows in it at any given time and I will only process it one rule at a time. Due to this contstraint we need to leave the condition about rule id in the queries.
I will add the index you suggest and try it again...
The Invoice table has the following indexes on it:
CREATE NONCLUSTERED INDEX [_dta_index_Invoice_24_1044198770__K9_K1_K2_K92_K42_K17_3_5_11_18_19_23_43_44_117] ON [dbo].[Invoice]
(
[SoldToKey] ASC,
[InvoiceKey] ASC,
[InvoiceDate] ASC,
[DeletedFlag] ASC,
[SupplierKey] ASC,
[ShipToKey] ASC
)
INCLUDE ( [InvoiceNumber],
[PurchaseOrderNumber],
[SoldToName],
[ShipToNumber],
[ShipToName],
[ShipToProvince],
[SupplierName],
[SupplierNumber],
[BillingType]) 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
/****** Object: Index [PK_InvoiceHeader] Script Date: 06/03/2011 09:23:34 ******/
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [PK_InvoiceHeader] PRIMARY KEY CLUSTERED
(
[InvoiceKey] 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
June 3, 2011 at 7:44 am
tfeuz (6/3/2011)
The 2 table you refer to have only 1 row in them ---The 80K of invoices is in the rb_invoicestoprocess table --- this is the list of items that I need to iterate through and recalculate
Okay, this will help out the RB_Rule table:
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)
AS
DECLARE @CalcDateType INT;
SELECT @CalcDateType = CalcDateType
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
;
WITH cteMain AS
(
SELECT a.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
WHERE c.rb_ruleid = @RuleId
UNION
SELECT a.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
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 7:49 am
tfeuz (6/3/2011)
The rb_invoicestoprocess table has no indexes on it.The idea is that the table will have many rows in it at any given time and I will only process it one rule at a time. Due to this contstraint we need to leave the condition about rule id in the queries.
I will add the index you suggest and try it again...
The Invoice table has the following indexes on it:
On the RB_InvoicesToProcess table, you might want to try InvoiceKey+RuleId as well as RuleId+InvoiceKey to see which one works better. I would assume that you would have a lot of invoices for a given Rule, so the first would be more selective.
As for the Invoice table... I meant the InvoiceTotal table what indexes are on it. (I had said InvoiceTable table... guess my fingers got ahead of my brain there...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 7:53 am
tfeuz (6/2/2011)
Wayne,Item #1
- I removed the variables you mentioned. Does having these extra variables "hanging around" cause preformance issues?
I forgot to answer this. No, there isn't a performance issue - well, not with the query anyway. Just a confusion issue (why is that variable here? I don't see it being used... let me look for it again...), which leads to a personal performance issue (spending more time figuring out what is going on than necessary).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 8:05 am
For the InvoiceTotal table:
1. You have overlapping indexes... the second one isn't necessary, and will cause extra overhead with insert/update/delete statements. Go ahead and remove it:
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceTotal_43_832058050__K2_3_7] ON [dbo].[InvoiceTotal]
(
[InvoiceKey] ASC
)
INCLUDE ( [TotalInvoiceAmount],
[InvoiceSubTotal]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceTotal_43_832058050__K2] ON [dbo].[InvoiceTotal]
(
[InvoiceKey] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
2. I think that the InvoiceTotal table isn't necessary. If so, it will remove 30% of the cost of this query! In the spCalculateRuleInvoiceUnits procedure, please try removing this block of code in the "cteMain" cte:
SELECT a.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
WHERE c.RB_RuleId = @RuleId
UNION
SELECT a.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
WHERE c.RB_RuleId = @RuleId
and replace it with this:
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
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 8:15 am
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...
New query plan uploaded....
June 3, 2011 at 8:20 am
How many runs did you do? Could be just from regenerating the plan and caching the data... 6 sec on the 1st run is not really bad news... not if after 5 runs it's always 6 seconds slower then you have a problem.
June 3, 2011 at 8:21 am
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)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 8:24 am
WayneS (6/3/2011)
Looking at the execution plan in SQL Sentry Plan Explorer, I see
Holly "/*& )(* /"*(/& "!*(/&"!) ? / "*/& " that's so cool! :w00t:
June 3, 2011 at 8:28 am
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.
June 3, 2011 at 8:29 am
Ninja's_RGR'us (6/3/2011)
WayneS (6/3/2011)
Looking at the execution plan in SQL Sentry Plan Explorer, I seeHolly "/*& )(* /"*(/& "!*(/&"!) ? / "*/& " that's so cool! :w00t:
Really nice, especially with some of the recent additions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 31 through 45 (of 117 total)
You must be logged in to reply to this topic. Login to reply