Sql Server Inserts timing out

I'm trying to insert multiple rows i开发者_如何学编程nto a simple table in Sql Server 2008. The table doesn't have any indexes other than the Primary Key.

I'm doing the inserts as follows

insert into TestTable 
(Col1,Col2, Col3,Col4, Col5)
select 1,1,1,1,GETDATE()
union all
select 1,2,3,1,GETDATE()
union all
select 1,5,2,1,GETDATE()
union all
select 1,4,15,1,GETDATE()

If I do this for a large number of rows ( say 400 ) and run the operation multiple times, the insert operation begins to take a huge amount of time.

Is there any way to further optimize the inserts?

---

insert into TestTable (Col1,Col2, Col3,Col4, Col5)
VALUES (1,1,1,1,GETDATE()), (1,1,1,1,GETDATE()), (1,1,1,1,GETDATE()), ...

---

make sure you commit rather frequently. this should reduce rollback segment usage.

---

You can try to chunk the data. Load the first 100 records, commit, then repeat. 400 records is not a lot. You can typically insert thousands of records at one time. All inserts are written to the log for rollback purposes. The log file is cleared when you commit.

---

It's probably compile time, not execution time.

If you have 400 rows exactly the same then generate 400 rows first

insert into TestTable 
   (Col1,Col2, Col3,Col4, Col5)
select
   1,1,1,1,GETDATE()
FROM
   (SELECT TOP 400 * FROM master.dbo.spt_values ORDER BY 1) foo