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...

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...

Wayne,

Missed one: Yes I tried the index order both ways -- the invoicekey is currently first.

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...

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 (...

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...

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...

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,...

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

(

...

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...

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

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...

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)...

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?

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...