Jeff, Phil - thanks for confirming my hunch regarding the use of index hints.

Turns out that with some fiddling with indexes, I was able to achieve the same performance improvements...

I thought the cowboy column was simplistic at best. If the article is saying that companies cannot afford to have experts operating outside of the company procedures, that I...

Revision.  So, I found an invoice table with 3.5 million invoices and discovered the query was taking too long - 11 minutes as of when I killed it.

So, I switched to...

The query below works well enough and is nice and tight.  It takes about 20 seconds on a 250,000 row table on a 2 CPU/4 GB server.  That's OK...

Michael,

Thanks for the observation on the merits of upping the timeout delay.  I'll make that change forthwith setting it to something large.  In practically every case, the using application will...

Epilog:

I ended up using th sp_getapplock to force a serial execution of certain parts of my stored procedure.  It seems to help quite a lot although I also modified a...

Well, for the moment I am using the SP_GETAPPLOCK approach so that the stored procedure first has to obtain an exclusive session resource before proceeding with the processing.  I am...

Sergiy, Michael:

All of your answers have been really helpful.

Can you see any advantages of the creation of the global temporary table as opposed to:

EXEC @LastErrorCode = sp_getapplock @Resource    = 'HE_EXPLODE',...

Michael,

That's exactly what I was looking for.  Now, I will be using Session versus Transaction locks so I have a couple more questions.

What defines a session and what terminates a...

John,

Thanks for that suggestion.  Actually, right now, I am using "logical locking" by writing a record to a table.  The problem is that if the stored procedure fails badly, the...

Jeff,

I appreciate your mellow answers.  Anyway, I am starting to lean towards SPs as subprograms.  It saves me having to fish through a long script no matter how well structured.

As a former COBOL programmer, the problems of GOTO have been understood from the beginning.

But, what I found is that a careful use of GOTO is effective.  In the main...

I am familiar with all that has been said in this thread.  I was hoping that somebody would tell me there was something akin to PERFORM paragraph-name in COBOL lurking...