topic Re: Performance improvement in select query in Application Development and Automation Discussions

Performance improvement in select query

vinay_pasalkar — Thu, 06 May 2010 11:37:08 GMT

Hi,

I have to improve the performance of the one custome program. I applied SQL trace & found out that below extract of code is taking maximum time.

SELECT vbak~vbeln

vbak~erdat

vbak~auart

vbak~augru

vbak~vkorg

vbak~vtweg

vbak~spart

vbak~kunnr

vbak~submi

vbak~knumv

vbuk~fksak

INTO CORRESPONDING FIELDS OF TABLE it_vbak

FROM vbak

INNER JOIN vbuk ON vbak_{vbeln EQ vbuk}vbeln

WHERE vbak~vbeln IN s_vbeln

AND vbak~vkorg EQ p_vkorg

AND vbak~vtweg EQ p_vtweg

AND vbak~spart IN s_spart

AND vbak~erdat IN s_erdat

AND vbak~kunnr IN s_kunnr

AND vbak~auart IN r_auart

AND vbak~augru EQ c_zcb

AND vbuk~fksak EQ c_c.

How can i use the for all entries in above code.. Will it enhance the performance of the code?

Re: Performance improvement in select query

Former Member — Thu, 06 May 2010 12:01:00 GMT

Hi,

I am not sure about you internal table structure

but usage of CORRESPONDING FIELDS OF TABLE

may burden the Database

Regards,

Raghava Channooru

Re: Performance improvement in select query

vinay_pasalkar — Thu, 06 May 2010 12:39:59 GMT

Hi,

Internal table has structure as follows

DATA : BEGIN OF it_vbak OCCURS 0,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

auart LIKE vbak-auart,

augru LIKE vbak-augru,

vkorg LIKE vbak-vkorg,

vtweg LIKE vbak-vtweg,

spart LIKE vbak-spart,

kunnr LIKE vbak-kunnr,

ship_to LIKE vbak-kunnr,

submi LIKE vbak-submi,

bstkd LIKE vbkd-bstkd,

bstdk LIKE vbkd-bstdk,

belnr LIKE ekbe-belnr,

cpudt LIKE ekbe-cpudt,

fksak LIKE vbuk-fksak,

fkdat LIKE vbrk-fkdat,

knumv LIKE vbak-knumv,

konda LIKE t188-konda,

obj_key LIKE nast-objky,

END OF it_vbak.

Please suggest way to improve the performance.

Thanks

Vinay

Re: Performance improvement in select query

Former Member — Thu, 06 May 2010 12:58:24 GMT

Hi,

Instead of a Join go with FOR ALL ENTRIES.

First fetch the data from VBAK into GT_VBAK

Then do the for all entries on VBUK based on the entries in GT_VBAK

Note: Please have all the fields in the where conditions in the same order as they are in the tables...for better performance

OR..

Search a VIEW which has both the tables VBAK/VBUK..

Hope this helps

Regards

Shiva

Re: Performance improvement in select query

Former Member — Thu, 06 May 2010 13:11:22 GMT

Vinay and Shiva - have you not read ?

The problem is probably entirely due to the contents of the SELECT-OPTIONS.

Rob

Re: Performance improvement in select query

Former Member — Fri, 07 May 2010 13:09:33 GMT

Suggest you take the time to review SAP Note 185530 regarding customer SD programs. That would lead you to alternative sources for data selection, such as table VAKPA, VAPMA, etc. (but these must be turned ON in config). You may be able to improve performance in all your SD/LE programs by using the suggestions in that SAP Note.

There are similar notes for other modules.

Anyone who reports SD/LE with ABAP should read the note.

Re: Performance improvement in select query

Former Member — Mon, 10 May 2010 10:55:23 GMT

Can you give it a try?

Create an itab with the required fields and rewrite the query removing corresponding clause. AFter the data is fetched move the data from this small itab to your main itab by small_itab1-field1 = main_tab-field1. Then blow up the small itab to free the memory.

Re: Performance improvement in select query

vinay_pasalkar — Tue, 11 May 2010 07:24:47 GMT

Hi,

It's good option to select the data from first table & then select data from second table using for all entries .

However don't you think combined ttime taken to execute two select quries will be same as time to execute single select query above.

Thanks

Vinay

Re: Performance improvement in select query

Former Member — Tue, 11 May 2010 10:39:03 GMT

same, same same ...

the INTO CORRESPONDING is o.k., and the JOIN is mandatory!!!


WHERE vbak~vbeln IN s_vbeln
AND vbak~vkorg EQ p_vkorg
AND vbak~vtweg EQ p_vtweg
AND vbak~spart IN s_spart
AND vbak~erdat IN s_erdat
AND vbak~kunnr IN s_kunnr
AND vbak~auart IN r_auart
AND vbak~augru EQ c_zcb
AND vbuk~fksak EQ c_c.

Only Rob is correct. There are 5 IN clauses !!!, they are dynamic, they can be empty, they can be filled which selective options or unselective options. This statements has finitely many different versions and not all can be optimized.

What is the content of the select-options in your example? Which ones are empty?