Fog Creek Software
Discussion Board




comma delimited sproc?

in a situation where you have to loop through dozens of inserts to a database, would you rather:

1.) feed a stored procedure a comma delimited string of values  and loop through them in T/SQL

2.) invoke a stored procedure that does a single insert in a loop in your application code, feeding it one value at each pass?

Option 1 keeps a connection open for longer, but option 2 requires multiple trips to the connection pool.

Any thoughts?

PopCulture
Friday, October 08, 2004

I'm not sure why you say option 2 requires multiple trips to the connection pool.  If you hold onto the connection and call the proc multiple times with the same connection that is a non issue.  Sure there is more network overhead of calling the proc multiple times and a bit more server overhead but these are typically dwarfed by any time spent by the server actually doing work.  Generally I prefer not to do any string parsing (the commas) inside database code, it's just uglier and typically proper error handling in this situation ends up being more complex.

So option one is probably marginally faster but I vote for option two because it is cleaner.

Alex
Wednesday, October 20, 2004

*  Recent Topics

*  Fog Creek Home