topic Re: Performance Issue:Select statement in Application Development and Automation Discussions

Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 08:42:08 GMT

Hello Experts,

Plz suggest how can i improve the performance of both these queries.

select a_{belnr a}gjahr a_{bldat a}gsber a_{blart a}budat a_{dmbtr a}xblnr

a_{shkzg a}werks a_{gsber a}bukrs

into corresponding fields of table i_bsis_main

from bsis as a

where a~bukrs eq p_bukrs and

a~hkont eq p_hkont and

a~budat <= s_budat and

( a~belnr in s_fi_doc ).

***************************************************************

select a_{belnr a}gjahr a_{bldat a}gsber a_{blart a}budat a_{dmbtr a}xblnr

a_{shkzg a}werks a_{gsber a}bukrs

into corresponding fields of i_bsis_main

from bsas as a

where a~bukrs eq p_bukrs and

a~hkont eq p_hkont and

a~budat <= s_budat and

a~augdt > s_budat and

a~augbl <> ' ' and

( a~belnr in s_fi_doc ).

append i_bsis_main.

endselect.

PLz suggest.

Ravi

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 08:52:15 GMT

This message was moderated.

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 09:39:48 GMT

Hi Ravi,

Insted of INTO CORRESPONDING use the the table of the same structure with the same sequence .

data : begin of  fs_bsis ,
            belnr type bsis-belnr,
            gjahr type bsis-gjahr,
            bldat type bsis-bldat,
            gsber type bsis-gsber,
            blart type bsis-blart,
            budat type bsis-budat ,
            dmbtr type bsis-dmbtr,
           xblnr type bsis-xblnr,
           shkzg  type bsis-shkzg ,
           werks type bsis-werks,
           gsber type bsis-gsber,
           bukrs type bsis-bukrs,
        end of fs_bsis.
data : i_bsis like table of fs_bsis.

select a~belnr a~gjahr a~bldat a~gsber a~blart a~budat a~dmbtr a~xblnr
a~shkzg a~werks a~gsber a~bukrs
into  table i_bsis_main                                          " INTO CORRESPONDING IS REMOVED
from bsis as a
where a~bukrs eq p_bukrs and
a~hkont eq p_hkont and
a~budat <= s_budat and
( a~belnr in s_fi_doc ).

This applies for both the select queries.

For further performance improvement you need to maintain some more things , like ---

instead of <= in select query fetch records in the internal table then imply this condition in the internal table.

The sequence of the fields in the query should as the database table .

Regards

Pinaki

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 09:50:21 GMT

Hi,

Try like this

select burks GJAHR BELNR BLDAT bLART SHKZG GSBER dmbtr werks from bsis

into table i_bsis_main

from bsis

where bukrs eq p_bukrs and

a~hkont eq p_hkont and

( a~belnr in s_fi_doc ) and

a~budat <= s_budat.

select burks GJAHR BELNR BLDAT bLART SHKZG GSBER dmbtr werks from bsis

into table i_bsis_main1

for all entries of i_bsis_main

where burks = i_bsis_main-burks and

hkont = i_bsis_main-hkont and

budat = i_bsis_main-budat and

belnr = i_bsis_main-belnr and

augdt > s_budat and

augbl ' '.

Thanks & Regards,

Anagha Deshmukh

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 10:20:30 GMT

Hi,

1. Define the fields in select stmt in the same way u declared it in internal table definition & write

into table <it> instead of into corresponding fields of...it improves performance...

2. Define views of the structure mentioned in an internal table ....and instead of JOIN stmt just use normal SELECT stmt without join.It ll improve ur performance a lot...try it...

regards,

ajit.

Edited by: AJIT THAKUR on Apr 18, 2009 12:21 PM

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 10:27:43 GMT

Ravi,

If u r fetching data from one table u should not use the inner join syntax Statement

simple write:

select belnr gjahr bldat gsber blart budat dmbtr xblnr

shkzg werks gsber bukrs

into corresponding fields of table i_bsis_main

from bsis

where bukrs eq p_bukrs

and hkont eq p_hkont

and belnr in s_fi_doc

and budat <= s_budat .

Re: Performance Issue:Select statement

Former Member — Sat, 18 Apr 2009 22:06:34 GMT

A) Moved to correct forum

B) It's fine as is.

Rob

Re: Performance Issue:Select statement

Former Member — Mon, 20 Apr 2009 06:51:48 GMT

Please use the SQL Trace and check the actual performance.

Is it really bad, what do you what to improve.

Lease the INTO CORRESPONDING where it is.

The second statement could use INTO TABLE.

Use the alias 'as a' only if several tables are involved, for one it is not necessary, confuses only.

Siegfried

Re: Performance Issue:Select statement

ThomasZloch — Mon, 20 Apr 2009 07:36:53 GMT

Cannot be said often enough: CORRESPONDING is fine.

Use APPENDING CORRESPONDING FIELDS OF TABLE in second select on BSAS, that's about all you can do.

Thomas