Copying table data efficiently

• Jus

  SSCrazy

  Points: 2536

  More actions

  October 25, 2024 at 4:46 pm

  #4473758

  Environment: Azure db with P15 (4000 DTU)

  Table 1: Having 90 coluumns of which 25 columnd encrypted , having approximately 60 million records

  Table 2: Exact similar structure as Table 1 with no records

  Requirement : Need to copy data from Table 1 to Table 2 In quicker time and least usage of DTUs.

  workdone:

  1)I have used inert into select..from table 1, which took 50 min to load with 100% DTU usage for all the time,

  2) copied chunks of data (with 400k chunks), took 90 min with 85% DTU Usage most of the time

  Now, i need a better way of copying data faster with least DTU usage.

   

  Please suggest any better ideas, thanks in advance.

• frederico_fonseca

  SSCoach

  Points: 16207

  More actions

  October 25, 2024 at 6:00 pm

  #4473786

  did you use "insert into tbl with (tablock)" or without it - and what indexes do you have on destination table.

  other than that I can't help as I don't have any Azure db's.

• Jonathan AC Roberts

  SSCoach

  Points: 18404

  More actions

  October 26, 2024 at 11:13 pm

  #4474141

  Try running the data transfer in multiple parallel sessions in SSMS, using chunked INSERT INTO ... SELECT queries. Open two or three SSMS windows, and in each, execute a separate query to handle a specific range of rows, adding a WITH (TABLOCK) hint to reduce lock overhead. Stagger each query's start by a few seconds to spread DTU usage and monitor real-time performance to prevent reaching 100% DTU saturation. This setup improves transfer speed by using manageable, non-overlapping chunks and leverages parallelism effectively, reducing total transfer time compared to a single, resource-intensive operation.

  Example:

  -- Window 1
  INSERT INTO Table2 WITH (TABLOCK)
  SELECT * FROM Table1
   WHERE PrimaryKey BETWEEN 1 AND 250000;
  
  -- Window 2
  INSERT INTO Table2 WITH (TABLOCK)
  SELECT * FROM Table1
   WHERE PrimaryKey BETWEEN 250001 AND 500000;
  
  -- Window 3
  INSERT INTO Table2 WITH (TABLOCK)
  SELECT * FROM Table1
   WHERE PrimaryKey BETWEEN 500001 AND 750000;