Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

how to loop through SQL inserts?

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 single connection open for longer, while option 2 requires multiple trips to the connection pool.

Any thoughts?

PopCulture
Friday, October 08, 2004

i would go with the first option.

grover
Friday, October 08, 2004

I'd go for option 1 or option 2, depending on the situation

Joe
Friday, October 08, 2004

I've done option 2, and it runs pretty darn fast. I think it's because the connection is cached(?) correct me if I'm wrong.

SongSing Writer
Friday, October 08, 2004

I just finished writing an import utility that inserted thousands of rows using option #2.  It runs great and within an acceptable amount of time considering the processing it has to do.

The only thing you'll want to watch for if you go the #2 route is that you're using .Close() and .Dispose() on each connection after you're done with your insert.  If you don't you're liable to experience a timeout because all the connections in your db connection pool will be used up.  Also, in your connection string, you can specify:

Pooling=true;Max Pool Size=500;Min Pool Size=50

...to ensure you have enough connections available.

Good luck.

smallbiz
Saturday, October 09, 2004

Using SQL Server, I've also accomplished the same thing by sending in the data to be inserted as XML. You can send in multiple rows that way and just select from it as if it were a table. You can insert multiple rows without having to loop through anything. For example, from Books Online:

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @empdata
INSERT INTO Employee
SELECT *
FROM OPENXML(@hDoc, '/Employee')
        WITH Employee
EXEC sp_xml_removedocument @hDoc

Ty
Sunday, October 10, 2004

I second the XML idea. It works great in SQL Server 2000 and is very flexible. You can use XPath syntax to strip out whichever bits you want from the XML.

The only slight issue is a known bug in sp_xml_removedocument which returns 1, rather than 0. If, like me, you check return codes it'll look like you've got an error condition, but in fact it has worked.

Nemesis
Monday, October 11, 2004

"I just finished writing an import utility that inserted thousands of rows using option #2"

Hm. What's the break-even point when you just dump the data to a text document and use BCP?

Philo

Philo
Friday, October 15, 2004

Option 2 definitely.

In addition, if you open an explicit DB transaction around the inserts, the server can avoid the auto-commit after each insert.

Jake
Sunday, October 24, 2004

*  Recent Topics

*  Fog Creek Home