topic Re: Select query performance improvement. in Application Development and Automation Discussions

Select query performance improvement.

Former Member — Mon, 01 Jul 2019 12:56:27 GMT

Hi,

As I am new to ABAP world, I needed some suggestion regarding an performance issue.

In my program there are multiple select single query hitting database where 100k of data are stored and taking 4-5 hours to fetch the result. I referred many blogs and googled and came to know that I can take all the data from database into internal table and fetch the data. But I am confused how to achieve it. Below is the code. Can anyone please guide me how to make the performance more faster and achieve the fetching in much lesser time. Here c_t_data is being populated with 100k's of records. and then many select single statements are being fired inside it.

LOOP AT c_t_data.
CLEAR: wa_za.

SELECT SINGLE matnr
INTO mara-matnr FROM mara
WHERE matnr = c_t_data-matnr
AND lvorm = space.
CHECK sy-subrc = 0.

SELECT SINGLE user4 waers
INTO (wa_za-yyuser4, wa_za-yywaers)
FROM aufk
WHERE aufnr = c_t_data-aufnr.

SELECT SINGLE kursk prsdt
INTO (wa_za-yykursk, wa_za-yyprsdt)
FROM vbkd
WHERE vbeln = c_t_data-vbeln
AND posnr = '000000'.

SELECT SINGLE kursk prsdt
INTO (wa_za-yykursk, wa_za-yyprsdt)
FROM vbkd
WHERE vbeln = c_t_data-vbeln
AND posnr = c_t_data-posnr.

SELECT SINGLE vbegdat venddat
INTO (wa_za-yyvbegdat, wa_za-yyvenddat)
FROM veda
WHERE vbeln = c_t_data-vbeln
AND vposn = '000000'.

SELECT SINGLE vbegdat venddat
INTO (wa_za-yyvbegdat, wa_za-yyvenddat)
FROM veda
WHERE vbeln = c_t_data-vbeln
AND vposn = c_t_data-posnr.

SELECT tj30t~txt04 INTO wa_za-yycontsts
FROM jest
JOIN jsto
ON jsto~objnr = jest~objnr
JOIN tj30t
ON tj30t~stsma = jsto~stsma
WHERE jest~objnr = c_t_data-objnr
AND jest~inact = space
AND tj30t~stsma = jsto~stsma
AND tj30t~estat = jest~stat
AND tj30t~spras = 'EN'.
ENDSELECT.

CLEAR wa_za-yyconroot.
MOVE c_t_data-vbeln(7) TO wa_za-yyconroot.

SELECT SINGLE knumv INTO vbak-knumv
FROM vbak
WHERE vbeln = c_t_data-vbeln.

SELECT SINGLE kbetr INTO wa_za-yykbetr
FROM konv
WHERE knumv = vbak-knumv and
KPOSN = '000000' and
KSCHL = 'ZDIS'.

SELECT vbeln parvw kunnr pernr parnr
FROM vbpa INTO TABLE itab_vbpa
WHERE vbeln = c_t_data-vbeln.

Thanks in advance,

Kumar.

Re: Select query performance improvement.

Former Member — Mon, 01 Jul 2019 13:06:35 GMT

Hi,

Why are

SELECT SINGLE kursk prsdt
INTO (wa_za-yykursk, wa_za-yyprsdt)
FROM vbkd
WHERE vbeln = c_t_data-vbeln
AND posnr = '000000'.

SELECT SINGLE kursk prsdt
INTO (wa_za-yykursk, wa_za-yyprsdt)
FROM vbkd
WHERE vbeln = c_t_data-vbeln
AND posnr = c_t_data-posnr.

And

SELECT SINGLE vbegdat venddat
INTO (wa_za-yyvbegdat, wa_za-yyvenddat)
FROM veda
WHERE vbeln = c_t_data-vbeln
AND vposn = '000000'.

SELECT SINGLE vbegdat venddat
INTO (wa_za-yyvbegdat, wa_za-yyvenddat)
FROM veda
WHERE vbeln = c_t_data-vbeln
AND vposn = c_t_data-posnr.

Repeated?

You should consolidate some separate single selects into a join.

Other than that you're using header lines which are obsolete, so I would suggest to find an experienced ABAP-er in your company to assist you in this task.

Kind regards, Rob Dielemans

Re: Select query performance improvement.

matt — Mon, 01 Jul 2019 13:11:12 GMT

Please use the code button when posting code.

You can replace:

SELECT tj30t~txt04 INTO wa_za-yycontsts
FROM jest
JOIN jsto
ON jsto~objnr = jest~objnr
JOIN tj30t
ON tj30t~stsma = jsto~stsma
WHERE jest~objnr = c_t_data-objnr
AND jest~inact = space
AND tj30t~stsma = jsto~stsma
AND tj30t~estat = jest~stat
AND tj30t~spras = 'EN'.
ENDSELECT.

with

SELECT single tj30t~txt04 INTO wa_za-yycontsts
FROM jest
JOIN jstoON jsto~objnr = jest~objnr
JOIN tj30tON tj30t~stsma = jsto~stsma
WHERE jest~objnr = c_t_data-objnr
AND jest~inact = space
AND tj30t~stsma = jsto~stsma
AND tj30t~estat = jest~stat
AND tj30t~spras = 'EN'.

It won't help performance, but it makes more sense. You can also merge the VBAK and KONV selects into one select with an inner join. Maybe some of the others. You should look at FOR ALL ENTRIES.

Where is c_t_data populated?

How much data is in the database tables being referenced?

Re: Select query performance improvement.

loyd_enochs3 — Mon, 01 Jul 2019 13:18:20 GMT

For the repetitious SELECTs (looking for the same record over and over again), you might consider building hashed ITABs for those tables and searching the ITABs first, then looking at database and saving the entries into the ITAB. For your example, this should be useful for the MARA-MATNR at least..

Re: Select query performance improvement.

Former Member — Mon, 01 Jul 2019 13:39:20 GMT

posnr/vposn = '0000000' is fetching for all items. Where as posnr/vposn = c_t_data-posnr is fetching for req order/item.

Re: Select query performance improvement.

loyd_enochs3 — Mon, 01 Jul 2019 13:43:37 GMT

Rahul,

But why would you do both? When you select for POSNR or VPOSN = zeroes, store the results in an ITAB, then look up individual items from the ITAB and not the database.

Re: Select query performance improvement.

Former Member — Mon, 01 Jul 2019 14:01:02 GMT

Loyd,

You are correct. But I think it is being done posnr/vposn = '000000' first to get at least one data into work area (wa_za is a work area) and then posnr/vposn = c_t_data-posnr to get required data. If c_t_data-posnr is not found then at least wa_za-yykursk/yyprsdt and wa_za-yyvbegdat/yyvenddat will have a value. Please correct me if I am wrong.

Re: Select query performance improvement.

loyd_enochs3 — Mon, 01 Jul 2019 14:29:20 GMT

Rahul,

I guess that works, but it still seems redundant to go to the database twice. If the result of the '000000' SELECT is stored within the program, then you will have either the specific line item or the "default" result to be used if no specific item exists.

But whoever did the functional requirements either didn't grasp that or couldn't explain it properly in the specs.

Re: Select query performance improvement.

DoanManhQuynh — Tue, 02 Jul 2019 01:24:33 GMT

1. your select with item number = 00000 and specific item number from internal table doesnt make sense. put it in 1 select with or condition or just select with document number.

2. loop and select is really poor performance. seem like c_t_data is a standard parameter from an enhancement you may try to sort it then use FOR ALL ENTRIES. if not, try to use join.

Re: Select query performance improvement.

rudramani — Tue, 02 Jul 2019 06:50:33 GMT

Hi Rahul,
I can see that you are using multiple select queries within a loop. As per SAP Standard you must try to decrease the use of Select queries in nested methods.

You can improve ABAP codes by using:

1. Select with Joins

2. Use ABAP CDS

3. Using an Internal Table wherever possible

All the above examples have implemented these concepts.

Re: Select query performance improvement.

dev_parbutteea — Tue, 02 Jul 2019 13:03:33 GMT

Hi,

1. First check if there are duplicates in the table c_t_data. if yes, do a copy and delete adj dups and use that for subsequent selects.

2. Remove all selects from loop.

Re: Select query performance improvement.

roberto_forti — Tue, 02 Jul 2019 14:17:57 GMT

Hi Rahul Kumar, Try to understand how transactions ATRA, ST12 and ST05 work to improve that as well as internal tables like Hashed, Sorted and Standard.

Regards, Roberto

Re: Select query performance improvement.

Former Member — Wed, 03 Jul 2019 08:59:57 GMT

Hi,

If I will remove all the select queries inside of the loop, then how would I implement it then?

Thanks.

Re: Select query performance improvement.

Sandra_Rossi — Wed, 03 Jul 2019 11:54:09 GMT

Rahul Kumar I guess Dev Parbutteea proposal was to use SELECT ... FOR ALL ENTRIES IN itab_copy_no_duplicates ... before the loop.

Re: Select query performance improvement.

Former Member — Tue, 09 Jul 2019 08:55:02 GMT

Hello,

Thanks for all your valuable feedbacks. I achieved the task.