Process taking double the time post Table Archieval

Javeed Sheriff M 1 Reputation point
2022-06-22T18:21:51.777+00:00

Hi All,
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.

  1. Created an staging table same as live table.
  2. Inserted the records from live table to Staging table.
  3. Drop the Live Table.
  4. Then rename the Staging table to as same as live table name.
  5. Created the Index same as live table
  6. rebuild indexes for 3 tables and update the stats for the 3 tables.
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,759 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2022-06-24T21:08:39.157+00:00

    It's only an estimated plan. An Actual plan, that is a plan from the an actual execution is better since it provides more information. However, it seems that the table PS_JP_PST1_TAO4 would benefit from an index on the column PROCESS_INSTANCE. And if this value is intended to be unique, it should be a unique index. (Else not.)

    0 comments