Recommended Tests

Okay...so you have a choice between one of the two following scenarios.  Which one do you choose and why?

Background - The aplication is based on IIS and SQL server.  Your app must make use of temp tables to handle some complex sorting and transformation tasks.  You'll pull data from permanent tables and use the temp tables to do the work. Some of the Selected data from the permanent tables will simply be copied "as is" to the temp table.  Fairly straightforward.  Eventually you'll encounter a row that matches the criteria you have to trigger an advanced transformation routine.  Once that row is processed, you'll resume copying all remaining rows into the temp table.

Do you:

1. Continuously leave the db connect open that's handling the copy work, as another connect handles the inevitable transformation that will happen, so when transforming is done, you're in position to resume copying right where you left off?

-OR-

2. Close the copy db connect at transformation time, then re-open it after the transformation routine to resume copying where you left off?  Keep in mind this may require you to do several MoveNext cursors to get back to where you need to be, or if using mySQL you may use LIMIT x,y.

Which option would impose the least "drag" on your webserver? This scenario assumes the SQL database and IIS are installed on the same machine.

There are pros and cons to each method.  Which one would you employ, and why?