select jsto jest optimisation

[marco_sposa]

Hello Folks,

i'm trying to optimize a select really poor on performance, any idea on how to change the join in order to have a better performance please?

 SELECT objnr FROM jsto AS a APPENDING CORRESPONDING FIELDS OF

                      TABLE onr_tab WHERE a~objnr IN r_s_objnr AND

                                          a~obtyp EQ sdcon-item AND

                                          a~stsma NE stsma_ini AND

             EXISTS ( SELECT objnr FROM jest WHERE objnr EQ a~objnr AND

                     stat LIKE 'E%' AND inact EQ space ) ORDER BY OBJNR.

[marco_sposa]

Hello Folks,

i'm trying to optimize a select really poor on performance, any idea on how to change the join in order to have a better performance please?

 SELECT objnr FROM jsto AS a APPENDING CORRESPONDING FIELDS OF

                      TABLE onr_tab WHERE a~objnr IN r_s_objnr AND

                                          a~obtyp EQ sdcon-item AND

                                          a~stsma NE stsma_ini AND

             EXISTS ( SELECT objnr FROM jest WHERE objnr EQ a~objnr AND

                     stat LIKE 'E%' AND inact EQ space ) ORDER BY OBJNR.

[RaymondGiuseppi]

Are views V_STAT or V_JSTO_JEST active in your database, else did you check access plan from a SQL trace?

[marco_sposa]

yes they are both active in the database.

i will start using them and may changing the query by doing 2 selects then using two selects instead of one ( using for all entries ).

Am i in the good direction?

[matt]

"then using two selects instead of one ( using for all entries ). Am i in the good direction?"

No. That's exactly the wrong thing to do. Don't do it.

[Jean_Sagi]

I really don't know JSTO, not JEST, but it looks like you can use some kind of JOIN instead of using EXISTS.

J.

[Sandra_Rossi]

Please attach the Execution Plan to your question, otherwise how can you know where the issue is!? (aka access plan as said Raymond)

[marco_sposa]

thanx you all by using the view V_JSTO_JEST (and the program logic) i solved the issue, the performance is now acceptable

[Sandra_Rossi]

thx for the feedback ! please use the option to convert your "answer" into a comment, under Raymond's answer