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: ???????