I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and moved into a data warehouse database, which is done using stored procedure and this involves complex join on other staging tables of similar size.
Below is the structure of staging table:
I am thinking about partitioning the staging tables on the SystemCode field. I have around 40 distinct systems from which data will be loaded. Once the records are processed, 'ProcessStatus' flag will be updated to prevent further processing.
Does anyone think this is a bad idea? My aim is to have them loaded as quickly as possible into data warehouse. I have considered indexes and the result was not great. I can't partition on date as the load to warehouse won't be based on this criteria.
Appreciate your help. Thanks.