We are using PeopleSoft Finance Application where we have Million rows of data on some of the Core Table's.
As we are facing Performance issues, we are planning to archive the data by keeping only 2 years of data and moving all the remaining years to the archive table.
Here we did some testing by archiving 3 core finance tables on the TESTING instance.
Here the 1st table had 124242809 row Data before archive and 64691967 after archive, 2nd table had 6220948 row data before archive and 2808580 after archive and the 3rd table had 93246 row data before archive and 30908 after archive.
After archiving the data to the Archive Table, we ran the index rebuild and Update Stats on the Original Table.
Post that we ran one of the regular PeopleSoft Process and that process is taking double the time. Usually before archiving it takes 20 minutes to complete and now post archive data it takes 40 to 1 Hour.
Tried the below steps as well and the problem exists. Not sure whether we are missing out any additional steps. Kindly suggest some tips for improving the performance post Table Archive.
- Created an staging table same as live table.
- Inserted the records from live table to Staging table.
- Drop the Live Table.
- Then rename the Staging table to as same as live table name.
- Created the Index same as live table
- rebuild indexes for 3 tables and update the stats for the 3 tables.