Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Bulk inserting data is extremely fast. My guess is that the slowness you are experiencing is the validation SELECTS that you are performing. Make sure they are as optimized as possible. –  datagod Feb 7 '13 at 0:51
add comment

4 Answers 4

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Don't disable the clustered index as that makes the table inaccessible. –  Kenneth Fisher Jul 7 '13 at 21:49
add comment

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.

share|improve this answer
    
this is a misconception that "NOLOCK" places no lock on the database objects and is best to avoid blocking. Actually, "NOLOCK" will put a SCH-S (schema stability) lock. look at the "Effect of Nolock on performance" sqlservercentral.com/articles/Performance+Tuning/2764 as well as "Previously committed rows might be missed if NOLOCK hint is used" blogs.msdn.com/b/sqlcat/archive/2007/02/01/… –  Kin Jun 7 '13 at 16:06
add comment

Look at the recommendations provided by me here. They are for flat files, but SQL Server side still applies to your scenario along with links to some excellent sources and white papers.

Basically, when you want to do selects while loading the data (obviously not a good idea), then look for setting RCSI (Read Committed Snapshot Isolation).

-- check if the database is already configured for RCSI 
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'

-- configure database to use RCSI 
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON

Some excerpt from the whitepaper :

Read committed snapshot isolation (RCSI) was introduced in SQL Server 2005 as a new mechanism to prevent queries reading data to block, or be blocked by, other queries modifying data in the same tables. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint.

When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows.

For databases that might incur large amounts of UPDATE or DELETE activity, RCSI can create additional contention and bandwidth demand on tempdb. However, under RCSI, INSERT (including BULK INSERT), has no impact on tempdb and imposes no additional overhead aside from the 17 additional bytes added to each row. (Note that these 17 bytes are also noncompressible).

You have to consider tempdb performance - multiple tempdb files will help. If you see a lot of contention then -T1118 will help in that case.

Now the data loading, can be done efficiently using the sliding window technique (referenced in the white paper).

You can use to automate the sliding window technique - creating staging tables, loading data into it and then switching the partitions, SQL Server Partition Management tool -- it has command line option as well -- available at CodePlex.

share|improve this answer
    
@AlexKuznetsov Where is it mentioned that the data is inserted from the output from a SELECT ? The data is selected when the insert is running on the same table. HTH –  Kin Jun 7 '13 at 19:03
    
agreed, you right. –  AlexKuznetsov Jun 7 '13 at 19:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.