A little while ago I was asked whether Business Units need to be 5 characters for good performance. PeopleSoft used to recommend that SETIDs and Business Units should be defined as five characters.
I blithely responded that it was because if you put five characters into the search criteria of a five character field on a Record Locator Dialogue, then the resulting SQL would result in an equality condition rather than a LIKE.
If I search for a string shorter than the field, it generates a LIKE:
If you search for a string equal in length to the field you get an equality (unless you specify something else in advanced search).
That is how it was in PeopleTools 8.44, or at least that is what I wrote in my book (page 94).
But things change. I checking the behaviour in PeopleTools 8.48, and this is what I found:
Lets start with the Basic Search mode in the Record Locator Dialogue
The default operation in the basic search is 'begins with'. The search string is shorter than the field (11 characters in this case) and so we get a LIKE criteria, but there is a change here. The % has been put into the search string.
However, if I fill the search field with 11 characters I still get a LIKE, and most significantly I no longer get an equality condition.
However, in Advanced Search, I get exactly what I ask for.
'Begins with' always results in a LIKE condition, irrespective of the length of the search string.
'Contains' always produces a LIKE condition, with % symbols at both ends, again irrespective of the length of the search string.
If I specify '=', then I get an equality condition in the SQL, but the search criteria also becomes a bind variable in the SQL. This is different from all other cases, where the search criteria appears in the SQL as a literal value.
Only on a mixed case field, does PeopleSoft append the % in a separate string, because both the column and the search string are forced into upper case.
So does it matter that PeopleTools uses a LIKE instead of an equality? Sometimes it could, but not always. Here is a very simple test where I search for a single employee by the unique key columns, first with an equality
And here is the same search with a LIKE criteria
In this case, although both queries return the same result, it made the difference between a index unique scan and a single fetch operation, and an index range scan and a second fetch, requiring another consistent read. Although, I can also produce tests which produce the same execution plan and number of consistent reads for = and LIKE.
The LIKE operation does cause an additional filter operation, but this is only a small CPU overhead.
However, this does mean that the advice about having 5 character SETIDs and Business Units is no longer valid.