Delete and Insert huge records in warehouse table

Hi,

My business needs to delete and insert rows in warehouse table (correction data) based on day and one other natural key combination. The warehouse table is being populated from various sources and are identied through source key in warehouse table. warehouse loading process is parallel for sources. Hence facing performance issues while trying to delete and insert corrections data for each source in warehouse table.

Any way to tweak the design or process to improve performance. Will soft delete using checksum work for huge delete and insert correction data.

Thanks in advance,
Paddu

Hi Paddu,

I would try and implement table partitioning by date. I'm only familiar with the SQL Server functionality (you need Enterprise Edition).

Once implemented it lets you drop a partition (i.e. delete all the rows) for a given date in a split-second. It also can store index data on the same position as the actual data, so you can drop and recreate indexes for just the affected dates.

Managing partitions can be a chore - I've been using the open source ManageParition.exe solution from sqlpartitionmgmt.codeplex.com

That tool does things like generating the staging table where you can load your fresh data, and handles the process of indexing and then merging the staging table back into the main table.

Good luck!
Mike

Partitioning can help here as Mike suggested. I would also look at trying to rewrite the ETL process to do only inserts.

Deleting data in a data warehouse always makes me feel uncomfortable. If you ran a report on Monday and deleted data on Tuesday, what would an auditor say on Wednesday.

I prefer inserting adjusting records so that corrections can be followed.

We have a similar issue in insurance where claims are reprocessed. A claim is initially paid then denied for example.

Or maybe a more appropriate method would be to handle the updates in the dimension tables.