Record Locator Dialogue Behaviour

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:

SELECT DISTINCT SETID ... WHERE SETID LIKE 'ABCD' || '%' ESCAPE '\' ...
ORDER BY SETID ...

If you search for a string equal in length to the field you get an equality (unless you specify something else in advanced search).

SELECT DISTINCT SETID ... WHERE SETID = 'ABCDE' ... ORDER BY SETID ...

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.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '1%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

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.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

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.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

'Contains' always produces a LIKE condition, with % symbols at both ends, again irrespective of the length of the search string.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID LIKE '%11111111111%'
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS

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.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1 AND EMPLID=:2
ORDER BY EMPLID
Bind-1 type=2 length=2 value=PS
Bind-2 type=2 length=1 value=1

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.

SELECT DISTINCT EMPLID, NAME, LAST_NAME_SRCH, SECOND_LAST_SRCH, NAME_AC
FROM PS_PERALL_SEC_SRCH
WHERE OPRID=:1
AND UPPER(NAME) LIKE UPPER('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') || '%' ESCAPE '\'
ORDER BY NAME, EMPLID
Bind-1 type=2 length=2 value=PS

Implications

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

SELECT emplid FROM ps_personal_data WHERE emplid = 'SFCH00034'
/

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PS_PERSONAL_DATA |     1 |     8 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("EMPLID"='SFCH00034')

Statistics
----------------------------------------------------------
         2  consistent gets

And here is the same search with a LIKE criteria

SELECT emplid FROM ps_personal_data WHERE emplid LIKE 'SFCH00034%'
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     8 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PS_PERSONAL_DATA |     1 |     8 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("EMPLID" LIKE 'SFCH00034%')
      filter("EMPLID" LIKE 'SFCH00034%')

Statistics
----------------------------------------------------------
         3  consistent gets

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.