Consultium's Blog

I was asked to look at a slow performing form recently by a client, and so I first tried to replicate the performance problem they were experiencing. This was quite straight forward on their test system; simply open the form, go to the Customer Number field, enter the value 3801016 and press tab.

I did this and it sat happily waiting for 2 minutes 48 seconds when it finally went to the next field in the form. I closed the form, opened it and tried again with the same values and it went to the next field almost instantly. So that told me that it was likely to be a query, and the database had returned the value so quickly this second time because the data was sat in memory and the database didn’t need to go and get the data from the disks which is a slower than accessing memory.

So I traced the form action and tkprof’d it to see what was happening. The slow running query which took the majority of the time in the trace file looked like this:

SELECT
 party.party_name
,acct.cust_account_id cust_account_id
,acct.account_number account_number
,party.email_address email_address
,nvl(party.gsa_indicator_flag, 'N') gsa_indicator
FROM
 hz_parties party
,hz_cust_accounts acct
WHERE 1=1
AND acct.party_id = party.party_id
AND acct.status = 'A'
AND party.party_id = nvl(:blk_search.party_id, party.party_id)
AND EXISTS (
  SELECT
  NULL
  FROM
   oe_order_headers h
  ,oe_transaction_types_all t
  ,oe_transaction_types_all_dfv tdfv
  ,fnd_lookup_values_vl lv
  ,fnd_lookup_values_dfv lvdfv
  WHERE 1=1
  AND h.sold_to_org_id = acct.cust_account_id
  AND h.open_flag = 'Y'
  AND h.order_type_id = t.transaction_type_id
  AND t.rowid = tdfv.row_id
  AND tdfv.order_type = lv.lookup_code
  AND lv.lookup_type = 'XXSYK_KC_ORDER_TYPES'
  AND lv.rowid = lvdfv.row_id
  AND lvdfv.locator_prefix = 'Y'
)
ORDER BY
3 

What I noticed about this query was there was no bind variable to represent what the user had entered (customer number 3801016) first before validation. So it appeared to me that the LOV was doing a blind query and returning all the data and only then looking for the value 3801016. So I added a line to the LOV query in the form itself:

  AND acct_account_number like :blk_search.customer_number||’%’

When testing this now was much faster (less than a couple of seconds) as long as there was some data in this field; if left blank it would perform as poorly as before which isn’t surprising as there was no limiting criteria to use an index with.