SQL query to search for pers based on SSN/TaxID is slow

I need to search for a person and his accounts etc based on his SSN/TAXID. The SQL statement seems very slow (20-30s). I am wondering what is the efficient way for such a simple search. Interestingly, searching for ORG does not seem to have the same performance issue.

Here is the SQL. The function is used because the SSN is encrypted.

select * from pers where pack_OSI_UTIL.func_FORMATTAXID( TaxId, 'PERS', 'N') = ‘xxx-xx-xxxx’

Thanks,

RFCU

Rich,
TaxIds were encrypted as of release 2005.1 several years ago and there is a section in 2005.1 Release doc that discusses how to work with encrypted Ids. I am sending you the doc via FTP (it is too large to attach here) -- please see pp 294-314. It refers to bank option AHTX and application setting for AHUS appl. You need to make appropriate settings in order to access encrypted ids from outside Core. When you have made this setup, you should be able to use following solution:

Your SQL decrypts each row in the table and compares it to supplied target value. This makes the search very long. In contrast, following SQL first excrypts your target taxId value and then finds it in the table. So it runs significantly faster:

In place of
select * from pers where pack_OSI_UTIL.func_FORMATTAXID( TaxId, 'PERS', 'N') = 'xxx-xx-xxxx' -- 77 sec in my db
Use
select * from pers where taxid=pack_taxid.func_settaxid('xxxxxxxxx',null) -- 0.05 sec in my db

--Raju