topic Re: Performance issue in Select Query in Application Development and Automation Discussions

Performance issue in Select Query

yarnagula_sudhir — Wed, 23 Mar 2016 05:34:22 GMT

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.

Re: Performance issue in Select Query

Former Member — Wed, 23 Mar 2016 05:52:37 GMT

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').

Re: Performance issue in Select Query

shadab_maldar — Wed, 23 Mar 2016 05:58:37 GMT

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.

Re: Performance issue in Select Query

Ashg1402 — Wed, 23 Mar 2016 06:01:32 GMT

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

Re: Performance issue in Select Query

Former Member — Wed, 23 Mar 2016 06:05:56 GMT

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' ).

Re: Performance issue in Select Query

Former Member — Wed, 23 Mar 2016 08:14:48 GMT

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.

Re: Performance issue in Select Query

Former Member — Wed, 23 Mar 2016 15:51:18 GMT

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