Merege statement giving performance issue

• ravi@sql

  SSCrazy

  Points: 2121

  More actions

  March 1, 2016 at 12:39 am

  #311737

  Hi Champs,

  I have a Merge statement which has both insert and update .

  But source table is quite huge it is having around 70 million records or 2 to 3 GB data .

  Destination table is empty .

  I have used two cte for calculation purpose from source table.

  Then using cte i have written merge statement .

  This is taking 4-5 hour how can i improve this performance ?

  Regards,

  Ravi

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 1, 2016 at 4:31 am

  #1862048

  ravi@sql (3/1/2016)

  ---

  Hi Champs,

  I have a Merge statement which has both insert and update .

  But source table is quite huge it is having around 70 million records or 2 to 3 GB data .

  Destination table is empty .

  I have used two cte for calculation purpose from source table.

  Then using cte i have written merge statement .

  This is taking 4-5 hour how can i improve this performance ?

  Regards,

  Ravi

  The flippant answer would be to change it to a plain INSERT. There won't be any updates if the destination table is empty :hehe:

  Can you provide an actual execution plan for the MERGE statement? Also helpful would be CREATE TABLE statements for the source and target tables, and a script to add a bunch of rows to the source table.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  March 1, 2016 at 7:18 am

  #1862093

  ravi@sql (3/1/2016)

  ---

  Hi Champs,

  I have a Merge statement which has both insert and update .

  But source table is quite huge it is having around 70 million records or 2 to 3 GB data .

  Destination table is empty .

  I have used two cte for calculation purpose from source table.

  Then using cte i have written merge statement .

  This is taking 4-5 hour how can i improve this performance ?

  Regards,

  Ravi

  There are hundreds of things that could be to blame here. You provided no table structures (with indexing), no code, no query plan. Help us help you.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• xsevensinzx

  One Orange Chip

  Points: 25560

  More actions

  March 1, 2016 at 7:45 am

  #1862102

  TheSQLGuru (3/1/2016)

  ---

  ravi@sql (3/1/2016)

  ---

  Hi Champs,

  I have a Merge statement which has both insert and update .

  But source table is quite huge it is having around 70 million records or 2 to 3 GB data .

  Destination table is empty .

  I have used two cte for calculation purpose from source table.

  Then using cte i have written merge statement .

  This is taking 4-5 hour how can i improve this performance ?

  Regards,

  Ravi

  There are hundreds of things that could be to blame here. You provided no table structures (with indexing), no code, no query plan. Help us help you.

  This.

  I personally have a MERGE statement with a CTE that merges around 1 to 10 million records onto a table over 100 million records without issues. The table structure, indexing, code and query plan go a long way in understanding the problem.

  I'm sure you likely stumbled upon this already, but just in case you haven't, here is the technet article on optimizing best practices for merges.

  Optimizing MERGE