Skip to Content
author's profile photo Former Member
Former Member

Table Design and Performance Issues

Hi,

I need to fetch order numbers for a given plant, workcenter and for scheduled finish date from AFKO table.

There are various conditions involved as in :

a) To check whether it is a released order

b) To check whether it is a created order etc.

These various conditions brings in join and at some places

select-for-all queries.

Report is working fine, only issue is performance, especially with the AFKO and AFPO tables. These tables have close to 500,000 records. So fetching data from these tables not on primary key(plant and date) using SE11 itself takes close to 25 seconds. Executing the report, it takes about 1 1/2 minutes, considering the join conditions and select-for-all entries, which are additional.

For this , I have come up with 3 designs:

Design 1
----------
Run a program(PROGRAM1) and , for the give user 
conditions, select all(approximately 10 fields) the 
required fields from AFKO and AFPO, put that in a Z-table 
in background. So next time when the user runs his 
program(PROGRAM2), data will be fetched from Z table. As 
the number of records will be less in Z table, time taken 
to fetch the records will be less. 


Problems foreseen
-----------------
a) Z table will have exactly the same data as AFKO and 
AFPO. So data is being duplicated.


b) User will enter the data in this program and run it in 
background mode. In case the user forgets to run this 
program, possibilities of stale data in Z-table

Design 2
---------
Instead of creating a Z table , create an index in AFKO 
based on dates and plant. Dates and Plant will always be 
the condition to fetch from these tables.

Problems Foreseen
------------------
a) Not sure about the implications of creating an index on
a base table will have on other programs

b) Again not sure about any extra access/authority is 
required to create an index on base table

Design 3
---------
Similar to design 1, but in this case, I will only store 
the date(Scheduled Finish Date) and ther order number in Z
table. So as far as the problem of duplication arises, it 
is only with the date stored in the Z table. As date is 
one of the criteria to search for the order numbers, if I 
create an index on this Z table on dates, the response 
would be quick and later would be searching the AFKO table
now on primary key, so again the response would be 
quick(considering that AFKO already has an index based on 
AUFNR(order no.)).

Problems Foreseen
------------------
As mentioned earlier, duplication has now been reduced to 
a singular field and again possibility of stale data has 
also been reduce to date field.

Especially in the designs which involve Z - table , some prime question that I should be asking is

a) How frequently will PROGRAM1 be running ? (Every day)

b) How much data this Z - table store ?

c) When will the records in the Z - table be deleted ? (Otherwise it would be just another AFKO table and response time will be as slow)

Are there any better designs than this, to nail this problem. Kindly share. I especially want a design, which removes the possibility of using a Z table. Is this possible ?

I am aware that there are some open threads which you would want to question. Feel free, as design, involves a lot of questions, although I have tried my best to elucidate my points.

Thank you for going through this entire post.

Regards,

Subramanian V.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2005 at 08:17 AM

    Hello Subramanian,

    Just a few thoughts of mine here -

    1. As you have rightly said, maintaining the Z-table is going to be a cause for concern. Also, the number of entries in the Z table might even become greater then the number of entries in the actual table, because the each user would be giving a different selection criteria. Isn't that correct?

    2. Have you explored all the avenues to optimize the ABAP part of the program? Indexes and ABAP optimization could give you some good results.

    3. If you have tried the all the possible options that you know (including the previous suggestion), you might want to consider the option of doing some kind of parallel processing. You could split the selection conditions and use something like

     CALL FUNCTION func IN BACKGROUND TASK.

    or

     CALL FUNCTION func ...STARTING NEW TASK task name. 

    I shall let you know if I can think something better.

    Regards,

    Anand Mandalika.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hello Subramanian,

      How you would want to split the input is something that you will have to decide for yourself. Some questions you might want to consider would be :

      1. How many parallel processes do you want to have?

      2. Does your system have the resources to execute the required number of parallel tasks?

      There's some excellent documentation on Implementing" target="_blank">http://help.sap.com/saphelp_46c/helpdata/en/fa/096e92543b11d1898e0000e8322d00/content.htm">Implementing Parallel Processing, which I'm sure you will find to be very helpful.

      Go through the information in the link and please do get back if you got some more doubts.

      =====================================================================

      By the way, since you are saying that you have a very large amount of data, one of the options that you must consider (which I guess is usually forgotten) is Extract Datasets (or field-groups). It might be construed that their use is only restricted to the case where the lines have different structures. But in my experience, they have, on occassion, given some really good results when it came to handling large volumes of data. Please do give it a thought.

      Regards,

      Anand Mandalika.

      <i>P.S.</i> You can still use parallel processing if you decide to go for the Z-Table.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2005 at 08:49 PM

    Couple of quick thoughts on a lazy Saturday afternoon. Hope all are having a nice weekend.

    In regards to avoiding a Z table, you can always do an EXPORT to INDX, and likewise an IMPORT from INDX later.

    In regards to creating an index, I always try and remember that we spent a lot of many to buy a data base management system and providing an indexing capability is one of the features that you paid for. This should only have a positive or neutral impact on any other reading programs, with a small and probably undetectable decrease in performace for updating programs.

    The Z table you are describing is very much like the matchcodes that we used heavily in R2 and are now considered obsolete in R3. As of 4.6C, matchcodes are hidden under a menu option from SE11.

    Maintaining another table is just a different way of adding work to the data base manager but in a way that no other users would ever be able to take advantage of it. Might as well add the index instead and then not only will your program be able to use it but also other users like SE16.

    Enough rambling for now, back to painting the kitchen but at least the football game will be on soon!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2005 at 09:35 AM

    Hi Subramanian,

    I think your problem needs to be addressed through parallel processing. I had a report that was extrapolated to run for 9 days, processing 10 million customers. It never got that far though, because it would crash after an hour or two (memory use).

    After adapting it for parallel processing, the report ran in 15 minutes.

    Send me an e-mail if you would like additional information, as a 3rd party parallel processing framework (that I am affiliated with) was used. I'm not sure it's appropriate to discuss it here in SDN.

    You can e-mail me on sbardzn@basistzchnklkgizs.ckm (replace z with e, replace k with o).

    Cheers,

    Scott

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2005 at 07:04 AM

    Hi Subramanian,

    I face the same situation here. Just have a look at this also,

    1. You can use the PROGRAM1 to create a file that stores the data update from the AFKO and AFPO tables daily/ weekly or monthly. you can use the file naming conventions as YYYYMMDD (with extensions).

    2. When you run the PROGRAM2, this should generate a report with the data present in the generated file.

    3. Thus as per your suggestion, there will be no duplicate z table, rather a file, and hence the delay will be small. This file will be different as their are named are generated based on date, thus reporting will become easier, if report is to be generated for monthly data updates / weekly updates or daily updates etc.

    Hope this is another option without creating a Z table.

    If you come across better solution please let me know,

    Thanks and Regards,

    Kathirvel

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Kathirvel,

      Thanks for going through that entire post of mine and posting a reply. Having a file store database data, I am quite cynical about that. Duplication still exists, not in tables,however in files and having to store this data datewise in a file, increases complexity multifold. Fetching data would take much more time, as I cannot use any search criteria on the file. I have to download that entire data.

      Nevertheless, it is an option. I shall keep you posted, once I get a solution.

      Regards,

      Subramanian V.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.