insert 4,000,000 rows in minimum second

• Gaurang-Patel

  SSCrazy

  Points: 2109

  More actions

  May 19, 2011 at 7:09 am

  #1327037

  HowardW (5/19/2011)

  ---

  Gaurang-Patel (5/19/2011)

  ---

  colin.Leversuch-Roberts,

  i know that select into is much quicker than insert into.and i had doubt that

  it is unlikely that i am going to get all records inserted in 5 sec(appending table)

  while it performs Best Select into finishes in 2 sec and insert into finishes in 9 sec,

  but we have structure we can not use select into ,we have to use insert into.

  Thanks.

  If SELECT INTO takes 2 secs (you really need to have an objective measure over several runs rather than referring to the fastest possible time) then the TABLOCK hint on the insert table should be very similar.

  Select into takes 2 secs most of the time ,with tablock hint it takes 3 secs most of time.

  http://www.sqlstairways.com

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  May 19, 2011 at 7:09 am

  #1327038

  Gaurang-Patel (5/19/2011)

  ---

  HowardW fortunately it helps most of time but not everytime but it is good.

  Select into: 2 sec.

  insert into: 9 sec (without Tablock).

  insert into: 3 sec (with Tablock).

  Thanks All.

  That sounds like a pretty good result to me and about as far as you can optimise it without scaling up the hardware - as I said, you need to measure this objectively by:

  1) taking the average over several runs

  2) SET STATISTICS TIME ON then use the SQL Server Execution Time (CPU Time) from here to measure the performance

  It's likely that you have all sorts of other things going on on the server and it has to wait for resources/locks - when you're looking at relatively small time periods, this makes a big difference

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  May 20, 2011 at 7:02 am

  #1327649

  No one has mentioned doing a file IO stall and wait stats analysis while the process is running. You need to do that to find out exactly what the bottleneck is so you can (possibly) address it. I agree with others that your hardware may prevent you getting that 33% improvement you seek (i.e. 3 seconds down to 2 seconds), but there are quite a few things that can be tweaked to address various waits stats or IO stall issues that could be addressed without requiring new hardware. You will almost certainly need to get a professional tuner on board if you want that extra second though. WAY too many variables come into play to solve that over a forum thread.

  I am VERY curious about what you have going on at a scale that requires your SLA though. Can you send me a private message about it?

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

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  May 20, 2011 at 8:08 am

  #1327702

  generally I always considered that the speed of the t-log was key - no transaction is complete until it's been logged, not that I've even been able to improve t log speed in my limited tests. My understanding is that your insert will be complete once you've had the "ok" from the t-log ( so to speak ) depending upon your storage of course, but enabling 100% write cache on the log drive would make that part the fastest then. Repeated tests will probably leave the source data in cache - do you clear cache between tests?

  The other point is that in isolation you may get one performance but when the system is running that's another matter.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/