| Use All of the Keys | KEVIN RESCHENBERG
05-23-2005 |
I've previously suggested
using all of the keys within
a subselect. Today I'd like to repeat this advice and
expand on it a little.
I frequently see cases in which an installation does
not use certain features of the PeopleSoft system, such
as SETIDs, or companies (when there is only one company),
or EMPL_RCD (when each employee has only one "job").
This certainly makes setup and maintenance more simple.
Unfortunately, in many of these cases the developers
then ignore these features when writing SQL.
If you have only one SETID, or if every EMPL_RCD
is 0, why bother joining on these keys? There are two
very important reasons. The first is performance.
If you leave out
keys when joining tables, you can prevent the database
from using indexes efficiently. Sometimes it doesn't
seem to matter much, but in other cases the effect
can be huge. It depends on how the various indexes
are defined and on the execution plan chosen by the
database. It's best just to join on all of the keys
(unless, of course, the logic of your query requires you to
skip one of them).
The second reason for using all of the keys
is that the one-SETID or one-EMPL_RCD
situation might not be permanent. Someday, someone is
going to want to hire an employee into two jobs, or
reorganize your department table. Someday, a new hire
into your HR or Finance department is going to have
prior experience with PeopleSoft and is going to suggest
changes. If you code for this possibility all along, there's
nothing to worry about. But if your custom code
neglects certain capabilities built into PeopleSoft,
you will be faced with a sudden need to revisit all
of the custom code.
Suppose that the only SETID you currently have in a table is
"SHARE". You could hard-code that value in your SQL.
However, that isn't the best solution either because
you would still need to modify your code if another
SETID is added. It's better to find the value in
another table. For example, JOB contains the field
SETID_DEPT. That is the SETID for the employee's
department and it corresponds to the SETID field
in the department table. You can also go through
the various "set control" tables to find the SETID,
but in most cases
it is much easier (and less confusing) to use the
copies stored in JOB. JOB also provides SETID_LOCATION
and SETID_JOBCODE. If you are not using JOB,
it is still better to join to the set control tables
than to leave the field out of the SQL.
So even if you don't need (or don't understand) SETID,
EMPL_RCD, COBRA_EVENT_ID and those other keys, use them
anyway in your SQL. Your code will be more efficient
and robust, and it will run no matter what the users
decide to do. See examples in my first column on this
subject, April 11, 2004.
|
