P4210: QBE search on DOC is too slow compared to DOCO

Gov

Guest
Hello,

With P4210, we are experiences considerable time difference when we do search (using QBE) on DOC compared to DOCO. I think primary index on DOCO might be the difference. One of my colleague recommended that creating an index on DOC (only) worked with Oracle db earlier in his experience. I am not sure how QBE filtering works with index choosing. Is some one come across similar scenario and had a solution?

Appreciate your insight!

Thanks,
 
QBE filter is altering the WHERE clause on the select statement sent to the DB. An index where the DOC field is the first field in the index would do wonders.
 
QBE filter is altering the WHERE clause on the select statement sent to the DB. An index where the DOC field is the first field in the index would do wonders.
for F4211, we already have a key having DOC as first field. i think F42119 missing key that contains DOC. Will give a try.
 
Here is what causing - We have moderate record count in Detail table (around 8000) and considerable count in History table (around 2,777,000). the history is making the performance lag.

What i tried - Just selected a invoice number (DOC) that is only in history. the QBE query on DOC is slow (As expected as there is no key with DOC on sales history table). Selected a invoice number (DOC) that is only in Detail table. The QBE query is still slow. I expected better performance when i chooses detail only invoice number as there is a key with DOC.

Question I have - I really surprise how JAS code or QBE query logic picks up which key of a table to use based on QBE selection and which table key to choose (May be both tables will have DOC in the key but with different key combination). I am not so convinced with the theory that application performance increases with key creation.

Thanks,
 
Your observation of F42119 not having an index where DOC is the first key is your culprit. If DOC is a frequent means of querying in customer service inquiry at your site, you should create an index for F42119 that matches the F4211 index where DOC is the first key.

You need to understand that E1 isn't doing any "Key selection" based on QBE values (or any filtering fields for that matter). All E1 does is generate a SQL statement to send to the underlying DB. The DB will do all the work of determining the best way to extract the records.

Since that is a union view, to maximize performance, each table should have matching indexes for the fields you most frequently use to lookup data in that app. You should be convinced that app performance increases with key creation in this case.
 
Back
Top