Enlightened Performance
Transcending SQL-89 for Ultimate Performance
Posted on 1/28/2007 (originally published in print on 3/1/2004)
by JD Kuwica
The Problem
One of the biggest hurdles faced by any large organization running PeopleSoft is achieving an acceptable, agreeable Service Level Agreement for Batch. The customer wants an infinite number of transactions processed in zero time. The hardware team wants a guaranteed four-hour dark-window every evening for system maintenance. Business Operations wants the system available 24-7. And, almost always, insufficient resources are allocated to the team responsible for meeting the SLA. Welcome to IT in the 21st century!
The Flawed Premise
The premise of this article is that not only are the resources often insufficient, but also inappropriate. In most organizations, “performance” is the province of the DBAs. This is a mistake for packaged applications like PeopleSoft. The problem is that the decision-makers who assign the responsibility for “system performance” to the DBAs grew up in the days of custom applications on big-iron. In those days, every piece of SQL (if, astonishingly, it were written in SQL) was scrutinized by a DBA before it ever went into production. That's the flawed assumption: that PeopleSoft's SQL has been subjected to performance-based review. A typical PeopleSoft batch program has so many SQL statements that statement-by-statement review would be infeasible. An additional problem is PeopleSoft's assumption that just because Application Designer allows a developer to create tables and indexes, no DBA involvement is needed.
Build a Team
So, how can this gap be bridged? Ideally, an organization would only need one or two people who are not only highly skilled in database tuning and PeopleSoft development, but also possess detailed application knowledge. While it is theoretically possible to find these all these skills in one person, those folks are exceptionally rare (and very expensive). So, the alternative is to create a multi-disciplinary team, jointly responsible for results.
A New Paradigm
Once the team is formed, a new paradigm for performance is needed. Sometimes, largely because of the implications of set-based processing, it is impossible to fix a slow statement by simply adding indexes. Accepting this does not mean accepting that a solution is unattainable, just that the solution must come from elsewhere.
Set-Based Processing
What are the implications of set-based processing that produce index-insensitive SQL statements? Consider the following: a batch program operates on parent/child/grandchild data entities. The parent table has a field which represents whether batch processing is needed; for example, Posted_Status. This field is likely to have very few values, such as N for “Needs Posting,” P for “Posted” and U for “Un-posted.” Obviously, the posting program should only operate on those parent rows with a Posted_Status of N. Also, after a certain amount of time has elapsed, the majority of the parent rows will have a Posted_Status of P. Assuming that, for the most part, “batch” processing implies processing all of the N rows, we can conclude that it is impossible to create a good index for quickly finding the desired rows. A good index is impossible because the cardinality of the index is very low. Further, if Posted_Status is the lead field of the index, the index will need constant reorganization, because the whole point of the field is to be changed from N to P.
To continue reading this article you must have a current VP1 Subscription.
