Insert Into table take 1 hour

• ipan_nugraha

  Valued Member

  Points: 58

  More actions

  October 12, 2009 at 9:56 pm

  #215447

  Dear All

  i have a table with 4,000,000 rows and 6 column and 3 index and 1 primary key, when inserting about >1000 rows/data, why it take 1 hour ?

  i using sql server 2005, CPU Intel Xeon quad core 3GB RAM

  thank's for help

  Ipan

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  October 13, 2009 at 1:22 am

  #1065051

  Try to remove the cursor and do the insert within a single operation. Something along the lines of

  with existing as

  (

  select CIFNo ,FacilityType

  ,max(convert(numeric, SeqNo)) as CurrSeqNo --guard non-numeric SeqNo

  from Apr_CIFSeqData

  group by CIFNo ,FacilityType

  )

  insert into Aprova_App.dbo.BS_FacilityNumbering

  select CIFNo ,FacilityType, CurrSeqNo, ,right('00' + convert(varchar(2)CurrSeqNo)), 2)

  from existing

  where not exists ( select 'x'

  from Aprova_App.dbo.BS_FacilityNumbering BS_FacilityNumbering

  where BS_FacilityNumbering.CIFNo = existing.CIFNo

  and BS_FacilityNumbering.FacilityType = existing.FacilityType)