topic Re: Performance in select statement in Application Development and Automation Discussions

Performance in select statement

Former Member — Wed, 22 Oct 2008 06:29:17 GMT

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 .

Re: Performance in select statement

Sathish — Wed, 22 Oct 2008 07:02:13 GMT

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

Re: Performance in select statement

BH2408 — Wed, 22 Oct 2008 07:04:12 GMT

Hi ,

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

Regards,

Bharani

Re: Performance in select statement

JozsefSzikszai — Wed, 22 Oct 2008 07:08:03 GMT

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

Re: Performance in select statement

SujeetMishra — Thu, 23 Oct 2008 07:40:54 GMT

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

Re: Performance in select statement

Former Member — Thu, 23 Oct 2008 12:43:30 GMT

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

Re: Performance in select statement

Former Member — Sat, 25 Oct 2008 18:20:45 GMT

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

Re: Performance in select statement

Former Member — Sat, 01 Nov 2008 09:16:17 GMT

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.

Re: Performance in select statement

Former Member — Sun, 02 Nov 2008 18:37:40 GMT

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.