Record Group too slow on forms6i

[danmul]

Hi,

I have an LOV using a record group that I am trying to use as to validate against a supplier ID or a supplier name. I have over 40,000 records. It was returning in about 2 seconds when I had only validate against ID. Now my first column uses concatenation for 3 columns. The query takes between 10 and 20 seconds to return. Does anybody know how I can speed this up. I will copy record group below.

Thanks,
Daniel.

select lpad(to_char(supplier_id),6,'0')
||' '||supplier_name||decode(trading_name, null, null, ' trading as'||trading_name) supplier,
mail_city,
mail_state,
supplier_id,
supplier_abn,
supplier_name,
trading_name
from aps_supplier
where active_flag = 'A'
order by supplier_id

 

[sem]

Don't validate against record group if it's quite big! When you check single ID the wole query is executed on server (most probably using index). When you validate on client side, the whole set of 40 000 records must be fetched and then checked in a loop. This is quite complex operation as it loads network, client memory and processor.

Regards, Dima

http://seminihin.narod.ru

 

[danmul]

Hi,

Thanks for reply. One of the standards that I have to stick to is all LOV's have to be validated from the list. For now I have changed my LOV so that it reads like this: select supplier_id, supplier_name . . . But this only allows the user to search by supplier_id using text_field. This is takes 2-3 seconds which is OK for now until they need to search using supplier name also.
Thanks,
Daniel.

 

[lewisp]

If you place %<suppliername>% into the LOV find box, it will search on the name as well as the ID.