2

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 ????

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
4
  • 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, 2012 at 15:04
  • +1 to your comment, @HLGEM. That is great advice.
    – Thomas Stringer
    Mar 23, 2012 at 15:05
  • 2
    I've never not used a staging table. +1
    – gbn
    Mar 23, 2012 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, 2012 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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