IMNSHO, option 2 will be the best performer over all.  Refinements include only sending enough Order Header info to the initial grid to allow the user to select the order...

Without seeing any code, table scripts, sample data and table row counts we really can't EFFECTIVELY help you.

Given that disclaimer, if I were you I would rework the entire sproc...

1) Just try using SQLNCLI as the provider, instead of msdasql.  Yeah, I know it SHOULDN'T work, and PROBABLY won't work, but I have had wierder things than that solve...

1) Open BOL (SQL 2005 Books Online) and paste link in bar and hit enter

2) I don't believe there is 64 bit SSMS. 

3) Did you try using SQL Native...

1) Did you make your DSN a system one or user? See here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fc1e0ca2-457d-45f6-a5a4-38793eb7511c.htm

2) This, and the links from it seem to imply to me that there is...

1) Search these forum archives, as well as the web and the msdn sql forums.  There is a wealth of information on performance analysis and tuning.

2) If it is really...

Can you download and install the 32 bit ODBC drivers (MDAC 2.8+??) on your 64 bit server?  Not sure if this is viable or not.

Do you have any inter-database dependencies in your objects?  If so, this could be a problem as sql server checks other dbs as appropriate.

are you explicitly interacting with your objects...

Good of you to remember our last discourse Jeff!!  HEHEHE

Actually, one of the other things I am an expert at is EDI (specifically ANSI X12 healthcare datasets).  That mechanism is INCREDIBLY compact, efficient, flexible and well documented.  Systems have...

I think you are missing the point and focusing on the wrong thing - language barrier I suspect.  I was just saying that cursors are slow and set-based logic is...

Say you have a set of code that takes something takes 1000 seconds to run.  A 1 order of magnitude improvement means it would then take 100 seconds to run.  2 orders...

Without seeing anything I can pretty confidently state that it is either old statistics (and you are asking for data at the 'tail' of the set) or more likely you...

Eric, nice post.

1) I agree in theory that ability to pass a table variable would add value to a lot of processes.

2) I bet a lot more table variables get...

Do you have an over-arching BEGIN TRAN before you do the first insert?  If so, and as I understand it, a rollback will roll back EVERYTHING that occurred up to...