Why are SQL server inserts so slow?

sql, sql-server-2005

Solution

You perform each insert inside its own transaction.

Beginning and committing transaction is very expensive in `SQL Server`.

Enclose everything into a single transaction block:

declare @i int
set @i = 0
set nocount on
BEGIN TRANSACTION
while @i < 2000
begin
insert into testdb(testcolumn)
values (1)
set @i = @i + 1
end
COMMIT

To generate sample data, you can use a recursive `CTE`:

WITH    q (num) AS
        (
        SELECT  1
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num < 2000
        )
INSERT
INTO    testdb(testcolumn)
SELECT  1
FROM    q
OPTION (MAXRECURSION 0)

, which will be faster.

Problem

I'm trying to insert rows of in-memory data into a table on SQL Server Express 2005. It is running what seems to me very slowly - about 5 seconds per 1000 rows inserted. I am just using a basic "INSERT INTO" command. The slowness does not depend on the table data - it is still slow with a table with one int column and no index. It is nothing to do with my software - it is just as slow running SQL in a loop from Management Studio. There is nothing else accessing the database at the same time. On a 3Ghz Xeon (old I know), this will take about 10 seconds to execute: ``` declare @i int set @i = 0 set nocount on while @i < 2000 begin insert into testdb(testcolumn) values (1) set @i = @i + 1 end ``` Is there a better way to insert bulk in-memory data than looping on INSERT? Or some configuration I should change in SQL Server?

Original source