Updating multiple rows in sql server 2016
The performance of 'extract, transform, load' (ETL) processes for large quantities of data can always be improved by objective testing and experiment with alternative techniques.The cause of poor performance can sometimes be surprising.I wanted to find the best way to import data into SQL Server, so I decided to experiment with a couple of different data load methods to compare the results.
I varied the number of columns while keeping the length in bytes of the rows constant.
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).
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.