Performance issue in Select Query

[yarnagula_sudhir]

Hi All,

Kindly find the below Select Query and let me know if any performance issue or it can be written in any other better way.

I want combination of Document Type JV and SA with TCode FB01 and FB05, with respective selection screen inputs bukrs, gjahr and budat.

select bukrs

          belnr

          gjahr

          blart

          budat

          usnam

          tcode from bkpf

                into table g_tab_bkpf

                where ( ( bukrs eq p_bukrs ) and ( gjahr eq p_year ) and

                        ( blart eq 'JV' ) and ( budat in s_pstdt ) and ( tcode eq 'FB01' ) or

                        ( bukrs eq p_bukrs ) and ( gjahr eq p_year ) and

                        ( blart eq 'JV' ) and ( budat in s_pstdt ) and ( tcode eq 'FB05' ) or

                        ( bukrs eq p_bukrs ) and ( gjahr eq p_year ) and

                        ( blart eq 'SA' ) and ( budat in s_pstdt ) and ( tcode eq 'FB01' ) or

                        ( bukrs eq p_bukrs ) and ( gjahr eq p_year ) and

                        ( blart eq 'SA' ) and ( budat in s_pstdt ) and ( tcode eq 'FB05' ) ).

With Regards,

Sudhir.

[Former Member]

SELECT bukrs

        belnr

        gjahr

        blart

        budat

        usnam

        tcode

   FROM bkpf

   INTO TABLE g_tab_bkpf

   WHERE bukrs EQ p_bukrs

     AND gjahr EQ p_year

     AND blart IN ('JV', 'SA')

     AND budat IN s_pstdt

     AND tcode IN ('FB01', 'FB05').

[Former Member]

This solution, I would prefer because it's easier to read. If your DB has a performance optimizer, I guess, you get the same performance result.

The important thing for a good performance is your indexes on the table.

[shadab_maldar]

Hi Yarangula,

Why to make selection so complex .

You can also validate your data after getting it into internal table.

According to me the query should be.

select bukrs belnr gjahr blart budat usnam tcode from bkpf into table gt_bkpf

where bukrs  in s_bukrs and

           gjahr  in s_gjahr  and

           blart in ('JV' , 'SA') and

           budat in s_pstdt and

           tcode in ('FB01' , 'FB05').

After getting the data into gt_bkpf you can loop it, apply the condition you want and get the data into the final itab.

Note: Make use of select options instead of parameters in your selection screen(You can use no-extension and no interval to display select option as parameter in selection screen).

Regards,

shadab.

[Ashg1402]

Hi sudhir,

     the query which you have written contains many repetitive fields. I will suggest you to write it in this way -

select bukrs

          belnr

          gjahr

          blart

          budat

          usnam

          tcode from bkpf

                into table g_tab_bkpf

                where ( ( bukrs eq p_bukrs ) and ( gjahr eq p_year ) and ( budat in s_pstdt ) and ( ( blart eq 'JV' )                 or ( blart eq 'SA' )) and  (( tcode eq 'FB01' ) or ( tcode eq 'FB05' )) ).

Try this , its more clear , you can also create range for tcode and blart.

Regards

Ashish

[Former Member]

SELECT bukrs belnr gjahr blart budat usnam tcode FROM bkpf       

     INTO TABLE g_tab_bkpf       

     WHERE bukrs EQ p_bukrs AND gjahr EQ p_year AND blart IN ( 'JV', 'SA' ) AND                     

                   budat IN s_pstdt AND tcode IN ( 'FB01', 'FB05' ). 

[Former Member]

It's actually quite simple - you just have to add "AND BSTAT EQ ' ' " to the SELECT.

This will allow you to use index BKPF~2 (BUKRS, BSTAT and BUDAT).

I wrote a blog on this years ago:

(Unfortunately, the code formatting came apart with the migration to the new SCN. But you should be able to figure it out.)

Rob

Message was edited by: Rob Burbank