topic Re: Performance issue on select query in Application Development and Automation Discussions

Performance issue on select query

Former Member — Mon, 06 Oct 2008 15:29:36 GMT

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)

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

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

Re: Performance issue on select query

Former Member — Mon, 06 Oct 2008 15:32:43 GMT

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

Rob

Re: Performance issue on select query

valter_oliveira — Mon, 06 Oct 2008 15:36:43 GMT

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

Re: Performance issue on select query

naveen_inuganti2 — Mon, 06 Oct 2008 15:37:47 GMT

Hi..,

Did you tried in SE30 or ST05 ?

--Naveen.i

Re: Performance issue on select query

Former Member — Mon, 06 Oct 2008 15:41:12 GMT

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.

Re: Performance issue on select query

valter_oliveira — Mon, 06 Oct 2008 15:42:50 GMT

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

Regards,

Valter Oliveira.

Re: Performance issue on select query

Former Member — Mon, 06 Oct 2008 15:46:18 GMT

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

Re: Performance issue on select query

Former Member — Mon, 06 Oct 2008 15:52:35 GMT

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

Re: Performance issue on select query

ThomasZloch — Mon, 06 Oct 2008 15:54:48 GMT

> 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

Re: Performance issue on select query

valter_oliveira — Mon, 06 Oct 2008 16:01:08 GMT

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.

Re: Performance issue on select query

ThomasZloch — Mon, 06 Oct 2008 16:15:44 GMT

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

Re: Performance issue on select query

Former Member — Mon, 06 Oct 2008 16:28:11 GMT

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

Re: Performance issue on select query

ThomasZloch — Mon, 06 Oct 2008 17:10:32 GMT

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

Re: Performance issue on select query

valter_oliveira — Tue, 07 Oct 2008 11:01:59 GMT

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.

Re: Performance issue on select query

Former Member — Sun, 11 Jan 2009 12:12:43 GMT

DISCARD