Really...

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

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

I forgot to answer this. No, there isn't a performance issue...

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

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

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

Lynn Pettis (6/3/2011)

---

Had a phone interview Thursday morning with Microsoft. It seemed to go pretty good. We'll know how good if the fly me up to Redmond for...

You're welcome.

I am just in the process of reloading my test data to get the same volume (100,000...

... and, Part 2...

CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)

AS

WITH cteMain AS

(

SELECT a.InvoiceKey,

b.InvoiceDate,

...

First of all, change those stored procedure names. Do NOT prefix them with sp_ - this makes SQL look in the master database first.

Okay, here is step 1. We're going...

Does anyone have any experience with SQL XDetails[/url]? I saw a link on the site here, and am wondering what others think. Also... the link implies a free version, but...

SQLRNNR (6/2/2011)

---

LutzM (6/2/2011)

---

To quote from a thread I've been involved:

Also, I will start to participate on the site more often. It has always been useful and seems to be very...

Excellent... this will help tremendously.

I've noticed two things in the sp_CalculateRuleInvoiceUnits procedure:

1. These variables are no longer used - probably as a result of eliminating cursors:

-- variables not currently being...

Hi GSquared,

I was going to suggest a minor change to your code to: (change the varchar(100) to datetime)

DECLARE @XML XML = '<rule> <date>1/7/2005 10:12:43 AM</date> <properties> <RuleName>TrainingEnrollmentSave</RuleName> <DebugLevel>3</DebugLevel> <DBName>BNA</DBName> <DBServerName>sa3sql03bv.sa.ito.ceridian.com</DBServerName>...