Why is SQL Server insertion so slow?

Question

I am trying to insert rows of data in memory into a table on SQL Server Express 2005. It works, which seems very slow to me - about 5 seconds per 1000 rows inserted. I just use the basic "INSERT INTO" command. Slowness is independent of table data - it is still slow with a table with one int column and index. This has nothing to do with my software - it is also slow to start SQL in a loop from Management Studio. At the same time, there is no access to the database. On 3Ghz Xeon (the old one that I know) it will take about 10 seconds:

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 data into memory than an INSERT loop? Or should I change some configuration in SQL Server?

Answer 1

.

SQL Server.

:

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

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)

.

Answer 2

1) Flush . , , . INSERT . :

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
  if (@i % 1000 = 0)
  begin
   commit;
   begin transaction;
  end
end
commit

2) . . Disk sec/Transfer .
3) ( ). , "" .
4) ( , )

, . , " SQL Server 2005" , , .

Answer 3

/ .

• , .. , ...
• Data Warehousing/ETL ( ). . / .
• . n 1000 .
• . , . db, , 10 000 / SAN, . ! .
• Sql Server, . , % / ; ; .

Answer 4

, , - 2000 , :

INSERT testdb(testcolumn)
SELECT 1
FROM master..spt_values
WHERE number BETWEEN 1 AND 2000

Answer 5

:

• . , , , ( 40- )
• // ..
• . - .
• . , .

(Ultra-Fast ASP.NET), .

Answer 6

( ) , , . 1000 , ( , SQL Server 2008 xml- 2005 ).

Answer 7

google "SQL Server Tuning"... . , , , , , , ( SQL Server ), RAID-, . / (OLTP) ( ). , , .

.

, .

I would consider prepared statements and transactions as a way to start optimizing. Then look at indexing (if this is a set of inserts that don't happen very often, I would consider dumping indexes, importing, and creating indexes).