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

Query optimization

Posted on Jan 31, 2005 at 08:14 AM 71 Views

I m facing a problem of Query optimization.

I have used one query in report which takes too much time. Thhis query contains 4 inner join and one left join. All inner joins are in one-to-one relaionship. I am giving this query below.

SELECT catsdb~pernr

catsdb~raufnr

afru~budat

catsdb~catshours

afvc~vornr

afih~equnr

crhd~arbpl

afvc~ltxa1

catsdb~lstnr

catsdb~sebeln

catsdb~sebelp

catsdb~belnr

catsdb~workdate

FROM catsdb INNER JOIN afih ON catsdbraufnr = afihaufnr

INNER JOIN crhd ON afihgewrk = crhdobjid

INNER JOIN pa0315 ON catsdbpernr = pa0315pernr

INNER JOIN afvc ON catsdbraufpl = afvcaufpl

AND catsdbraplzl = afvcaplzl

LEFT JOIN afru ON afruaufnr = catsdbraufnr

AND afrucatsbelnr = catsdbbelnr

INTO CORRESPONDING FIELDS OF TABLE LT_AFRU

WHERE catsdb~pernr IN s_pernr

AND catsdb~workdate IN s_wrkdat

AND pa0315~lifnr IN s_lifnr

AND afih~equnr <> ''

AND crhd~objty = 'A'

AND crhd~arbpl IN s_arbpl

AND catsdb~belnr <> ''

AND not catsdb~status in ('40','60').

This query returns 343 records in our system.

Table record information is given below :

CATSDB : 432

AFRU : 573,142

AFIH : 859,363

PA0315 : 105

CRHD : 6237

AFVC : 1,040,925

If anyone knows then Please give me alternate solution for this query faster than this query.

thanks,

Bhavik

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2005 at 10:22 AM
    1

    Hi,

    Take a look at http://service.sap.com/performance for tips on improving your program's performace.

    A few tips though:

    1. If possible, change the <b>INTO CORRESPONDING FIELDS OF TABLE LT_AFRU</b> to <b>INTO TABLE LT_AFRU</b>.

    2. Check whether your select options contain at least 1 entry, otherwise the complete table will be scanned

    3. Your WHERE clause needs a bit of rewriting. Try this:

    WHERE catsdb~pernr IN s_pernr
AND catsdb~workdate IN s_wrkdat
AND catsdb~belnr <> '' "sequence changed, brought up
AND not catsdb~status in ('40','60') "sequence changed, brought up
AND pa0315~lifnr IN s_lifnr
AND afih~equnr <> ''
AND crhd~objty = 'A'
AND crhd~arbpl IN s_arbpl.

    The order of search keys is very important. The DB Optimizer can choose an index only upto the last specified key in where clause. Try to put your selection criterion in the order with which keys are defind in Data Dictionary. See if you can further specifiy any key values to restrict selection.

    4. Use ST05 to see whether indexes are being used. If not, see if it is suitable to create your own secondary indexes.

    Do get back with feedback, if this helped. We can then further look into ways of improving the overall performance of the program as well as your current query.

    Regards

    Message was edited by: Shehryar Khan

    Message was edited by: Shehryar Khan

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer

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.