The table in our diagram already contains 100 columns. We need to add another 600 columns if we follow the horizontal method of data storage. If we use a vertical data warehouse, which means creating a new table and creating referential integrity with a table containing 100 columns, there will be a problem joining the table, since a table containing 100 columns has 53 million records, and the new table created will have much more than that. Which approach is better in this case.
I would like to add an interesting test case here. I added 600 columns to my table, already having 87 columns and 53 million records. Then I tried updating it in batches
a> Time spent updating 1000 records → 2.10 sec. b> Time required to update 10,000 records → 5.57 sec. c> Time spent on updating 1,000,000 records → 5.42 min. d> Time spent updating 53 million records → 4. 5 hours (exhausted table space and we needed to expand the table space)
Can anyone suggest a faster update method?
source
share