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.

We have a CSV the user imports into the Application.

After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a table(Subscribers)

We got around 1 millions of inserts and 1 million of updates operations.

Which will be the most effective way to execute them?

1) For the inserts we planned to put them in a CSV file and execute them as a BULK INSERT.

2) What should we do for the updates? Is there something similar to BULK INSERT?

3) Is there any other optimization we should look?

3a) Like for example disabling the indexes while doing this? We should consider the DB should be kept available as normal for the other users of the application...

3b) Suggestions ????

share|improve this question
add comment

1 Answer

If you are bringing data from a CSV with a large import process, I'd recommend you utilize a staging table. This will allow you to dump all of your imported data into the staging table that won't be queried by end-users/applications. You can use this staging table as a source for your INSERT and UPDATE operations.

Once the data is in the staging table you can kick off a stored procedure (or multiple stored procedures, depending on how vast your business logic is) to do the necessary INSERT and UPDATE DML statements into the actual queried table.

As for performance, you need to decide what is more important for your implementation. If you're looking to get the data in the table as quickly as possible, a very feasible route may be to disable the indexes. But realize that OLAP performance will be impacted by that. If you want to ensure the minimal amount of end-user impact then you would have to approach this a different way. When you say "the DB should be kept availabel as normal" then I would assume the latter.

Performance is double-edged in this situation. Performance for the data import will most likely take away from performance of end-user interaction with the data, and vice versa.

share|improve this answer
2  
Once data is in a staging table, you can perform the inserts/updates in batches, 1000 records (justa guess, you will have to look at your system performance to see where to set I have set batches form 500 to 50,000) at a time. This will be less disruptive of your system but overall will take longer. I also suggest doing this process during low user volumn hours if there are any. We do most large imports in the middle of the night. –  HLGEM Mar 23 '12 at 15:04
    
+1 to your comment, @HLGEM. That is great advice. –  Thomas Stringer Mar 23 '12 at 15:05
2  
I've never not used a staging table. +1 –  gbn Mar 23 '12 at 15:07
2  
Staging tables help to research when something goes wrong too. You can easily look to see if it was their data at a fault (which it is most of the time in a mature import process). –  HLGEM Mar 23 '12 at 15:33
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.