Performance issue on select query

[Former Member]

hi,

Can any explain how the system behaves for the below 2 scenarios and which is much better?

In table EABLG i have around 1 billion records (FYI)

1)

SELECT

ablbelnr

anlage

ablesgr

FROM eablg

INTO TABLE it_eablg

FOR ALL ENTRIES IN it_eanl

WHERE ( anlage = it_eanl-anlage

AND ablesgr = '01'

AND adatsoll GT '20080706' ) " last 3 months

OR ( anlage = it_eanl-anlage

AND ablesgr = '06'

AND adatsoll GT '20080706' ) " last 3 months

2)

SELECT

ablbelnr

anlage

ablesgr

FROM eablg

INTO TABLE it_eablg

FOR ALL ENTRIES IN it_eanl

WHERE anlage = it_eanl-anlage

AND ( ablesgr = '01'

OR ablesgr = '06')

AND adatsoll GT '20080706' . " last 3 months

thanks in advance.

with regards

sumanth

.

[Former Member]

Try running both through ST05 and then see the EXPLAIN. If there is any difference, that should show you.

Rob

[valter_oliveira]

The second option looks better because the WHERE condition is more simpler, but as Rob said you should use ST05 to check the differences. However, I would use this one:

SELECT ablbelnr anlage ablesgr 
  FROM eablg
  INTO TABLE it_eablg
   FOR ALL ENTRIES IN it_eanl
 WHERE anlage = it_eanl-anlage
   AND ablesgr IN ('01','06') 
   AND adatsoll GT '20080706' . " last 3 months 

Since you are using index AAG you should have no problems .. unless it_eanl is too big.

Regards,

Valter Oliveira.

Edited by: Valter Oliveira on Oct 6, 2008 4:40 PM

[Former Member]

I know if you are using signs like = <> is better just use that, and if you are using GT, EQ is the same thing so, the better option is

SELECT ablbelnr anlage ablesgr

FROM eablg

INTO TABLE it_eablg

FOR ALL ENTRIES IN it_eanl

WHERE anlage EQ it_eanl-anlage

AND ablesgr IN ('01','06')

AND adatsoll GT '20080706' . " last 3 months

Is better for the interpreter SQL.

Regards,

Ar@cely

[naveen_inuganti2]

Hi..,

Did you tried in SE30 or ST05 ?

--Naveen.i

[Former Member]

the problem here is i dont have data in my development system and need to run this program in quailty soon with out any testing.. and so even if i run the trace in development system is of no gud use.

[valter_oliveira]

Ok, so I think what you should do is to find out is how big will be it_eanl ...

Regards,

Valter Oliveira.

[ThomasZloch]

> the problem here is i dont have data in my development system and need to run this program in quailty soon with out any testing

You can enter SQL statements directly in ST05 (by hitting F5) in QA, so you don't have to transport the program beforehand.

Plus, convince your management that good data and unit testing is required in DEV if they want good quality programs.

Thomas

[Former Member]

here EABLG consists of 1 billion records and IT_EANL consists of around 0.7 million records.

here i am dealing wiht large amount of data and so i need to be much care ful on the select query.

please suggest the best one

[valter_oliveira]

Well, I wouldn't advise you to use FOR ALL ENTRIES from an internal table with 0.7 million records. As an alternative way, I would JOIN eanl and eablg, using package size to get data in blocks, because you may have memory problems too ... However, it's difficult to say that it's the BEST solution for your.

SELECT a~anlage b~ablesgr b~ablbelnr
  INTO TABLE it_eablg
  FROM eanl AS a INNER JOIN eablg AS b
    ON a~anlage = b~anlage
  PACKAGE SIZE 100000
 WHERE a~anlage EQ "the condition you used to fill it_eanl
   AND b~ablesgr IN ('01','06')
   AND b~adatsoll GT '20080706' . " last 3 months
ENDSELECT. 

I would ask rob or thomas to comment what I wrote ...

Regards,

Valter Oliveira.

[ThomasZloch]

Hi Valter,

I think this case is impossible to solve theoretically (at least for me). 1 billion records is a lot, and I'm not familiar with those IS-U installations. It will come down to comparing the options (also the one you introduced) in a system that holds sufficient data.

Cheers

Thomas

[valter_oliveira]

Thomas,

First of all, thanks for your answer ... and I agree with you ... it's very difficult to solve it theoreticaly (that's why I said (to Sumanth) that I could not say that my idea was the best option). I was asking for your comment in the way that I think, since we have a complete index access for second table (eablg), doesn't matter if it has 1 billion records .. the amount of records of the first table (eanl) is the most important thing here, and FOR ALL ENTRIES would not be a good option if first table is very big (that's why I sugested the join) ... am I wrong?

Sumanth,

after testing in ST05 the various options, post here the result for us ...

Regards,

Valter Oliveira.

[Former Member]

thanks for all the help specially Rob, Valter, Ar@cely and Thomas..

i am going this way...coz as per the below analysis ..

EABLG table with the (where clause) conditions have 0.4 million records and so..

1) I will just fetch the records from EABLG

2) and then loop on EABLG and delete un necessary records from eablg...

Thomas,

Can u tell how to write select queries in ST05 after pressing F5....

i tried copy paste the same select query with out for all entries and it throwed me some error or please share me some document if u have any

Sumanth

[ThomasZloch]

The statement has to be in a special format, so it might make sense run a trace in DEV, check the "Explain" there and download/upload the statement to QA (also via ST05). I have not tried this myself yet, so I would be interested if it works

Here is two resources for you, SAP online help and a good blog by Siegfried Boes:

http://help.sap.com/saphelp_erp60_sp/helpdata/EN/d1/801f7c454211d189710000e8322d00/frameset.htm

It's a very powerful analysis tool, so it's worth the time to work yourself into it.

But again, you actually want good test data in DEV for this matter.

Thomas

[Former Member]

DISCARD