principlesqlMinor
DROP vs DELETE vs TRUNCATE for staging data
Viewed 0 times
deletestagingdroptruncatefordata
Problem
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:
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?
- Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
- Delete all data in staging, load the data into staging table, load all data into target.
- Truncate staging table, load into staging, load into target
- Drop staging table, create staging table, laod into staging table, load into target.
- 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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#188391, answer score: 2
Revisions (0)
No revisions yet.