Delete and Insert huge records in warehouse table

View previous topic View next topic Go down

Delete and Insert huge records in warehouse table

Post  paddu on Mon Nov 04, 2013 12:00 am

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

paddu

Posts : 2
Join date : 2011-12-04

View user profile

Back to top Go down

Re: Delete and Insert huge records in warehouse table

Post  Mike Honey on Thu Nov 21, 2013 9:39 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Delete and Insert huge records in warehouse table

Post  BoxesAndLines on Fri Nov 22, 2013 8:59 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Delete and Insert huge records in warehouse table

Post  Jeff Smith on Wed Nov 27, 2013 2:40 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Delete and Insert huge records in warehouse table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum