topic Re: Query Optimization in Application Development and Automation Discussions

Query Optimization

Former Member — Mon, 12 Sep 2005 03:34:13 GMT

i have a report which is very slow after analysing it the following querys is making the report very slow,

if any body can tell me how to optimize the following query...

 LOOP AT i_data.
    SELECT SUM( a~fkimg )
     FROM vbrp AS a
       INNER JOIN vbrk AS b ON
        a~vbeln = b~vbeln
      INTO i_data-fkimg
     WHERE a~aubel = i_data-vbeln AND
           a~aupos = i_data-posnr AND
           b~fksto = ' ' AND
           b~vbtyp = 'M'.
  ENDLOOP.

thanx

abhishek suppal

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 03:41:10 GMT

Instead of doing a SUM on the database table, it would be better to do a COLLECT on i_data.


LOOP AT i_data.
    SELECT a~fkimg FROM vbrp AS a
             INNER JOIN vbrk AS b 
                     ON a~vbeln = b~vbeln
                   INTO i_data-fkimg
                  WHERE a~aubel = i_data-vbeln 
                    AND a~aupos = i_data-posnr 
                    AND b~fksto = ' ' 
                    AND b~vbtyp = 'M'.
    COLLECT i_data. "assuming i_data has no other numeric fields.
ENDLOOP.

Srinivas

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 03:55:51 GMT

Hi Abhishek.

Doing a select within a loop is going to be costly.

Moreover, the first selection has to be from vbrk and not vbrp...

Try this code...

data:

begin of tab occurs 0,

vbeln type vbeln,

posnr type posnr,

fkimg type vbrp-fkimg,

end of tab.

if i_data[] is not initial.

SELECT a~fkimg

FROM vbrk AS b

INNER JOIN vbrp AS a ON a_{vbeln = b}vbeln

INTO table tab

for all entries in i_data

WHERE a~aubel = i_data-vbeln

AND a~aupos = i_data-posnr

AND b~fksto = ' '

AND b~vbtyp = 'M'.

endif.

tab will have document/item and qty.

You can sum this and put it back into i_data.

You can not use for all entries with sum and hence you need to do it this way.

I am sure this will be a lot of performance optimization.

Tyr adn let me know.

P.S> Remember to reward points and close this post if this solves ur problem.

Rgds,

Prash.

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 04:02:21 GMT

Hi Abishek,

You can sort based on the required field and delete adjacent duplicates and then put it in a loop.This can improve performance considerably.

Regards,

Latha Prabhu.

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 04:33:48 GMT

Hi,

try with this

data : i_data1 like vbrp-fkimg occurs 0 with header line.

sort i_data by vbeln posnr.

delete adjacent duplicates from i_data comparing vbeln posnr.

if not i_data[] is initial.

SELECT b~fkimg INTO table i_data1

FROM vbrk AS a

INNER JOIN vbrp AS b ON

a_{vbeln = b}vbeln

for all entries in i_data

WHERE

a~fksto = ' ' AND

a~vbtyp = 'M' and

b~aubel = i_data-vbeln AND

b~aupos = i_data-posnr.

endif.

if i_data1[] is not initial.

do the sum in i_data1

endif.

Cheers,

Sasi

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 05:10:57 GMT

Hi Abi,

Firstly, there is a mistake in the code that you have posted. After you select the sum ( a~fkimg ) into i_data-fkimg you are not modifying the table i_data.

Assuming that it was typo.

My solution is use select endselect instead of writing code to first select from vbak,vbap and then select from vbrk, vbrp.

try this one out.

SELECT * UP TO 10 ROWS FROM VBAP.

SELECT B~fkimg FROM vbrK AS a

INNER JOIN vbrP AS b

ON a_{vbeln = b}vbeln

INTO V_fkimg

WHERE B~aubel = VBAP-vbeln AND

B~aupos = VBAP-posnr AND

A~fksto = ' ' AND

A~vbtyp = 'M'.

IF SY-SUBRC = 0.

TAB-FKIMG = TAB-FKIMG + V_FKIMG.

ENDIF.

ENDSELECT.

TAB-VBELN = VBAP-VBELN.

TAB-POSNR = VBAP-POSNR.

APPEND TAB.

CLEAR TAB.

CLEAR V_FKIMG.

ENDSELECT.

and let me know.

Bye,

SAP.

Re: Query Optimization

Former Member — Mon, 12 Sep 2005 06:50:02 GMT

thanx all for ur replies any ways the solutions u have give are correct one, i have tried thes, but still its slow, is there no other query from which i can find the Sale Order and Billing document Link, acessing vbrp table takes lot of time. is there any other link by which i can know the Billing document no. against the Sale Order. if so then tell me..

abhishek suppal

Re: Query Optimization

former_member221770 — Mon, 12 Sep 2005 07:05:09 GMT

Abhi,

try using table VBFA. This is the document flow. It will show the relationships between the Sales DOcuments.

Enter in your Sales Order Number into VBELV and make sure VBTYP_N = 'M' and VBTYP_N = 'C'.

CHeers,

Pat.

PS. Kindly assign Reward Points to the posts you find helpful.

Re: Query Optimization

Former Member — Tue, 13 Sep 2005 03:57:39 GMT

thanx Pat,

it solved the problem...

abhishek suppal