Issue: A client was experencing slow response from a SQL script ran against the emPath employee benefit (BN3) table and a custom table. Research: The slow response was directly related to the custom table using VARCHAR field types while the BN3 table used CHAR field types. The workaround on the field type issue was to trim each column when referenced in the where clause. That workaround, along with the fact that columns in the where clause were part of an index, caused very slow response.
Resolution: The resolution is to use RPAD instead of TRIM. This results in a significant performance improvement.
Original where clause:
where trim(bn3.em_employee_id) = trim(es.em_employee_id)
and trim(bn3.bn_benefit) = trim(es.bn_benefit)
and trim(bn3.bn_coverage) = trim(es.bn_coverage)
Performance improving where clause:
where bn3.em_employee_id = rpad(es.em_employee_id,9)
and bn3.bn_benefit = rpad(es.bn_benefit,8)
and bn3.bn_coverage = rpad(es.bn_coverage,4)