select statement problem

[Former Member]

Hi Experts,

The below code is taking the much time at the time of execution .

SELECT *
    INTO CORRESPONDING FIELDS OF TABLE my_caufv
    FROM caufv AS caufv
    INNER JOIN mara ON mara~matnr = caufv~plnbez
    WHERE caufv~werks  =  s_werks
      AND caufv~bukrs  IN s_bukrs
      AND caufv~plnbez IN s_plnbez
      AND mara~mtart   IN s_mtart
      AND mara~matkl   IN s_matkl
      AND caufv~aufnr  IN s_aufnr
      AND caufv~auart  IN s_auart
      AND caufv~aprio  IN s_aprio
      AND NOT EXISTS
         ( SELECT * FROM jest AS ord_jest
             WHERE ord_jest~objnr    =   caufv~objnr
               AND ord_jest~stat     IN  r_order_stat
               AND ord_jest~inact    =   ' ' )
      AND exists
         ( SELECT * FROM
              ( crhd  AS crhd  INNER JOIN afvc  AS afvc
                 ON crhd~objid  = afvc~arbid )
             WHERE afvc~aufpl  =   caufv~aufpl
               AND crhd~arbpl  IN  s_arbpl
               AND NOT exists
              ( SELECT * FROM jest AS oper_jest
                 WHERE oper_jest~objnr    =   afvc~objnr
                   AND oper_jest~stat     IN  r_wc_oper_stat
                   AND oper_jest~inact    =   ' ' )
         ).

Intead of the above code I have written the below code but the problem is for the above code I am getting 23 entries

but the below code I am getting the 24 entries. I tried to solve this but i didn't find any difference.

please help me on this.

SELECT *
      INTO CORRESPONDING FIELDS OF TABLE i_caufv
      FROM caufv AS caufv
      INNER JOIN mara AS mara
      ON mara~matnr = caufv~plnbez
      WHERE caufv~werks  =  p_werks
        AND caufv~bukrs  IN s_bukrs
        AND caufv~plnbez IN s_plnbez
        AND mara~mtart   IN s_mtart
        AND mara~matkl   IN s_matkl
        AND caufv~aufnr  IN s_aufnr
        AND caufv~auart  IN s_auart
        AND caufv~aprio  IN s_aprio.

  IF sy-subrc = 0.

    SORT i_caufv BY objnr.
    i_caufv_tmp[] = i_caufv[].
    DELETE ADJACENT DUPLICATES FROM i_caufv_tmp COMPARING objnr.

    SELECT objnr
           stat
           inact
           FROM jest
           INTO TABLE i_jest
           FOR ALL ENTRIES IN i_caufv_tmp
           WHERE objnr = i_caufv_tmp-objnr
           AND stat IN r_order_stat
           AND inact = ' ' .

    IF sy-subrc = 0.

      SORT i_jest BY objnr.

      i_jest_tmp[] = i_jest[].
      DELETE ADJACENT DUPLICATES FROM i_jest_tmp COMPARING objnr.

      LOOP AT i_jest_tmp.
        r_objnr-sign = 'I'.
        r_objnr-option = 'EQ'.
        r_objnr-low = i_jest_tmp-objnr.
        APPEND r_objnr.
        CLEAR r_objnr.
      ENDLOOP.

      DELETE i_caufv WHERE objnr IN r_objnr.

    ENDIF.

    if not i_caufv[] IS INITIAL.

      REFRESH i_caufv_tmp.
      SORT i_caufv BY aufpl.
      i_caufv_tmp[] = i_caufv[].
      DELETE ADJACENT DUPLICATES FROM i_caufv_tmp COMPARING aufpl.

      select afvc~aufpl
             afvc~arbid
             afvc~objnr
             crhd~objid
             crhd~arbpl
             from afvc as afvc
             inner join crhd as crhd
             on afvc~arbid = crhd~objid
             into table i3_afvc
             for all entries in i_caufv_tmp
             where afvc~aufpl = i_caufv_tmp-aufpl
             and crhd~arbpl in s_arbpl.

      if sy-subrc = 0.

        LOOP AT i3_afvc.
          r_aufpl-sign = 'E'.
          r_aufpl-option = 'EQ'.
          r_aufpl-low = i3_afvc-aufpl.
          append r_aufpl.
          clear r_aufpl.
        ENDLOOP.

        DELETE i_caufv WHERE aufpl IN r_aufpl.

        SORT i3_afvc BY objnr.

        i_afvc_tmp[] = i3_afvc[].
        DELETE ADJACENT DUPLICATES FROM i3_afvc COMPARING objnr.

        SELECT objnr
               stat
               inact
               FROM jest
               INTO TABLE i2_jest
               FOR ALL ENTRIES IN i_afvc_tmp
               WHERE objnr = i_afvc_tmp-objnr
                 AND stat IN r_wc_oper_stat
                 AND inact = ' ' .

        IF sy-subrc = 0.
          SORT i2_jest BY objnr.

          i2_jest_tmp[] = i2_jest[].

          DELETE ADJACENT DUPLICATES FROM i2_jest_tmp COMPARING objnr.

          LOOP AT i2_jest_tmp.
            r_objnr_tmp-sign = 'I'.
            r_objnr_tmp-option = 'EQ'.
            r_objnr_tmp-low = i2_jest_tmp-objnr.
            APPEND r_objnr_tmp.
            CLEAR r_objnr_tmp.
          ENDLOOP.

          DELETE i_caufv WHERE objnr IN r_objnr_tmp.
          SORT i3_afvc BY aufpl.
        ENDIF.

      ELSE.

        DELETE TABLE i_caufv.

      ENDIF.
    ENDIF.

thnx

Sam.

Edited by: Julius Bussche on Jan 7, 2009 9:30 PM

Code tags added and moved to better forum.

[Sathish]

1. Check which is the 24th record which should not be there

2. Check in the select query which condition fails and allows that 24th record

3. After doing that post the query and the reason maybe obvious also.

[SujeetMishra]

Hello Sam,

I never recommend anyone to use joins.

anyways, you first set breakpoint at your first select statement.n see which entry is coming extra.

you can manually see the records which one is extra and match that same single records with your all select statement's condition for both of your code.

Have a Nice Day.

Regards,

Sujeet

[Former Member]

>

> Hello Sam,
> 
> I never recommend anyone to use joins.
> 
> anyways, you first set breakpoint at your first select statement.n see which entry is coming extra.
> you can manually see the records which one is extra and match that same single records with your all select statement's condition for both of your code.
> 
> Have a Nice Day.
> 
> Regards,
> Sujeet

May I know why you never recommend anyone to use joins?

Is it because more difficult for you to understand a complex select statement with join if something goes wrong like Sam's problem?

What we should know is in the first select statement that Sam wrote is not only join select statement but it is also contain correlated non scalar subquery.

I'm not saying that this is not a good approach, however with the existence of this subquery in your select statement, you can have different result with a single variation in your data.

For example, if any of your records does not have its relation in the 2nd table that inner joined with the main table, that record will not be selected at all. This is why sometimes we use left outer join to avoid missing record due to no related data found in the 2nd table. Try check if any of such condition exist in your record.

Your 2nd approach may have the correct result since it is easier to ensure the correctness if you have split your one single complex select statement into several abap statement, however, performance wise, it is not necessarily the best. You have to do ST05 or performance trace SE30 to analyze whether your first select statement already make use of available indexes or not.

Like i've said in this forum before, people tend to avoid using join not because of its performance, but because it is easier to understand several statements than one complex long statement.

Compare both approach and see which one has the best performance.

Best performance does not always means you can execute the query in seconds. If your query involved large table such as JEST in here, most probably the best performance your query can get is in minutes.

Regards,

Abraham

[Former Member]

>

> Hello Sam,
> 
> I never recommend anyone to use joins.
> 

Hi Sujeet,

• why do you think JOIN's are part of the SQL ANSI standards?

• why does SAP support JOIN's in OPEN SQL?

• why they are one of the core component of relational theory ( and for every reporting system using relational databases: see market leader BusinessObjects and their universe constructs: they make billions with joining tables...)

The JOIN is surely the most frequently used semantic in the programming world if it comes to access data models in relational databases

So everyone uses joins (except you).

Don't discourage others to use them if you are not capable (or willing) to write a statement with JOINs.

Bye

yk

[Former Member]

Hi Abraham,

agree with you completly , except

>

> Like i've said in this forum before, people tend to avoid using join not because of its performance, but because it is easier to understand several statements than one complex long statement.
> 
> Abraham

I found the procedural spaghetti code not EASY to read, what do you think?

> Your 2nd approach may have the correct result since it is easier to ensure the correctness if you have split >your one single complex select statement into several abap statement, however, performance wise, it is not >necessarily the best.

i think to have a good knowledge of the data that has to be retrieved AND the SQL to be used

should be sufficient that one can ensure correctness also on the SQL statement level.

Up to now he has different results - wich is right and wich is wrong? Well , that's what you get paid for as a developer: to solve the problem.

[Former Member]

Hi ,

To Yukonkid, you're right, I should've reword my statement like this:

people tend to avoid using join not because of its performance, but because it is easier to debug several statements than one complex and long join statement if there's a problem.

Honestly, I need to fully focus and several time reading that logic in order to understands the second logic while with just reading it once, i can understands the 1st logic.

So, which result that is correct, 23 or 24?

You should now the correct result first before try to troubleshoot your program or else you will walk without knowing where you should go.

Regards,

Abraham

[Former Member]

Hi Sam,

besides the performane issue, your post is a good example how easy to read is the SQL statement and

how terrible to read the procedural spaghetti code you have developed out of it (and to make matters worse got different results and could not find a clue because of the complexity of the code).

I encourage you to stay with the SQL statement.

There is a important rule in SQL that if you can express a problem in one statement, do it!

You would analyze your SQL in ST05 (as mentioend) , find the root cause of the poor performance

and solve this instead of writing source code around the perf. issue (wich is not solved , though) and would be hard to maintain by other developers.

It MAY turn out that for the data you want to retrieve the performance would be ok (only you can know that),

it may that you must rewrite some parts of it so index support can become more efficient.

I.e. :

• the EXISTS is efficient only if an index can be supported by the inner (big) tables;

• the joins should be supported by existing indexes

• using proper join fields as demanded by the data model

• an effective filter should be defined to restrict the data set as much as possible.

But going from a smooth statement using algebra of sets to a procedural nightmare would let J.F. Codd turn over in his grave

bye

yk

[Former Member]

Coming back to the original question.

First do you really expect that it will only be used in one setting where it gets 23 or 24 records.

If yes, then buffer the result.

But I would expect no, and then you FOR ALL ENTRIES will not work!

You must check the ranges, they allow a lot of different settings, depending which ones are filled and

which not. Probably not all can be optimized, but the most important should be optimized.

I had once a similar statement a join of 5 tables which a NOT exists condition to the same join.

There it was necessary to split it into 3 branches depending on which conditions were actually used.

There it was even necessary to split the join into smaller join plus FOT ALL ENTRIES.

Performance improved dramatically, from many minutes to fractions of seconds, I never had similar improvements. But the performance was different for the branches.

You must carefully look into the data distribution, and into the use cases.

Siegfried