topic Re: select jsto jest optimisation in Application Development and Automation Discussions

select jsto jest optimisation

marco_sposa — Wed, 23 Jan 2019 13:31:33 GMT

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.

Re: select jsto jest optimisation

RaymondGiuseppi — Wed, 23 Jan 2019 14:32:59 GMT

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

Re: select jsto jest optimisation

marco_sposa — Wed, 23 Jan 2019 14:56:44 GMT

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?

Re: select jsto jest optimisation

Jean_Sagi — Wed, 23 Jan 2019 20:57:49 GMT

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

Re: select jsto jest optimisation

Sandra_Rossi — Wed, 23 Jan 2019 20:59:35 GMT

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

Re: select jsto jest optimisation

marco_sposa — Sun, 27 Jan 2019 12:11:01 GMT

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

Re: select jsto jest optimisation

Sandra_Rossi — Sun, 27 Jan 2019 12:58:59 GMT

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

Re: select jsto jest optimisation

matt — Sun, 27 Jan 2019 16:28:06 GMT

"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.