| 5-Character SETIDs | KEVIN RESCHENBERG
05-30-2005 |
Last week's references to SETID reminded me of
the old item about always defining SETID and BUSINESS_UNIT
values to be five characters in length—in other words,
setting these up so that every value completely fills
the field. For example, if your SETIDs follow country
lines, you would create a "USA01" instead of a "USA".
This advice was given by PeopleSoft. A quick search on
Customer Connection shows that it was originally published
as a workaround to a performance problem, to be used until
the issue is corrected. PeopleBooks still (at the 8.14
version at least) says that five characters must be used for
SETID and BUSINESS_UNIT, warning of severe performance
problems if this is not done.
Entries on search forms are used to construct SQL.
When a SETID entry does not completely fill the field,
Tools will construct a BETWEEN clause. I imagine that
it's something like AND SETID BETWEEN 'USA' and 'USAz'.
If the SETID completely filled the field, the SQL could
be simpler and more efficient: AND SETID = 'USA'.
(I'm surprised about the BETWEEN. Why not AND SETID LIKE USA%?
Is BETWEEN much more efficient than LIKE?)
If your SETIDs or BUSINESS_UNITs are not set up as the
full five characters, should you go through the effort
of changing them? I don't think so. This issue
affects online search dialogs only, not App
Engine, SQR, PeopleCode, custom views, or anything else.
The one time I tried to test this, I didn't see any
difference at all (although results might vary by
the database type and the specific tables being used).
Also, this issue would seem to apply
to every field on a search dialog. Enter a partial
code—company ID, for example—and Tools will return
all of the matches. It must be using the same logic
for these fields...right?
Well, I don't know about that for sure. It may be that
SETID and BUSINESS_UNIT are handled in a special way.
(There are certain "magic" field names that Tools will
handle separately. More on that in a future post.)
In any case, my advice is to use the full five characters
when you create new SETID or BUSINESS_UNIT values, but
don't worry about it too much if this was not done
in the past and you haven't experienced any major
online search performance issues.
|
