Poor performance in a form due to wrong used index

antoine_mpo

Reputable Poster
Hi List,

We created a custom interactive application with a form, based on a custom view. This view is based on the F41021 (Item Location File) and the F4108 (Lot Master), including both primary keys, with a join on MCU, ITM and LOTN.
This form is mainly used to search by lot number (LOTN), most of the time only with the begining of the LOTN.
The problem is that such search has really poor performance !!
I took a look at it.
When looking at the jdedebug of my fat client, a request is taking more than 30 seconds. If i execute exactly the same request in an sql editor, it takes something like 0,03 seconds !!
With Toad i look at the explain plans. There are totally different for the same request in an sql editor and in jde.
In the sql editor, it uses the index #4 of the F41021 and then the primary key of the F4108. In jde, it process a fast full scan of the index #5 (contains more fields and is less efficient) and after process a full scan of the table F4108 !

Does anyone knows how is working JDE in that case and if it's possible to improve performance by using the good indexes ?

Thanks for your help.
 
You could try creating a stored outline, example from the Oracle 8i reference guides below:

CREATE OUTLINE salaries FOR CATEGORY special
ON SELECT ename, sal FROM emp;

You need to do this with DBA or at least "Creat Any Outline" system privilege.

Part of the disadvantage of doing this, I believe, is that you can't input SQL statements that use Bind variables. I believe they need to be literals.
 
Hi Charles,

Can you give me some further information and that "Create Outline" statement ? What information should it gives me ?
I look at the Oracle Documentation but it's not really clear to me.
I did the following statement :
"CREATE OUTLINE pbF41021 FOR CATEGORY special
ON SELECT t0.liitm, t0.limcu, t0.lilocn, t0.lilotn, t0.lipqoh, t1.iolotn,
t1.iomcu, t1.ioitm, t1.iolitm, t1.iolot1, t1.iolot2, t1.iolot3
FROM indta.f41021 t0, indta.f4108 t1
WHERE (t0.lilotn LIKE '4044400%')
AND (t0.liitm = t1.ioitm AND t0.limcu = t1.iomcu
AND t0.lilotn = t1.iolotn
)
ORDER BY t1.iolitm ASC, t0.limcu ASC, t0.lilotn ASC"
It says it's done, but after that, where to i see informations when i submit the defined sql request ??
What should it do ?

Thanks,
 
Hi List,

I have some new interesting facts about my issue.
During some tests on my custom application, i noticed that if on my form i only used fields of 1 of the 2 tables of my view (F41021 or F4108), the response time with immediate (and indexes well used). But if i add any fields of the other table, the performance went bad and indexes were not well used.

I also find that :
The statisctics (oracle statistics, generated with "analyze table .... compute statistics") on my 2 tables were recent.
I tried to delete them.
If i delete only for one the 2 tables, the performance get poorer. But if i delete statistics on both, it solves the performance issue on my custom application !! The response time is immediate and the indexes used are the same as if i was using my sql editor !!
Strange isn't it ?

But i got to check that delete statistics doesn't put the mess in other applications or batch.

If someone as any idea of what's going on, i would be grateful !

Thanks,
 
Back
Top