Inserting Multiple Records in SQL2000

I have a web app that c开发者_运维知识库urrently is inserting x (between 1 + 40) records into a table that contains about 5 fields, via a linq-2-sql-stored procedure in a loop.

Would it be better to manually write the SQL Inserts to say a string builder and run them against the database when the loops completed rather than 30 transactions? or should I just accept this is negligible for such a small number of inserts.

In general, if the database is well optimized (indexed properly and well maintained), the largest overhead you have is in connection creation.

It will be better to create one big query and initiate one connection than it is to create 30 small queries and initiate 30 connections.

If you look to the future, you may end up with a loop of hundreds or thousands. You may also have many more users calling this function, so you may as well think about the scaling now.

To summarise: Create the query and run it once after you finish looping.