Union all or Multiple Inserts

• rosh-396756

  SSCrazy

  Points: 2338

  More actions

  May 7, 2008 at 12:37 am

  #190019

  Performance wise, which is better solution to insert multiple records in a table - Union all or Multiple Inserts

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  May 7, 2008 at 1:04 am

  #811598

  [font="Verdana"]Do you mean...

  Insert Into Test

  Select 1, 'A' Union All

  Select 2, 'B' Union All

  Select 3, 'C' Union All

  Select 4, 'D' Union All

  and

  Insert Into Test values (1, 'A')

  Insert Into Test values (2, 'B')

  Insert Into Test values (3, 'C')

  Insert Into Test values (4, 'D')

  then I guess Union All, but I don't know the technical reason behind it.

  Mahesh

  [/font]

  MH-09-AM-8694

• rosh-396756

  SSCrazy

  Points: 2338

  More actions

  May 7, 2008 at 1:17 am

  #811604

  Yes, I mean the same.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  May 7, 2008 at 3:04 am

  #811632

  Each insert is separate transaction.

  Which involves locks, reindexing, etc.

  1st option (union all) create single transaction.

  2nd option creates as many transactions as many rows being inserted.

  _____________
  Code for TallyGenerator

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  May 8, 2008 at 7:24 am

  #812405

  Union all is a clear winner..

  If you do a UNION ALL you will get all the rows and take less resources as there is no need to sort out the DISTINCT rows or repeat the insert for each row.

  When I tried it 3 separate ways (multiple inserts, insert with mult select/Union and insert with mult Select/Union All), Union All took the least time (Constant scan + Concat), Union took about another 30% or so (had to do Merges (for distinct) instead of Concatenation ) and multiple INSERTs was the most costly by far (each Insert was about the same cost as the single UNION ALL insert so the total is orders of Magnitude more).

  /* Multiple Inserts */

  /* Multiple Insert Statements */

  DECLARE @Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

  SET DATEFORMAT DMY

  INSERT @Sample

  SELECT 'inv1', 'acc1', '01/01/2007'

  Insert @sample

  SELECT 'inv2', 'acc1', '01/02/2007'

  Insert @sample

  SELECT 'inv3', 'acc1', '01/03/2007'

  Insert @sample

  SELECT 'inv4', 'acc1', '01/04/2008'

  Insert @sample

  SELECT 'inv5', 'acc2', '11/05/2007'

  Insert @sample

  SELECT 'inv6', 'acc2', '12/06/2007'

  Insert @sample

  SELECT 'inv7', 'acc2', '01/07/2008'

  Insert @sample

  SELECT 'inv8', 'acc3', '13/08/2007'

  /* SINGLE INSERT WITH UNION */

  /* SINGLE INSERT WITH UNION *?

  DECLARE @Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

  SET DATEFORMAT DMY

  INSERT @Sample

  SELECT 'inv1', 'acc1', '01/01/2007' UNION

  SELECT 'inv2', 'acc1', '01/02/2007' UNION

  SELECT 'inv3', 'acc1', '01/03/2007' UNION

  SELECT 'inv4', 'acc1', '01/04/2008' UNION

  SELECT 'inv5', 'acc2', '11/05/2007' UNION

  SELECT 'inv6', 'acc2', '12/06/2007' UNION

  SELECT 'inv7', 'acc2', '01/07/2008' UNION

  SELECT 'inv8', 'acc3', '13/08/2007'

  /* SINGLE INSERT WITH UNION ALL */

  /* SINGLE INSERT WITH UNION ALL */

  DECLARE @Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

  SET DATEFORMAT DMY

  INSERT @Sample

  SELECT 'inv1', 'acc1', '01/01/2007' UNION ALL

  SELECT 'inv2', 'acc1', '01/02/2007' UNION ALL

  SELECT 'inv3', 'acc1', '01/03/2007' UNION ALL

  SELECT 'inv4', 'acc1', '01/04/2008' UNION ALL

  SELECT 'inv5', 'acc2', '11/05/2007' UNION ALL

  SELECT 'inv6', 'acc2', '12/06/2007' UNION ALL

  SELECT 'inv7', 'acc2', '01/07/2008' UNION ALL

  SELECT 'inv8', 'acc3', '13/08/2007'

  Toni

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 8, 2008 at 7:35 am

  #812410

  But be careful if your INSERT..SELECT..UNION ALL... statement is very big or your transaction log is very small.

  John