0

I'm trying to copy 60 Million Records in other Table with the Clustered Index with Fetching. but after 20 Million Records it's going too Slow. I dont no what should i do. can anybody help me please. hier is my Time counting.

1000000 Million Min: 1
2000000 Million Min: 0
3000000 Million Min: 0
4000000 Million Min: 2
5000000 Million Min: 2
6000000 Million Min: 1
7000000 Million Min: 0
8000000 Million Min: 1
9000000 Million Min: 0
10000000 Million Min: 1
11000000 Million Min: 0
12000000 Million Min: 1
13000000 Million Min: 1
14000000 Million Min: 0
15000000 Million Min: 1
16000000 Million Min: 1
17000000 Million Min: 1
18000000 Million Min: 0
19000000 Million Min: 1
20000000 Million Min: 3
21000000 Million Min: 3
22000000 Million Min: 4
23000000 Million Min: 5
24000000 Million Min: 4
25000000 Million Min: 4
26000000 Million Min: 4
27000000 Million Min: 4
28000000 Million Min: 5
29000000 Million Min: 5
30000000 Million Min: 5
31000000 Million Min: 6
32000000 Million Min: 7
33000000 Million Min: 7
34000000 Million Min: 8
35000000 Million Min: 8
36000000 Million Min: 9
37000000 Million Min: 8
38000000 Million Min: 10
39000000 Million Min: 10
40000000 Million Min: 11
41000000 Million Min: 11
42000000 Million Min: 11
43000000 Million Min: 12
44000000 Million Min: 11
45000000 Million Min: 12
46000000 Million Min: 12
47000000 Million Min: 14
48000000 Million Min: 13
49000000 Million Min: 13
50000000 Million Min: 14
51000000 Million Min: 15
52000000 Million Min: 14
53000000 Million Min: 16
54000000 Million Min: 18
55000000 Million Min: 18
56000000 Million Min: 20
57000000 Million Min: 19
58000000 Million Min: 21
59000000 Million Min: 19 

    
declare 
        @RecNo Int
      , @RecCount Int

      , @RecordST nvarchar(max)
      , @str_date datetime
      , @end_date datetime;

    Set @RecNo = 0
    select @RecCount = 1000000

         While 1 = 1 
           Begin


                set @str_date = getdate();
                Insert Into dbo.test2(
                   ap_id                                
                  ,lipt_id                          
                  ,li_cntr                                            
                 )
            select 
                     ap_id                              
                    ,lipt_id                            
                    ,li_cntr                                                
                 from dbo.test 
             order by  ap_id, lipt_id, li_cntr

             offset @RecNo rows
             fetch next @RecCount rows only;
             if  @@ROWCOUNT = 0
               break              

             Set @RecNo += 1000000;

             set @end_date = GETDATE() ;
             set @RecordST = cast(@RecNo as nvarchar(max)) + ' Million Min:'+cast(DATEDIFF(MINUTE,@str_date,@end_date) as nvarchar(max))
             RAISERROR(@RecordST,0,0) WITH NOWAIT            

  end
Hamid ajdari
  • 1
  • 2
  • 1
    how about dropping the index(es) before the copy, then creating it again after the copy? – Cato Jul 30 '18 at 10:53
  • Because of using offset that method of inserting will become slower the higher that offset becomes. You might want to read [this old SO answer](https://stackoverflow.com/a/7354416/4003419). If the source table has a primary key that's a number, then with that method the speed would be more consistent than doing it via offset. – LukStorms Jul 30 '18 at 11:04
  • 2
    `OFFSET` and `FETCH` will become progressively slower because the offset must be recalculated every iteration. You'll get linear performance if you batch by ranges of the clustered index key instead of row number pagination. – Dan Guzman Jul 30 '18 at 11:07
  • thanks for the answers. how can i batch by ranges of the clustered index key instead of row number pagination? could you please explain more? – Hamid ajdari Jul 30 '18 at 11:50
  • `TOP(@RecCount) ... WHERE plip_ap_id > @LastValueProcessed ORDER BY plip_ap_id` should have the same performance characteristics every time. This requires that your table has a unique key that you can seek from. For a compound key it gets a lot more annoying. – Jeroen Mostert Jul 30 '18 at 12:58

1 Answers1

1

First of all, you need drop all constraints like unique, PK and etc. It's the bottleneck for each insert in an existing table.

Secondly, If you insert into the table much more records than it has now then you may increase performance using SELECT INTO statement instead INSERT. But remember that SELECT INTO creates a new table so you would need to think how to append records which were there before.

Last but not the least, you can use loops and insert 1M record by batch.

Meow Meow
  • 637
  • 6
  • 17