topic Re: optimization select query... in Application Development and Automation Discussions

optimization select query...

former_member192432 — Sat, 10 Oct 2009 09:13:47 GMT

Hi..

The below select query is causing performance issue in production , it is taking much time.

Please suggest how to optimize it .

SELECT belnr bukrs gjahr awkey blart FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN s_bukrs AND

gjahr IN r_year.

Regards

chetan

Moderator message - Please see before posting - post locked

Edited by: Rob Burbank on Oct 13, 2009 10:49 AM

Re: optimization select query...

BGarcia — Sat, 10 Oct 2009 10:45:52 GMT

Hi Chetan,

I suggest the following to optimize your performance.

- Change the order of the fields (belnr bukrs gjahr awkey blart), so that they're in the same order than showned in SE11 table data definition (you may need to change the order in t_bkpf definiton also).

- Add the client in your select statement.

Something like this:


SELECT bukrs belnr gjahr blart awkey FROM bkpf CLIENT SPECIFIED
INTO TABLE t_bkpf
WHERE mandt = sy-mandt
AND bukrs IN s_bukrs 
AND gjahr IN r_year.

Your statement it's very simple. I don't think that there is much more to do.

Hope that helps.

Kind regards.

Edited by: Bruno Garcia on Oct 10, 2009 10:48 AM

Re: optimization select query...

Former Member — Sat, 10 Oct 2009 16:42:19 GMT

Hi Chetan,

I think your code is perfect..as Bruno replied there is nothing much to change because it will use primary index of BKPF table...

Only one thing you need to check is that make sure that S_bukrs and R_year have values..

Hope this reply helps you..:)

Regards

Basavaraj

Re: optimization select query...

Former Member — Mon, 12 Oct 2009 05:44:25 GMT

Hi Chetan,

Try to give maximum possible key fieds in where condition.If possible give BELNR(Accounting document number) also in where condition.Otherwise u can go with Bruno statement.

Thanks & Regards,

Pydi Reddy.

Re: optimization select query...

Former Member — Mon, 12 Oct 2009 06:53:38 GMT

> - Add the client in your select statement.

Did you ever hear about the automatic client addition? This recommendation is nonsense! and should not be done!

Re: optimization select query...

Former Member — Mon, 12 Oct 2009 06:56:01 GMT

the problem is related to the select-options!!!

> WHERE bukrs IN s_bukrs AND

> gjahr IN r_year.

Be aware that these are dynamic conditions, only at runtime they aree filled, and they can be empty (non-selective) or

very limiting. So you must check the actual content if you want to figure out the cause of your problem.

Siegfried

Re: optimization select query...

Former Member — Tue, 13 Oct 2009 14:17:24 GMT

Hi chetan teli,

Try this:

SELECT belnr bukrs gjahr awkey blart

FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN s_bukrs

AND belnr GT 0

AND gjahr IN r_year.

Add belnr in the Where condition to use the full primary key.

Regards,

José

Re: optimization select query...

christine_evans — Tue, 13 Oct 2009 14:47:04 GMT


> Hi chetan teli,
> 
> Try this:
> 
> SELECT belnr bukrs gjahr awkey blart 
>    FROM bkpf
>      INTO TABLE t_bkpf
>   WHERE bukrs IN s_bukrs
>        AND belnr GT 0
>        AND gjahr IN r_year.
> 
> Add belnr in the Where condition to use the full primary key.
> 
> Regards,
> 
> José

Doing this is not going to make a lot of difference to the amount of data the query will have to search through if, like us, you have a very small number of BUKRS values.