SQL Server, other ideas for a fast insert ...multivalues!

Hi Guys,

Welcome back!

 

After yesterday's post, here is another one today, always light! Another trick for faster inserts!

Enjoy the reading!

Multivalues

The T-SQL syntax allows the specification of more than one set of values ​​in the INSERT statement 

For example:

INSERT INTO Table (code,descr) 
VALUES ('0001','first item'), ('0002','second item'), ('0003','third item') 

We can specify a maximum of 1000 set of values.

Let's see what we can do with it ... In some cases we could use this possibility to gain speed.

The test

Come on! follow me!

 

Let's create a simple heap table and insert some data, let's say 100,000 rows

     
CREATE TABLE MOV (id int, Qty float)

Now i use this loop to fill the table:

       
DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 100000  -- 100K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1)')
  SET @i = @i + 1
END

Now the total running time of these 100,000 inserts is ... 46 seconds

Now let's try to make half of the insertions by inserting two lines at a time.

To do this we modify the script as follows:

       
DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 50000  -- 50K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2)')
  SET @i = @i + 1
END

Do it too if you don't believe it!

The time I got is 23 seconds.
We have halved the time!

So why not go on, mates?

 

We now perform 20 thousand inserts with 5 values:

       
DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 20000  -- 10K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2),(3),(4),(5)')
  SET @i = @i + 1
END

The total running time? 10 seconds!

Other results:

10.000 inserts x 10 values = 6 seconds

5.000 inserts x 20 values = 3 seconds

2.000 inserts x 50 values = 2 seconds

1.000 inserts x 100 values = 1 second

 

finally

100 inserts x 1000 values = 0.5 second.

 

This is the graph:

 

 

Wow! not bad!!! 

- knowledge sometimes makes the difference -

That's all for today

Stay tuned for the next post!

...and don't forget to follow me on linked by clicking on follow me!

 

 

Next post:

Previous post: SQL Server, How to do a fast massive insert