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 are building a data warehouse. For some of our target tables, the only way to manage the process correctly is to re-transform all the data every time something changes.

Currently we do this using a 'table swap', ie:

  • Create new data in temp schema 'B'
  • Open transaction
  • Move A.Table to schema C
  • Move B.Table to schema A
  • Move C.Table to schema B
  • Commit

In theory this is all instant, because SQL never needs to physically copy any data. However due to the schema locks that get acquired, we are getting terrible concurrency.

Is there any other way of bulk moving data between tables (move, not copy), or do we have to fall back to doing an INSERT INTO (which might be bulk logged, but will still cause all the data to be written to disk again, which is slow)

The tables in question are several GB, hence the performance issues

SQL 2012

share|improve this question
    
How does moving the table to another schema help you transform the data? –  Thomas Kejser Jan 5 at 1:17

1 Answer 1

The better way wqould be to fix the broken programming requiring a total re-transform of all data. THat is a terrible non-scaling approach to start with and whoever justified that - I hope they now work in another field, as far away from any project responsibility as possible.

If you use enterprise edition you could possibly use a partition and partition swaps - even with only ONE Partition that at least has some schema lock optimizations. The schema of the table never changes. It has negative implications on statistics, though, before SQL Server 2014.

At the end it really runs down to a non-competent approach that was choosen - those "smart shortcuts" always come back to bite you.

share|improve this answer

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.