June 3, 2011 at 10:34 pm
tfeuz (6/3/2011)
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
Even though time is probably the easiest thing to measure, it is not the only indicator of performance... other things going on at the same time can affect this.
The indexes have helped. The big remaining thing is to get rid of the table scan on the Invoice table - it's 65% of the total query.
Can you script out all of the indexes on the Invoice table, and post them?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 4, 2011 at 4:39 am
Sure -- here you go
USE [TBR3]
GO
/****** Object: Index [_dta_index_Invoice_24_1044198770__K9_K1_K2_K92_K42_K17_3_5_11_18_19_23_43_44_117] Script Date: 06/04/2011 06:38:26 ******/
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
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
Tony
June 4, 2011 at 10:13 pm
This isn't a clustered index, and there is a clustered index on that table.
Isn't the Primary Key on this table the InvoiceKey column? (which means that it has an index on it?) If so, is the PK a clustered index?
If InvoiceKey is the PK, and it isn't the clustered index, let's try making that the clustered index (seems like a good choice for it). If not, then let's try a new index on the Invoice table:
CREATE INDEX IX_InvoiceKey ON dbo.Invoice (InvoiceKey)
INCLUDE (InvoiceDate, ShipToDiscountDueDate, ShipToKey, ShipToNetDueDate, TermsDiscountDueDate, TermsNetDueDate);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 5, 2011 at 4:32 am
Wayne,
I think I am missing something -- the invoicekey is the PK and it is clustered:
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 5, 2011 at 7:04 am
tfeuz (6/5/2011)
Wayne,I think I am missing something -- the invoicekey is the PK and it is clustered:
It's not you missing it, it was me. I didn't scroll down far enough in the code you posted to see it... 🙁
I think I see what's causing the CI (table) scan... we're determining which column to use by the value of the @CalcDateType and comparing that against the PeriodStarting/PeriodEnding columns in the RB_RulePeriod table. The calculation for which column to use is performed for each row...
What we need to do is:
1. Create 5 indexes (one for each of the possible columns to use), and
2. Create 5 procedures (one for each of the possible columns to query against).
First, create 5 indexes on the Invoice table:
1. ShipToDiscountDueDate , InvoiceKey INCLUDE ShipToKey
2. ShipToNetDueDate, InvoiceKey INCLUDE ShipToKey
3. TermsDiscountDueDate, InvoiceKey INCLUDE ShipToKey
4. TermsNetDueDate, InvoiceKey INCLUDE ShipToKey
5. InvoiceDate, InvoiceKey INCLUDE ShipToKey
In the existing proc, after the SELECT @CalcDateTime, you would go:
IF @CalcDateTime = 1 EXECUTE Proc2 ELSE -- to use the ShipToDiscountDueDate column
IF @CalcDateTime = 2 EXECUTE Proc3 ELSE -- to use the ShipToNetDueDate column
IF @CalcDateTime = 3 EXECUTE Proc4 ELSE -- to use the TermsDiscountDueDate column
IF @CalcDateTime = 4 EXECUTE Proc5 ELSE -- to use the TermsNetDueDate column
EXECUTE Proc6; -- to use the InvoiceDate column
Take the rest of the code out of the existing proc, and put it into 5 new procs. In the CROSS APPLYs, change "InvoiceDate2" to the appropriate column. Remove the InvoiceDate2 calculation from the cteMain2 area. In the cteMain area, you only need the one column, so remove the other 4 date columns (on both sides of the union!) - or else the index won't cover the query and you'll be back to a CI Scan.
I'm not wild about adding 5 indexes, but they would all be narrow (one datetime, 2 integers), and they look like they would be pretty static.
If you know that most of the time @CalcDateType will evaluate to 1 or 2 values, you could choose to have indexes for only those columns - this would mean that the columns without the supporting indexes would have to use this slower CI (table) scan. It might be worth it if they aren't used frequently.
This issue is getting very close to a "catch-all query", as explained by Gail Shaw in her blog: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 5, 2011 at 7:33 am
Alternatively, you could still do that all in one proc, but it would use dynamic sql to build a string with the proper column, then execute that string. This way, all the logic would be in one place instead of 5 procs to maintain if anything changes. You'll still need the five indexes though.
Something like this...
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 = @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 (@SQLCmd);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 4:49 am
WayneS (6/5/2011)
First, create 5 indexes on the Invoice table:1. ShipToDiscountDueDate , InvoiceKey INCLUDE ShipToKey
2. ShipToNetDueDate, InvoiceKey INCLUDE ShipToKey
3. TermsDiscountDueDate, InvoiceKey INCLUDE ShipToKey
4. TermsNetDueDate, InvoiceKey INCLUDE ShipToKey
5. InvoiceDate, InvoiceKey INCLUDE ShipToKey
I was thinking... these indexes are going to be much more selective if the InvoiceKey column is first. You should try them out both ways.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 6:44 am
Wayne,
I added all the indexes and updated the stored procedure as you suggested....
I get the following errors:
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@RuleId".
Msg 137, Level 15, State 2, Line 20
Must declare the scalar variable "@RuleId".
Msg 137, Level 15, State 2, Line 32
Must declare the scalar variable "@RuleUnitType".
Msg 137, Level 15, State 2, Line 55
Must declare the scalar variable "@RuleId".
Msg 137, Level 15, State 2, Line 80
Must declare the scalar variable "@RuleId".
Msg 137, Level 15, State 2, Line 87
Must declare the scalar variable "@RuleId".
I wondered why it worked correctly for @Col2Use and not the SP parameters.
I thought the difference was that @Col2Use was a local variable....so I created "local" copies of the variables above -- but that did not work....
Here is the full set of code....
Tony
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 = @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 (@SQLCmd);
June 6, 2011 at 7:13 am
Found it ---
We need to do string manipulation to build the SQL....
Preformance actually got worse --- 47 seconds.
Going to try reversing the index order and then will post SQL plan of the best preformning solution....
TF
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);
June 6, 2011 at 7:45 am
tfeuz (6/6/2011)
Found it ---We need to do string manipulation to build the SQL....
Good. As soon as I saw the error message, I started doing the head-slap thing.
For query plan re-use, I'd suggest leaving it the way it was, and passing those parameters in:
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
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 7:55 am
If you add a ColToUse column to the RB_Rule table, you can change this:
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;
to this:
SELECT @Col2Use = ColToUse
FROM dbo.RB_Rule
WHERE RB_RuleId = @RuleId;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 8:17 am
Wayne,
So I have done some more testing and this is what I found....
The more recent versions of the stored procedures seem to run in the 30-45 second range for all the data.
Here is the most recent SP:
The execution plan is attached -- it is the one called "slow"
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 went back a few versions and this one runs consistenly in 16 seconds..
It has the plan marked "fast"
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;
GO
I am thinking that we probably have 'maxed out at 16 seconds' (the most important statistic for our users').
Your thoughts???
The other interesting thing is that if I run this through the preformance analyzer and use the suggested indexes\statitics generated by SQL server it estimates an improvement of 52% but actually preformance worse. I had another thread with Lutz several months ago that bascially was "be careful of just accepting the suggested changes"
I think I need to go away and examine the finish product you have creatred, do some research and then get back with some questions about why you did things a certain way....
June 6, 2011 at 8:20 am
tfeuz, you have been going round-and-round on this problem for EIGHT days now! Forums are not very useful for this type of extremely complex problem resolution. I bet 4 hours (6 tops) of a qualified perf tuning professional remoted into your system and this sproc would have been fixed right up. They could have mentored you along the way on how to analyze and improve performance along the way too.
Not sure if anyone pointed it out or not but I have seen DTA do HORRIBLE things to databases (as in make them essentially non-functional) at the hands of unqualified users. PLEASE be careful with it! It took me over 100 manhours once to clean up the DTA mess one client created for themselves.
Best of luck getting this one finally working efficiently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 6, 2011 at 9:05 am
tfeuz (6/6/2011)
Wayne,So I have done some more testing and this is what I found....
The more recent versions of the stored procedures seem to run in the 30-45 second range for all the data.
I went back a few versions and this one runs consistenly in 16 seconds..
It has the plan marked "fast"
I am thinking that we probably have 'maxed out at 16 seconds' (the most important statistic for our users').
Your thoughts???
The other interesting thing is that if I run this through the preformance analyzer and use the suggested indexes\statitics generated by SQL server it estimates an improvement of 52% but actually preformance worse. I had another thread with Lutz several months ago that bascially was "be careful of just accepting the suggested changes"
I think I need to go away and examine the finish product you have creatred, do some research and then get back with some questions about why you did things a certain way....
Tony,
I think we can get it down into the single digits. Maybe even into the low range of that. I'd like to keep playing with this to try.
Like Kevin, Lutz and others have mentioned, don't just blindly accept what dta and/or "missing index suggestions" suggest.
Have you been looking at the execution plans being created with these changes? This last changes made a drastic impact on the invoice table... but also on the overall execution plan. We were looking at the Invoice table taking ~ 65% of the total query... it's now down to about 1%. This last change made a totally awesome difference. Was this using InvoiceDate or InvoiceKey as the leading column of the index, and did you try both ways?
It's now showing the biggest chunk being InvoiceLineItem table. What indexes are on this table? (All I'm showing is the PK on InvoiceLineKey...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2011 at 9:29 am
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?
Viewing 15 posts - 61 through 75 (of 117 total)
You must be logged in to reply to this topic. Login to reply