Peoplesoft Search Keys

I have taken a very simple topic today but yet a very important one. Whenever we create any custom objects in Peoplesoft, be mindful of the Keys and Alt Search keys you choose, since every column that has been identified as a key, will end up creating an additional index.

Every index is a lot of overhead on INSERT/UPDATE and DELETE statements. So, choose the KEYS carefully and only what is really needed. I have seen objects with 28 Keys that essentially means an index with 28 columns, which would obviously be no good as an index.

Additionally, every ALT search keys would create additional indexes on that objects. This would be even worse. You will take a big performance hit if you have more indexes than needed. 

Peoplesoft: Slow data conversions

Are your data conversions running longer and longer?

There is a simple fix you can do to make it go faster. Let's discuss it here.

Let us look at the basic conversion process in any environment.

Flat Files -> Staging tables -> ERP tables.

This is how the typical conversion happens. We get the flat files from legacy the system. We load the data from the flat files to the staging tables using data loading utility like SQL Loader or any other tool.
Once the staging tables are populated, we apply business validations and business logic to make sure we have clean data. Finally, we insert the data into the ERP Tables.

In the above process, the use of the STAGING tables cannot be undermined but their use is only limited. The STAGING tables will not be used once the data is converted. The ERP application is never going to use the staging tables. So, by dropping the unnecessary indexes on these staging tables, we can easily improve the performance of the conversions by manifolds. In Peoplesoft, when we define ALT keys, Peopletools automatically create indexes on these keys that are often not needed as these staging tables are not going to be searched by the application.

Following the simple step described above, you are sure to make your conversions go faster by a factor that is dependent on the number of indexes dropped.

Have a question? Please leave a comment and I would be glad to answer.