Updating multiple rows in sql server 2016
Once the files were loaded, we were able to run sophisticated queries to calculate statistics and then load them into relational tables.
After developing a prototype based on Informatica, we decided to use Power Center 9 for workflow control and supplemented this with SQL Server stored procedures for parsing, validating and inserting the data.
Before implementing parallel loading we wanted to find a simple way to speed up the performance of our procedure, something small that made a big difference – multiple rows insert.
Instead of calling a single row insert, I sent five rows in the one statement.
Last year, I participated in an Extract, Transform, Load (ETL) project.
My task was to load data from a large comma-delimited file.
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 sizes of other file types were 30 times smaller, and we successfully used Informatica Power Center 9 to parse, validate, transform, and load the files into a staging table in SQL Server.What’s the best way to use statements that insert several rows at a time, and how many rows should I try to pass at a time?For performance, should they be wrapped in explicit transactions or should I use auto-commit?We wanted to reduce the time spent on the loading process (16 hours), and by using stored procedures, we could now calculate some statistics with incrementing counters in the loop.Also, we were able to implement a business rule which stated that the number of values in a file line was a variable between 840 (12 months * 70 years) and 1200 (12 months * 100 years).