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.

Note that results may vary by server because the optimization engine on every SQL Server Instance could do it differently. The SQL code is below, so feel free to use it as a basis for conducting your own performance benchmarks.

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