All;

I have uploaded the requested query plans.

A few notes:

- the database in the case is the same database - this is contrary to the example I gave in my first...

All;

Thank you for all your responses. As most of you alluded to, I went down the path of checking out the query plans and the indexes.

After ensuring that both...

That is what I thought -- is there a good chance this will resolve the deadlock?

Tony

WayneS (6/8/2011)

---

Just making sure you're looking at the code as a sanity check! :w00t:

Does this...

WayneS (6/8/2011)

---

Tony,

Starting over. The main (not single) procedure is totally rewritten to combine the TVF with it. If this works, we'll combine the TVF into the single procedure also.

Start off...

RB_InvoicesToProcess 1:1 with Invoices but you have...

WayneS (6/7/2011)

---

tfeuz (6/7/2011)

---

Wayne,

I had to make a fix to the code -- you did not constrain by the rule id when you create the temp table -- it caused duplicate...

Wayne,

I had to make a fix to the code -- you did not constrain by the rule id when you create the temp table -- it caused duplicate values...

Note the...

I'm trying to redo the iTVF, and I have a few questions:

1. For any Invoice.InvoiceId value, will there be one and only one row in InvoiceTotal for that InvoiceId?

** Correct...

Wayne,

Here you go...this has the latest "isnull" change..sqlpplan is attached..

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server...

Wayne,

Check out my last post --- the "main" proc is slightly different as it has a case statement in the insert to trap for null values...i found that just this...

Darn --

Here you go

Statistics:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU...

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

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,

...

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