Humour Peoplesoft Performance

A Conversation with a PeopleSoft “Developer” – Part 1

A bit of amusement, but unfortunately based on actual conversations I have had with developers over the years.

This one started with me noticing some heavy logical I/O at the database level across various views. These originated from various user actions – search records on pages, “submit” buttons on approval pages and even scheduled queries.

A bit of investigation into each case always brought me to a single locally developed “workflow” table. It had the usual structure for a workflow/worklist table – had all the workflow key columns, followed by the “data” key columns. Every column in the table was flagged as a key column. It was instantly obvious that the developer did not understand the importance of indexes and possibly quite a few other things. Here’s the conversation:

Me: So, this worklist table PS_xxx_WL – you created that right?

Developer: Yes.

Me: And you developed all the approval pages, the PeopleCode and the search records that use this table?

Developer: Yes (starting to look a bit nervous).

Me: So how did you choose the “key” of the table?

Developer: What do you mean?

Me: Well, why are ALL of the table columns flagged as key columns.

Developer: They need to be that way to avoid duplicates.

Aside: Oh … this is getting to be fun …

Me: Hmmm. So the combination of the workflow fields of BUSPROCNAME, ACTIVITYNAME, EVENTNAME, WORKLISTNAME, INSTANCEID and TRANSACTIONID isn’t unique?

Developer: No, you need the columns from the underlying data table to ensure uniqueness (he’s saying the words but doubting the truth of them now).

Me: But what’s the key to PSWORKLIST?

Developer: Errr …

Me: I gave you a hint a couple of questions ago.

Developer: Well …

Me: Yes?

Developer: But an employee might have two workflow transactions of the same type…

Me: Very true. And?

Developer: Anyway, you need to include those fields to make accessing via the employee faster.

Aside: Now it’s getting really fun,

Me: So you are saying that having all the other fields in the unique key makes access via the EMPLID faster?

Developer: Yes. It needs to be in the key to be retrieved quickly.

Me: I am afraid you are quite wrong about that.

Developer: But that’s what they teach on the PeopleTools courses.

Me: They most certainly don’t. Ok – let’s talk about that “faster” belief you have in some more detail ….

I won’t detail the rest of what was a very long conversation, but in summary this developer didn’t understand the data structures, how a database solves a query and honestly believed that just being in the “key” made access via that column faster. A certified PeopleSoft developer too … 🙂

My favourite part of the later conversation included this gem:

Developer: I spent 3 months developing all of this.

Me: (Slightly surprised so little took so long ….) And in all that time you didn’t notice that everything you built was dependent on the non-worklist keys and required access via the employee? And you didn’t think that an index leading on EMPLID might help performance?

Developer: It is indexed – it is in the key. A table can only have one key.

Me: (Suddenly realising how clueless this guy really is). Hang on … do you mean key or index?

Developer: The key and the index are the same thing.

Me: (To myself WTF?). So a table cannot have multiple indexes?

Developer: No – only the one.

Me: ???????

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.