Suggest me less expensive SELECT for aggregate function.

[Former Member]

Hi,

I hv a SELECT statement with AGGREGATE function, but, as aggregates are expensive, so, want to replace it with less expensive SELECT statement, so, pls. let me know it, Wht can i use instaed, code is as below,

 CLEAR my_amount.
  SELECT SUM( wrbtr ) into my_amount from BSID
                          WHERE bukrs = p_bukrs
                          and   kunnr = p_konto
                          and   budat <= sy-datum. 

so, pls. let me know alternative(replace) SELECT which is less expensive performence wise

thank you

[former_member194669]

Try something this way

SELECT bukrs kunnr budat wrbtr into i_temp from BSID
WHERE bukrs = p_bukrs
and kunnr = p_konto
and budat <= sy-datum.

loop at i_temp.
 v_wrbtr = v_wrbtr + i_temp-wrbtr.
endloop.

my_amount = v_wrbtr.

a®

[Former Member]

Hi,

It would be better if you select the data into an internal table & calculate the sum.

Regards

Abhii

[christine_evans]

>

> Hi,
> 
> I hv a SELECT statement with AGGREGATE function, but, as aggregates are expensive, so, want to replace it with less expensive SELECT statement, so, pls. let me know it, Wht can i use instaed, code is as below,
> 
> 
 CLEAR my_amount.
>   SELECT SUM( wrbtr ) into my_amount from BSID
>                           WHERE bukrs = p_bukrs
>                           and   kunnr = p_konto
>                           and   budat <= sy-datum. 
> 
> so, pls. let me know alternative(replace) SELECT which is less expensive performence wise
> 
> thank you

I don't think using SUM is a bad thing to do if you're using an index. The only replacement would be to select all the data into an itab and add it up yourself and I don't think that doing this would be any quicker - it might even be slower because you would be bringing back more records rather than restricting the number of records brought back at database level which is what the SUM would do. The thing I'd be concerned about here is that you're not taking acount of the SHKZG debit / credit flag. But if all the lines you're dealing are credits or all are debits that won't matter to you.

[Former Member]

Thank you.

You said: Even though its AGGREGATE function, using INDEX over rides the bad performence.

Me: But, am not using index in the above mentioned SELECT, so, guess, aggregate function makes my select expensive, right?

And I guess, when we use aggegate function then, all the calculation part will be done on data base server, where as if i use ARS suggested SELECT, in that case, all the calculations will be done on application server, right? so, load on DB server become less, right?

You said:

> The thing I'd be concerned about here is that you're not taking acount of the SHKZG debit / credit flag. But if all the lines you're dealing are credits or all are debits that won't matter to you

Me: Yes, I have double to check with functional guy, ur right, it might b a mistake. But u also said that-that won't matter to you----i guess, it matters!! bcoz, in my SELECT i did not mentioned any where that, all are DEBITS/CREDITS, so, guess, not using SHKGZ is a mistake!! or what do u mean, that won't matter to you

Thank you

Moderator message - Please do not use code tags when quoting - it messes up page formatting

Edited by: Rob Burbank on Nov 27, 2009 2:52 PM

[Former Member]

Well, you are using the main part of the primary index (BUKRS and KUNNR) so it shouldn't be a problem. But even if you weren't using the index, I doubt if there would be much of a difference. If you are summing a lot of data, it will take time either way.

But you should take care of logic before worrying about performance.

Rob

[christine_evans]

You are using an index so using SUM should be fine - unless you are selecting a very large number of records in which case any sort of SELECT will be slow. Getting the database server to do the work is ok in my view.

I would always try to use SHKZG when summing from FI line item tables. But of course if you do this in open SQL you are not going to be able to use a SUM anyway; you could do it in real SQL by using a decode but this is generally not advised (though I've done it).

I think you should stick to the simple method. Select all the records you want to sum and then add them up using ABAP taking account of SHKZG.