Hello, there is a table tst_ex1 containing 2 fields: id int identity (0,1) , gui uniqueidentifier null . And the tst_ex2 table contains the gui2 uniqueidentifier null field. From the tst_ex1 table containing 3 million rows I want to insert a million rows into the tst_ex2 table, but the algorithm developed by me performs this operation extremely slowly (data insertion will take more than a week), since most of the time, there is a check of all 3 million lines for compliance with the where clause (code below):

use Test go declare @b int = 0; while @b < 1000000 begin insert into dbo.tst_ex2 (tst_ex2.gui2) select tst_ex1.gui from dbo.tst_ex1 where tst_ex1.id = @b; set @b += 1; end;

Help speed up the insertion process. I read about bcp , but in practice I did not understand how to use it.

  • 2
    insert into dbo.tst_ex2 (tst_ex2.gui2) select tst_ex1.gui from dbo.tst_ex1 where tst_ex1.id<100000 not tried? - Yura Ivanov
  • 1. tst_ex1 there any index on the tst_ex1 table, or is it an index-free heap? 2. Paste not one by one, but in pieces, say, 10,000 ... where tst_ex1.id between @b and @b + 10000 ( ... where tst_ex1.id between @b and @b + 10000 ). - Yaant
  • If there are indexes in the second table, then you can (most likely even need to) try to disable them before insertion and re-enable them after. - Anatol
  • > insert into dbo.tst_ex2 (tst_ex2.gui2) select tst_ex1.gui from dbo.tst_ex1> where tst_ex1.id <100000 have not tried? - student007
  • @YuraIvanov I did not specify in the condition that the insert must be done in a loop, and yes, the request goes much faster)) - student007

1 answer 1

The main way to speed up such a request is to make sure that you have an index on the id field. According to the logic of the table, this field can also be made PRIMARY KEY with a clustered index.

If you want to further speed up the insertion, the next step is to partition the table.

  • Thanks minamoto , did that with id . I also used Yaant for insertion acceleration . - student007