Another amusing conversation and somewhat shocking “discovery”.
More poor performing SQL – obviously missing any sort of suitable index (on Oracle).
Me (to developer): Could you add a suitable composite (multi-column) index to table PS_xxxx please for columns C,B and A – there seems to be frequent access using these three columns that would be vastly improved by adding an index.
Developer: OK.
After a few days I notice new “alternate search key” indexes in the DEV environment. One for each of the columns A, B and C.
Me: Did you add that index?
Developer: Yes, but in the testing I did it wasn’t very much faster.
Me: Really? So you added one index with columns C, B and A in that order and it wasn’t any quicker?
Developer: It didn’t seem to be.
Me: So how did you add the index exactly?
Developer: I marked column C as a search key in App Designer, saved the record, added B as search key and saved the record …. then I did build to create the index.
Me: Hmmm. And how many indexes were created?
Developer: Well just the one.
Me: Did you check that?
Developer: Well no – how would I do that?
Me: SQL Developer? TOAD? SQL against user_indexes/user_ind_columns?
The blank look I got at this point led me to believe this developer really didn’t have a clue.
Me: Do you understand what actually happens when you mark a column as an alternate search key in App Designer? It creates an index leading with that column and containing the “keys” of the record. So what you actually added was three separate indexes.
Developer: But there isn’t another way to add an index.
Me: (WTF?). So you have never used Tools -> Data Administration -> Indexes?
Developer: Errr … no.
Me: Why don’t you go have a look at it and come back to me?
Some time later ….
Developer: There is no such menu option in App Designer.
Me: (Sigh). Yes there is. Did you have the record open when you went to Tools -> Data Administration?
Developer: No.
Me: Give that a try, It is context sensitive – like some other menu options.
Some time later the developer returned “amazed” at the performance difference adding the index made (this was a 1.5 million row table). All of his pages were now responding instantly. Go figure …
I later discovered that the users had complained numerous times (i.e. well over 100 times) about the response time of the pages related to this table. The responses they were given by the “help desk” were less than helpful:
- The system is especially busy on (Mondays|Tuesdays…).
- The database is busy at the moment. Not entirely a lie – although arguably they should have said: “The database is unnecessarily busy at the moment because the developer is a clueless muppet”).
- The network is congested.
Of course, the individual in question was a Certified PeopleSoft Developer. Ho hum.