1

I need to insert 1.3 million of records from one table into another, and it takes really long time (over 13 min). After some research I found that it is better to do this operation in batches, so I put together something like this (actual query is more complicated, it is simplified here for briefness):

DECLARE @key INT; SET @key = 0; 
CREATE TABLE #CURRENT_KEYS(KEY INT)

WHILE 1=1
BEGIN
   -- Getting subset of keys
   INSERT INTO #CURRENT_KEYS(KEY)   
   SELECT TOP 100000 KEY FROM #ALL_KEYS WHERE KEY > @key
   IF @@ROWCOUNT = 0 BREAK

   -- Main Insert
   INSERT INTO #RESULT(KEY, VALUE)
   SELECT MAIN_TABLE.KEY, MAIN_TABLE.VALUE 
   FROM MAIN_TABLE INNER_JOIN #CURRENT_KEYS 
   ON MAIN_TABLE.KEY = #CURRENT_KEYS.KEY

   SELECT @key = MAX(KEY ) FROM #CURRENT_KEYS

   TRUNCATE TABLE #CURRENT_KEYS
END

I already have indexed list of 1.3 million keys in #ALL_KEYS table so idea here is in a loop create smaller subset of keys for the JOIN and INSERT. The above loop executes 13 times (1,300,000 records / 100,000 records in a batch). If I put a break after just one iterations - execution time is 9 seconds. I assumed total execution time would be 9*13 seconds, but it's the same 13 minutes!

Any idea why?

NOTE: Instead of temp table #CURRENT_KEYS, I tried to use CTE, but with the same result.

UPDATE Some wait stats.

I am showing for this process PAGEIOLATCH_SH and sometimes PREEMPTIVE_OS_WRITEFILEGATHER in wait stats occasionally over 500ms, but often < 100Ms. Also SP_WHO shows user as suspended for the duration of the query.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • 3
    Did you measure any wait stats and see what is taking 13 minutes? Could be autogrow of log, tempdb data file, blocking, who knows? – Aaron Bertrand Jun 27 '13 at 19:23
  • @AaronBertrand Not really sure how to do it, I am not a DBA and have little experience in optimization – Yuriy Galanter Jun 27 '13 at 19:28
  • You [may find this a useful read](http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes). Or someone you hire who does have experience in optimization. :-) Did you try using transactions inside the loop? – Aaron Bertrand Jun 27 '13 at 19:30
  • Thanks I will look into the article. I tried surround the inner insert by transaction as well as entire loop - had no effect. – Yuriy Galanter Jun 27 '13 at 19:36
  • Question: Do inserts into temp table getting logged as well? – Yuriy Galanter Jun 27 '13 at 19:37
  • @AaronBertrand I did some wait stats research and updated the post. Is it useful? How can I act on this find? Thanks! – Yuriy Galanter Jun 27 '13 at 20:21

1 Answers1

3

I'm pretty sure you're experiencing disk pressure. PREEMPTIVE_OS_WRITEFILEGATHER is an autogrowth event (database getting larger), and PAGEIOLATCH_SH means that the process is waiting for a latch on a buffer that's an IO request (probably your file growth event).

http://blog.sqlauthority.com/2011/02/19/sql-server-preemptive-and-non-preemptive-wait-type-day-19-of-28/

http://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28/

What I would recommend is pre-growing both tempdb (for your temp table) and the database that's going to hold the batch insert.

http://support.microsoft.com/kb/2091024

Katherine Villyard
  • 196
  • 6
  • Results is inserted into temp table as well, do I still need to pre-grow main DB? Also what's the recommended size of temp db? Originally it was 200Mb I increased it to 500Mb, but see no noticable results. – Yuriy Galanter Jun 27 '13 at 21:39
  • If the main DB is experiencing autogrowth events, then yes. The recommended size of tempdb is "it depends," alas. I benchmark mine under typical load and set it to a reasonable number for my server load at startup. (Tempdb grows under load, but reverts to its original specified size at start up.) This article on optimizing tempdb might help. http://msdn.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx – Katherine Villyard Jun 27 '13 at 21:48
  • Unfortunately that didn't help. I doubled in size both TempDB an main DB and this didn't affect the speed. Most of the times `PAGEIOLATCH_SH` wait is very small 20-50ms could it still have such a huge effect on delay or is it something else? – Yuriy Galanter Jun 28 '13 at 12:53
  • Are the files still growing? Is it possible that the inserts are overlapping in a way that causes them to block each other? – Katherine Villyard Jun 28 '13 at 17:50
  • As far as I can see, file sizes stay the same as the initial size – Yuriy Galanter Jun 28 '13 at 19:26