search

dnlnln / generate-sql-merge

Generate SQL MERGE statements with Table data
MIT License
337 stars 151 forks source link

Improve performance of large MERGE with VALUES statements #101

Closed dnlnln closed 1 year ago

dnlnln commented 1 year ago

Enhance the @max_rows_per_batch parameter (introduced in #94 by @EitanBlumin ) to add batch separators (ie. GO keywords) between the individual MERGE statements.

In addition to resolving #75, I have seen a 100% performance improvement from my testing on a table with 40k records. 

EXEC [WideWorldImporters]..[sp_generate_merge] 
  @schema = 'Application', 
  @table_name='Cities', 
  @max_rows_per_batch = 5000,
  @delete_if_not_matched = 0

Tested on the WideWorldImporters.Application.Cities table which took 4 minutes to execute without GOs and only 2 minutes with GOs.

Before-and-after:

The below was generated before and after this PR on the AdventureWorks.Person.AddressType table (@max_rows_per_batch=3): image

Note: To preserve the previous behaviour of NOT including the GO keyword between merge batches, specify the following param: @batch_separator=NULL