3

I've just written something to insert 10000 rows into a table for the purposes of load testing.

The data in each of the rows is the same and uninteresting.

I did it like this:

DECLARE @i int = 0
WHILE @i < 10000 BEGIN
    exec blah.CreateBasicRow ;
    SET @i = @i + 1
END

All create basic row does is fill out the not null columns with something valid.

It turns out this is very slow and it even seems to hang occasionally! What are my alternatives? Would it be better to write something to generate a long file with all the data repeated with fewer insert clauses? Are there any other options?

Update

A constraint is that this needs to be in a form that sqlcmd can deal with - our database versioning process produces sql files to be run by sqlcmd. So I could generate a patch file with the data in a different form but I couldn't use a different tool to insert the data.

JonnyRaa
  • 7,559
  • 6
  • 45
  • 49
  • possible duplicate of [What's the fastest way to bulk insert a lot of data in SQL Server (C# client)](http://stackoverflow.com/questions/24200/whats-the-fastest-way-to-bulk-insert-a-lot-of-data-in-sql-server-c-client) – Krishnraj Rana Jul 23 '14 at 09:40
  • 1
    @KrishnrajRana I'm not convinced it is. That guy is trying to do it from C#. I'll update my question... – JonnyRaa Jul 23 '14 at 10:48

1 Answers1

2

You can speed this exact code up by wrapping a transaction around the loop. That way SQL Server does not have to harden the log to disk on each iteration (possibly multiple times depending on how often you issue a DML statement in that proc).

That said, the fastest way to go is to insert all records at once. Something like

insert into Target
select someComputedColumns
from Numbers n
WHERE n.ID <= 10000

This should execute in <<1sec for typical cases. It breaks the encapsulation of using that procedure, though.

usr
  • 168,620
  • 35
  • 240
  • 369
  • just wrapping it in a transaction seems to have helped quite a bit. I'm not sure I follow your other suggestion - would Numbers actually need to contain the data or just something else? – JonnyRaa Jul 23 '14 at 13:10
  • I should have elaborated on that. Numbers is a typical numbers table containing at least 10k rows. That's just for convenience. The main point is to generate and insert everything in one batch so that the query optimizer can fully optimize this algorithm. – usr Jul 23 '14 at 13:18