Tony,

Here's a new TVF to try out. It is no longer an inline TVF; it's now a multi-statement TVF. I'm trying to hit some of the larger tables only once,...

Tony,

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?

2. For...

... or this (it will only hit the #AllLocations table once, not twice. However, it will scan the entire table. Previous query will take advantage of indexes if available on...

Celebration time!

....... or did you take that sword thingy of yours in to work and do away with them all?

cgrammont (6/7/2011)

---

I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going through...

To answer your question... just clustering.

The other available options:

1. Shipping the transaction log: requires Bulk Logged for Full recovery models.

2. Mirroring: requires Full recovery model.

It's a lot easier to manage...

Whoops... quoted my post instead of editing it. It's now corrected...

Actually, none of those choices are correct. You get back 50 random GUID values.

1. NEWID() returns a random GUID (aka uniqueidentifier)

2. Select statement has no order by clause, so the...

Are you talking about the TotalGross,TotalNet, TotalUnits?

If so, try this instead:

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

AS

DECLARE @Col2Use sysname,

@SQLCmd ...

Jim,

If I've read things right, you're going to the Summit? Do you know that Thursdays up there is "Kilt Day"? Grant started it... he can fill you in on it!

Tony,

Okay, I don't think any additional indexes will help out... this looks to be about as efficient as possible as it is currently written.

I do see that the branch that...

FYI: Wrong procedure. Need the one that has the parametrized string, calling sp_executesql at the end.

tfeuz (6/6/2011)

---

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

Tony,

Can you add SET STATISTICS IO,TIME ON; to the test that you're running (not to the procedures), and send the results?

Please do it for "slow" and "InvoiceKeyFirst" methods.

tfeuz (6/6/2011)

---

Wayne,

Just so we are on the same page -- this is the current SP we are working with. The ones using string concatentation took at least 2-3...