Performance Tuning - The better approach

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

opticalmouse
Posts: 72
Joined: Sun Feb 27, 2005 4:23 am

Performance Tuning - The better approach

Post by opticalmouse » Tue Jul 01, 2008 12:56 pm

Hello,
I am currently working on optimizing a program which runs for about 2 Hours in Production as a background job. One of the select queries in the program is

Case 1:

Code: Select all

  SELECT objnr INTO TABLE it_jest
          FROM jest
          FOR ALL ENTRIES IN it_aufnr
          WHERE objnr = it_aufnr-objnr
          AND   stat  = 'TECO'
          AND   inact = space.
Now that in JEST table OBJNR and STAT are the only key fields, Is it possible that the select on INACT is causing the select query to run slowly ?

If I modify the above select query as shown below, in your opinion make any difference to the overall performance ?

Case 2:

Code: Select all

  SELECT objnr INTO TABLE it_jest
          FROM jest
          FOR ALL ENTRIES IN it_aufnr
          WHERE objnr = it_aufnr-objnr
          AND   stat  = 'TECO'.
  DELETE it_jest WHERE inact <> space.
Unfortunately I have no data in Development so I am not sure which approach would be better :roll:

Thanks.
Live Life King Size

skarkada
Posts: 701
Joined: Mon Oct 21, 2002 1:12 pm
Location: Houston, TX, USA

Re: Performance Tuning - The better approach

Post by skarkada » Tue Jul 01, 2008 2:39 pm

I don't think that SQL statement is the problem in your program.

You can test your theory in production system using SE16. Run SE16 on JEST without any filters and get only 200 OBJNR values. Then run SE16 again for those OBJNR values and other filters and compare the time.

What you should really be doing to analyze a program for performance is to run it in SE30.

Good luck.
Sudhi Karkada

YuriT
Posts: 885
Joined: Fri Feb 03, 2006 6:40 am
Location: Basel/Riga

Re: Performance Tuning - The better approach

Post by YuriT » Wed Jul 02, 2008 8:34 am

Hi.

This select looks OK you have key fields listed after all. But please make sure it_aufnr table isn't empty. If it is, you will end up selecting EVERYTHING from jest.

And if this isn't a problem, use Runtime Analysis (SE30) and SQL Trace (ST05) for more detailed information on your program's performance.

abap_doctor
Posts: 6955
Joined: Thu Mar 25, 2004 11:29 am
Location: Airstrip One

Re: Performance Tuning - The better approach

Post by abap_doctor » Wed Jul 02, 2008 9:29 am

FOR ALL ENTRIES IN has been discussed many times here, so take a search to see what has been advised before.

It can be useful for small amounts of data, but, in my own opinion, I try not to use it!!!! :wink:
The Doc
Tuly Idiot Magna Grand Docot Image

opticalmouse
Posts: 72
Joined: Sun Feb 27, 2005 4:23 am

Re: Performance Tuning - The better approach

Post by opticalmouse » Wed Jul 02, 2008 12:23 pm

Hello,
Thanks a lot for your replies. Maybe I should rephrase my question. Is it better to filter the non-key fields in the select query or would it be better off if I select the data with only the key fields into an internal table and then delete the entries based upon the non-key fields.
Live Life King Size

JDO
Posts: 17
Joined: Mon Dec 11, 2006 1:11 pm
Location: Germany

Re: Performance Tuning - The better approach

Post by JDO » Wed Jul 02, 2008 12:53 pm

Hi,

I think, your select is wrongly using the secondary index JEST~I with fields STAT and INACT (Rel. 4.7).

Is this the case, your second approach is better, because it will point the DB-Optimizer in the right direction.

Regards Juergen

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Performance Tuning - The better approach

Post by Dr Sidewalk » Thu Jul 03, 2008 4:10 am

I think someone has already mentioned about the effects of an empty FAEI (For All Entries In) itab.

However, if you're using IBM's DB2/6 then you could be in for some surprises in regards to the FAEI clause as it works differently (believe it or not) to way that Oracle would.

Could you let us know.

Also, is there a reason that you have not simply JOINED transparent table 'jest' with the source transparent table that itab it_aufnr was derived?.

With regards to the non-key fields. No it does not matter if use the non-key fields (normally) in the selection WHERE clause, just so long as you've identified the primary index keys within the WHERE clause, in the same order they appear in the index, and as manner as you can define. The database will use the index partially if this is the case. However, we have a secondary index (called simply 'I') against table jest on our system and this looks like a SAP created secondary index. So, this is were the fun begins because the key for secondary index I is MANDT,STAT,INACT, meaning it could be using this secondary index instead of the primary. It should not do this because you've already mentioned the two key fields for the primary index, but I've seen the wrong index get used before, again mainly in DB2/6 database enviroments. Check the results of an Explain path for the generated SQL using tranx ST05. :)


Dr Sidewalk
Last edited by Dr Sidewalk on Fri Jul 11, 2008 2:15 am, edited 1 time in total.
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Performance Tuning - The better approach

Post by wilhitern1 » Thu Jul 03, 2008 6:56 am

Dr Sidewalk, I haven't seen that talked about before. Do you know of some good documentation?
Neal Wilhite (WilhiteRN1)Image

opticalmouse
Posts: 72
Joined: Sun Feb 27, 2005 4:23 am

Re: Performance Tuning - The better approach

Post by opticalmouse » Thu Jul 03, 2008 3:25 pm

Dr Sidewalk wrote:I think some has already mentioned about the effects of an empty FAEI (For All Entries In) itab.

However, if you're using IBM's DB2/6 then you could be in for some surprises in regards to the FAEI clause as it works differently (believe it or not) to way that Oracle would.

Could you let us know.

Also, is there a reason that you have not simply JOINED transparent table 'jest' with the source transparent table that itab it_aufnr was derived?
Hello,
I have an Oracle database in here. I have selected a FAE over a Join as I was under the (possibily wrong) impression that a join would be slower than a FAE. By the way I am selecting the contents of it_aufnr from AFKO table and this usually has over 50,000 Records.

The correct index (OBJNR, STAT) is being used in this case.
Live Life King Size

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Performance Tuning - The better approach

Post by Dr Sidewalk » Mon Jul 07, 2008 1:17 pm

wilhitern1,

You mentioned:
Dr Sidewalk, I haven't seen that talked about before. Do you know of some good documentation?
What exactly did I talk about as there were a few things covered?.

opticalmouse,

You mentioned:
Hello,
I have an Oracle database in here. I have selected a FAE over a Join as I was under the (possibily wrong) impression that a join would be slower than a FAE. By the way I am selecting the contents of it_aufnr from AFKO table and this usually has over 50,000 Records.
The problem here is that you're visiting AFKO at least once anyway regardless of how many records there are. So, if you joined using key fields and using the same where clause then there should be very little difference in processing speed. FAEI can be very helpful at times, but it's also the devils little helper as well. Try tracking (using ST05) the SQL statements produced when you submit a SELECT statement with a FAEI clause. Depending on the number of entries in the FAEI itab you could see thousands of seperate (UNIONed into 10 SQL statements) SQL statements. On it's own this is not too much of a problem but when it starts running against other programs which also use the FAEI clauses then the database gets swamped with thousands of single SQL statements and you end up sending more SQL code that you actually retrieve data. In this case it's better to join the tables and have just one SQL statement and let the database (that's what it's good at) extract the data and return it. You just need to ensure that your join is efficient and makes use of all keys when joining and within the WHERE clause.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Performance Tuning - The better approach

Post by wilhitern1 » Tue Jul 08, 2008 11:11 pm

I was refering to the following:
However, if you're using IBM's DB2/6 then you could be in for some surprises in regards to the FAEI clause as it works differently (believe it or not) to way that Oracle would.
Sorry, I should have quoted it the first time.
Neal Wilhite (WilhiteRN1)Image

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Performance Tuning - The better approach

Post by Dr Sidewalk » Fri Jul 11, 2008 2:20 am

I don't have it to hand but there one or two OSS notes specifically mentioning the FAEI clause when using DB2/6. If I can find the note in my notes I will add it to this posting.

We had tremendous problems with the FAEI clause. In the end it was much faster to create and populate our own table which we used instead of the itab used by the FAEI clause, and then simply joined to it. Once the SQL returned the result we then deleted the contents of this temp table. This sounds long-winded but it was 20-30 faster that using the FAEI method with a DB2/6 database.

Thankfully all my recent projects have been with Oracle which does not suffer this problem.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Performance Tuning - The better approach

Post by wilhitern1 » Fri Jul 11, 2008 6:55 am

Very cool! I should be able to dig it out from that. Thanks Dr S

PS: recently, I've been thinking the same about your create table and join technique but experts keep telling me I'm nuts. I expect that I'll have to try it now.
Neal Wilhite (WilhiteRN1)Image

PJA
Posts: 11383
Joined: Sun Jul 20, 2003 3:11 pm
Location: Deepest darkest Aberdeenshire
Contact:

Re: Performance Tuning - The better approach

Post by PJA » Fri Jul 11, 2008 7:42 am

opticalmouse,

I've used this format many times without problem:

Code: Select all

 select * from jest into table istat
        for all entries in itab
        where objnr = itab-objnr_wo
        and   stat = 'I0076'     " internal number from TCode BS23
        and   inact = ' '.
I noticed that your value for STAT is "TECO", is that a user-status?

PeteA

opticalmouse
Posts: 72
Joined: Sun Feb 27, 2005 4:23 am

Re: Performance Tuning - The better approach

Post by opticalmouse » Sat Jul 12, 2008 1:03 pm

PJA wrote: I noticed that your value for STAT is "TECO", is that a user-status?
PeteA
Actually I have a variable in the place of 'TECO' which contains the system status. The select query I mentioned is just an example.
Live Life King Size

Post Reply