Performance Issue:Select statement

[Former Member]

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

[Former Member]

This message was moderated.

[Former Member]

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

[Former Member]

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

[Former Member]

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...

OR

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

[Former Member]

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 .

[Former Member]

A) Moved to correct forum

B) It's fine as is.

Rob

[Former Member]

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

[ThomasZloch]

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