I'm using SQL server 2008 R2 and I have a
products table with an auto increment integer
id column as primary key and a
product_no column (unique) and 6 tables like
product_assets which have foreign keys to the
I need to import about 1 million products into the
products table and the other tables (about 16 million rows in total (all tables together))
This import should run as quickly as possible (<=6h) and I need to perform additional SELECTs per data row from the tables during import to validate the data. The data source is a CSV file so I'm making a transaction for each line which represents an article (entry for the
articles table) due to the fact that the import could fail at any line.
During import it should also be possible to read data from the mentioned tables with a different connection.
Currently this import runs in over 24h (the SELECTs to validate the data which I have to do for every line from the CSV during import takes it toll)
Any advice how to improve import performance in such a case?
I can split the CSV data in an preprocessing step into parts (e.g. last char of the product number) so that the parts don't interfere on row level and run these parts in different threads simultaneously if that could improve the import speed somehow. I mention this because the import machine and the sql server machine have a low CPU utilization during import so i guess a lot of time is wasted with network I/O (machines are connected with gigabit ethernet).