October 25, 2024 at 4:46 pm
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.
October 25, 2024 at 6:00 pm
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.
October 26, 2024 at 11:13 pm
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply