I've tried recreating your problem on my laptop but with not much success. The only thing I can think is that maybe the table Audit_Trail_Transaction is locked.

A couple of...

Just spotted one possible improvement

this sql

SELECT @Field_Position = 0;

WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )

could be changed to

SELECT @Field_Position = 0;

SELECT @Total_Number_Of_Fields...

Although your code is nice and generic and can be used by multiple tables with different columns, I think this is also the cause of your issues.

If I'm reading...

the Go on this line

ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO

needs to be on the following line

ALTER TABLE MyTable DROP CONSTRAINT PK_MyId

GO

I would also check your disk performance. I've seen two cases recently of very bad performance due to the virtual servers being connected to an overused/failing shared storage.

regards

David

Here is one reason why I think select * can be bad.

Lets say that you have two Tables as below which can be joined on ID = ID2

create table Table1...

Try to remove the cursor and do the insert within a single operation. Something along the lines of

with existing as

(

select CIFNo ,FacilityType

,max(convert(numeric, SeqNo)) as CurrSeqNo...

It's useful if you want to know which tables contain data, or contain a lot of rows. I've used the method in the past after a customer had...

I think answer 1 is correct as well!

David

Does using ILSS.SHIPMENT_ID like ABL.[ShipTranNo] + '%' make any difference?

David

Would it be possible for you to post the queries and the execution plans?

thank you for your replies and article links - I will take a look.

Regards

David

Do you need to a select *? Fewer columns might result in a different index being used.

I guess that running the query directly on the server would remove...

the method we use is to store the results of the initial search (limited to 500 rows) in a generic search results table. (Normally just the p.k. ) ...

My first step would be to run your query bit by bit to determine which bit(s) are taking too much time.

David