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')
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:
Do it too if you don't believe it!
DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 50000 -- 50K
BEGIN
EXEC('INSERT INTO MOV (id) VALUES (1),(2)')
SET @i = @i + 1
END
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:
The total running time? 10 seconds!
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
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