I really wish SQL Server folks had some kind of optimization there: this method is so simple, it is very tempting to use.It does not really matter for performance whether the INSERT command has parameters or not, but we do want to use parameters for better security and correctness: For commands not specifically enrolled in a transaction, SQL Server will open an implicit transaction. In fact, if we wrap the insertion loop in a transaction, this improves performance by about 20 times!We won't get in each other's way and we won't insult the other girls.I need some kind of proof, some kind of verifiable, empirical data.In this article I post results of my investigations, so my efforts are not wasted. I was inserting 1,000 records into a simple 3-column table without indexes.
Meet single women and men who know their chardonnays and cabernet sauvignons from their viogniers and zinfandels.
But it is still not nearly as fast as Every time you insert a record into a table, its indexes are updated and triggers are executed. Whether this overhead is significant depends on the number of indexes and complexity of the triggers.
In my benchmark the table did not have any indexes or triggers on it.
The client was a Windows XP SP3 machine with 3.0 GHz Pentium 4 and 3GB of RAM, connected to the server via a wired 100MBit Ethernet connection.
I ran the program both on the remote client described above, and locally on the database server machine.