CODEWITHSUNDEEP
sqlsql-serverdatabase
victorf
victorf

Reputation: 1048

Which INSERT is faster: with VALUES or with SELECT/UNION ALL?

Which INSERT statement with multiple rows is faster in SQL Server?

This?

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy' UNION ALL
SELECT 124, 'Jonny' UNION ALL
...
SELECT 925, 'Sally'

Or this?

INSERT INTO dbo.MyTable (ID, Name) VALUES
(123, 'Timmy'),
(124, 'Jonny'),
...
(925, 'Sally')

I tried to record 18704 rows using one INSERT for each 10 rows. Times are in (mm:ss)

It was unexpected, because I hoped the SELECT/UNION ALL solution would be slower as it was more explicit operation (SELECT and UNION ALL).

Notably this performance is not good as it could be and surely the bottleneck is another thing, but I'd like to know if there is a significant difference between this two forms.

Upvotes: 1

Views: 1275

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

The performance difference is going to very minor. You should not be worrying about such things.

It is possibly faster to bulk insert the data.

If your insert is taking that long, then you have other problems, most likely:

  • Contention on the table (the table is locked by other processes).
  • Triggers on the table.
  • A lot of index overhead.
  • Check constraints using complex user-defined functions.

You might just have lots of contention on the table from other processes.

You might want to split the insert into smaller chunks. This will incur less contention.

Upvotes: 1

Related Questions