Tweet
Assume we have a table called “Transactions”. This table consists of all sales transactions ever recorded in the system. It holds approximately 155 million records. Each night a program runs that reads through EVERY record from the Transactions table and copies each record’s fields and some additional fields from other tables and places them into a new table that we’ll refer to as “New_Transactions”. Please note that the New_Transactions table gets cleared before it’s populated each evening. Thus, they’re currently doing a rebuild of everything each night.
99% of the time the New_Transactions table is identical to the original Transactions table with the exception of new records from the day prior that exist in the Transactions table. In a perfect world the New_Transactions table wouldn’t get cleared, and only the new records would be appended to the table.
That catch here is there are times when historical information changes like sales region changes, and these changes need to be incorporated into the New_Transactions table. I also need to ensure that everything from the Transaction table exists in the New_Transactions table and if a record doesn’t exist any longer in the Transactions table, then it also needs to be removed from the New Transactions Table.
I’d like to hear your thoughts. If you’re asking, “Why are they doing it this way?” It’s not my design, I’ve only been asked to make it run faster as this process takes over 2 hours to run each evening.
99% of the time the New_Transactions table is identical to the original Transactions table with the exception of new records from the day prior that exist in the Transactions table. In a perfect world the New_Transactions table wouldn’t get cleared, and only the new records would be appended to the table.
That catch here is there are times when historical information changes like sales region changes, and these changes need to be incorporated into the New_Transactions table. I also need to ensure that everything from the Transaction table exists in the New_Transactions table and if a record doesn’t exist any longer in the Transactions table, then it also needs to be removed from the New Transactions Table.
I’d like to hear your thoughts. If you’re asking, “Why are they doing it this way?” It’s not my design, I’ve only been asked to make it run faster as this process takes over 2 hours to run each evening.
Comment