Performance in select statement

[Former Member]

Hi Expart,

I am writing the statement . It ll take time very much .

SELECT DISTINCT a~belnr

a~lifnr

a~budat

a~dmbtr

a~gsber

b~name1

APPENDING CORRESPONDING FIELDS OF TABLE itab_itds

FROM bsak as a

INNER JOIN lfa1 AS b

ON a_{lifnr = b}lifnr

INNER JOIN bkpf as c

on a_{belnr = c}belnr AND a_{gjahr = c}gjahr and a_{xblnr = c}xblnr

FOR ALL ENTRIES IN itab_bsas

WHERE a~belnr = itab_bsas-belnr

AND a~budat IN budat1

and a~gsber = gsber-low

and c~stblg = ' '.

Plz. tell me how to performance the report it ll take time very less .

[Sathish]

You need to split the query. Based on the order of execution and usage of conditions, split the query.

[BH2408]

Hi ,

Use For all entries instead of inner join .....

Regards,

Bharani

[JozsefSzikszai]

1. BSAK-BKPF should be joined with bukrs, belnr, gjahr (remove xblnr)

2. check before the SELECT, if itab_bsas has any entry

3. If the above still don't help, than you have to do something with the WHERE condition

[SujeetMishra]

Hi razz,

i always scare of using joins..because really it takes much time to procede to next statement in program.

and also bad for performance.

so you need to split your code into different internal table and compare with previous entry.

like below :

SELECT belnr lifnr budat dmbtr gsber INTO CORRESPONDING FIELDS OF TABLE it_bsak

FOR ALL ENTRIES IN itab_bsas

where -

---

IF NOT it_bsak[] IS INITIAL.

SELECT name1 INTO c_name1 from LFA1

FOR ALL ENTRIES IN it_bsak.

where belnr = it_bsak-belnr.

Endif.

Regards,

sujeet

[Former Member]

Hi,

if think that, then this is the discussion for you:

Never thought about what FAE is doing?

The real reason is: Had some bad experience with Joins ,never really learned to write good SQL ,

never thought about the data model (tables, indexes) behind the statements.

Databases natural language is SQL - it's fast, it's reliable and is existing since around the seventies.

I will go along with Eric.

If you go for the procedural approach - do it. Later some expert will come and replace it with high-performance SQL

bye

yk

[Former Member]

Hi Razz,

This will definalty degrade your performance.

Instead of this. try to fetch the data into the internal table and then use the For All entries.

Also avoid Inner join it degrade yoru performance.

Thanks,

Chidanand

[Former Member]

hello Razz,

According to me Don't use the Inner Join along with the For All Entries. Either you can use one. For better performance use the For All Entries. Or one more solution is that you can change your code slightly so that it can increase your performance.

[Former Member]

Hi,

I can see a lot of issues with your SELECT:

Avoid using Inner Join with FOR ALL ENTRIES.....it May give you incorrect results sometime.

Avoid using FOR ALL ENTRIES with Large Tables like BKPF.

Always check that the Driver Table should not be empty before writing the FOR ALL ENTRIES.

Avoid Append Corresponding fields of......Use into table.....use an Internal Table with Similar structure and do the append later on as an Internal Table Operation.

You basically SPLIT your Select into Two Select Statements......First get the data fetched from BKPF and then do the FOR ALL ENTRIES on BSAK.