2

I need to import data (>> 1 GB) into MS-SQL-Server. For several reasons I need to import this data into a staging table (not directly into the target). From staging the data is loaded into the target table. The data in the staging table is never used again. I have several ways to do:

  1. Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
  2. Delete all data in staging, load the data into staging table, load all data into target.
  3. Truncate staging table, load into staging, load into target
  4. Drop staging table, create staging table, laod into staging table, load into target.
  5. Drop staging database, create staging table, load into staging table, load into target.

If the solution is not restricted by your server role: What would be the recomended solution? Which is most robust?

Solution 1 is most likely nonsense if you don't need the data anymore. Solution 3 performances much better compared to 1 and 2. But what between solution 4 and 3? Actually I would prefer solution 5 as I can change nealy every parameter within the staging process within my load procedure but you need rights to drop&create database. This is quite a lot for a load process. So in most of the cases I use drop and recreate the staging table. But is this the recommended way?

  • 2
    Unless your table definition is going to change step 3 makes the most sense. – Erik Darling Oct 13 '17 at 9:42
  • 1
    The difference between 3 and 4 is that you loose all the permissions when do drop/create and should re-grant them (when truncating all the permissions remain) – sepupic Oct 13 '17 at 9:42
  • I'd like to comment that for solution #4 and #5 the most robust solution might be to drop the table i.g. database after loading. – Christian4145 Oct 20 '17 at 9:08
1

What would be the recommended solution?

  • Option 3 if you can change the recovery model of the db that you are inserting into else create a staging db and have it in bulk or simple recovery.
  • Depending on the version of sql server, you can even use memory optimized table for staging created with schema_ONLY.

Which is most robust? - Test it out what suits your requirement. Do not drop the entire db or table - what is the point to drop and recreate .. when you are reusing it anyway.

Recently I had a requirement to import 3+ GB csv files into sql server. Below is what I did to achieve 100K rows/sec (YMMV due to hardware and environment difference) on a physical machine (was a little less on a VM ) using SSDs and 16 cores and 256GB RAM.

  • Use BULK INSERT .. WITH (TABLOCK) & TF 610 & appropriate batchsize with bulk logged recovery model and insert the data into staging table (no indexes at all) or using SSIS - use fast load.
  • Depending on the version of sql server for compression, you can use columnstore index (that might slow down a bit ... but not significantly).
  • For best performance, you can split the files and insert simultaneously insert using BULK INSERT.
  • You can have a seperate database with the staging table if you cannot switch to BULK Logged or Simple recovery model for the DB that you are trying to insert.
  • If the data is going to be large, then insert into different partitions & use partitioning and just SWITCH partitions when the load is completed. Starting sql server 2016, you can truncate individual partitions. This way, you can create a dynamic script to use BULK INSERT to insert into different partitions and then switch the partitions.
  • Make sure to presize the data and log files to have minimal autogrowth events, power plan is set to high performance and Instant file initialization is enabled.

Refer to my answer for some additional hints :

2

The "recommended way" is to design the best solution given the relevant factors.

Options #1 and #2 are unlikely to be ideal as they result in a lot of unnecessary I/O. #3 is what would be best in most situations, but as far as I/O is concerned it is not much different from #4. So performance-wise, #3 and #4 can considered to be the same. The additional I/O to drop/create a table as opposed to just truncating it is insignificant compared to the I/O of the typical data load.

Your comments seem to indicate that you need to make changes to the staging table quite often, and if that is the case, then #4 may be best for you. Since it's a staging table, it is unlikely that permissions are complex, and if it's in a dedicated staging database where staging account is the only ID that has read/write, then you wouldn't even need to explicitly set permissions when the table is created.

Option #5 is a non-starter unless you have some specific reason to create a new database each time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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