3

SQL: Fastest way to insert new records where one doesn’t already exist

Fastest way to insert new records where one doesn’t already exist

SQL Developers come across this scenario quite often – having to insert records into a table where a record doesn’t already exist.

The age-old technique and I suspect most common practice is doing a left join where the values are null from the table being inserted into. Even though this is simple, is it the fastest way?

That’s when this curious consultant started wondering what’s the fastest way to insert new records where one doesn’t already exist during a single process?

 

The Different Techniques

Other than the LEFT JOIN, what are other ways to accomplish this? Here are a few common ways that will be benchmarked:

  1. Insert Where Not Exists
  2. MERGE
  3. Insert Except
  4. LEFT JOIN

 

Setting Things Up for the Speed Test

For testing purposes, the SQL code was executed on a Windows 2012 Server with 128GB memory, 16 core CPU rated at 2.54 Ghz, using Microsoft SQL Server 2014.

 

To ensure SQL Server didn’t keep any queries (or anything for that matter) cached, the following code was run before each test:

Two tables are created, and populated with up to 50 million rows. The primary keys are the same:

Here are the full code snippets for each technique:

#

Code
1) Insert Where Not Exists
2) Merge
3) Insert Except
4) Left Join

 

The Results Are In!

Here are the results, in milli-seconds, as to how long each technique took to execute.

The winner is highlighted in green.

Technique

Time to execute, in milliseconds, over 3 runs:

# Records:

50,000

500,000

5,000,000

50,000,000

500,000,000

1: Insert Where Not Exists

166, 166, 163

1006, 990, 1066

16183, 16386, 16280

168896, 169770, 171383

355310, 348843, 348780

2: Merge

243, 233, 233

2426, 2460, 2473

24813, 24610, 25000

255043, 255043, 255213

463486, 513423, 504380

3: Insert Except

173, 170, 173

850, 826, 870

16220, 16140, 16160

104223, 106333, 110643

221620, 221946, 222553

4: Left Join

140, 136, 140

716, 726, 760

15436, 15406, 15436

167053, 160610, 169553

344873, 315123, 342326

 

The Way To Go

Who else thought the LEFT JOIN would be the dominant player up through 500,000,000 records and beyond?

I never thought the LEFT JOIN would slow down as much as it did when hitting 50,000,000 records and more.

While it seems to dominate for less than 50,000,000, once that threshold is hit the INSERT EXCEPT option proved to be king.

I’ve left you the SQL code below, so feel free to use it as a basis for conducting your own performance benchmarks.

Leave a comment below and share the knowledge if you have any suggestions or other ways of doing this!

The SQL Source Code Used

 

  • Konstantinos

    Even after 2 years this is really helpful. Thank you David.

  • todd

    It would be interesting to see how these results compare to the “JFDI” approach described here http://stackoverflow.com/a/3408196/62600

    • Johnny Boy

      Whelp, the author posted the source code. Feel free to try it out and let us know. 🙂