• MadAdmin (10/26/2016)


    Deletes and updates should be avoided.

    Use partitions for your fact tables and apply partition switching instead of deleting data.

    Agree completely.

    Don't bother issuing a delete/update on a columnstore. It may perform great to start with, but it will quickly degrade. And it will degrade all future queries on the table until it is rebuilt.

    I had to update a column for about 1% of a columnstore table with 100 billion rows. The standard approach of updating chunks in a While loop started out great, but very quickly slowed down as the number of delta stores increased.

    Fortunately, our table was partitioned by day (about 100 million per day). What I did was swapped the day to update with an empty partition/table. Than I insert-selected back into the target table, transforming the data in the select statement. After truncating the swap table, I was ready to update the next day. Since the process was repeatable, I was able to script it out and got through it within a couple of hours.

    Wes
    (A solid design is always preferable to a creative workaround)