A line in the file consisted of an object identifier and its payments for decades.
This change reduced execution time by 40%, which meant hours in my case. begin get parsed data into variables @c1_1,@c1_2, @c1_3,…@c5_6, @c5_7, insert into Target Table(c1,c2,..,c7) values(@c1_1,@c1_2…@c1_7), (@c2_1,@c2_2…@c2_7), (@c3_1,@c3_2…@c3_7), (@c4_1,@c4_2…@c4_7), (@c5_1,@c5_2…@c5_7); for explicit transaction control, every 2000 inserts were wrapped into a transaction end After implementing multiple rows insert and parallelization, the full time of loading and processing the file was reduced to two hours and forty-five minutes.
The total time included all the ETL operations, loading a staging table, statistics calculation and index creation.
Last year, I participated in an Extract, Transform, Load (ETL) project.
My task was to load data from a large comma-delimited file.
During every test, I loaded a million rows into a table.