Importing data with a lot of SELECTS during transaction

Question

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 articles and product_assets which have foreign keys to the products table.

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).

Answer 1

I would first load the data into a staging table (no indexes/constraints other than a self incrementing clustered primary key). Once the data is loaded you can add other indexes to help out with the next step, which would be to start processing the records.

You should do as much of your data validations in sets, say 10,000 records at a time. Doing it one record at a time is too time consuming, and doing it all in one batch can bring your server down to its knees.

Answer 2

First you need to drop/disable all indexes and keys on inserting tables. There was a similar question I answered a while ago. If your database needs to be live during import then don't do this, but it will affect your performance.

Then you need to abandon SELECT for each row and use BULK INSERT. Use BATCHSIZE argument to specify how many rows you want to insert per batch. Your current solution would translate to BATCHSIZE = 1, but I would recommend to increase this number in order to reduce number of roundtrips to SQL Server. Also take a look at this answer.

Answer 3

The best approach would be to split your file into X partitions. (You can read more about table partition on the web) Then load your partitions in X parallel processes in a SSIS package using the BULK INSERT TASK. You should load your file in a stage partition table first.

Then, instead of using a each line validation approach, I would go into a multiple lines validation if possible.

You will finish by offloading directly to the production table. But you might consider NOT letting other processes reading your data OR make sure this read access is using with(NOLOCK) query hint to avoid blocking.