topic Re: Query optimization in Application Development and Automation Discussions

Query optimization

Former Member — Mon, 31 Jan 2005 08:14:01 GMT

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 catsdb_{raufnr = afih}aufnr

INNER JOIN crhd ON afih_{gewrk = crhd}objid

INNER JOIN pa0315 ON catsdb_{pernr = pa0315}pernr

INNER JOIN afvc ON catsdb_{raufpl = afvc}aufpl

AND catsdb_{raplzl = afvc}aplzl

LEFT JOIN afru ON afru_{aufnr = catsdb}raufnr

AND afru_{catsbelnr = catsdb}belnr

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

Re: Query optimization

Former Member — Mon, 31 Jan 2005 10:22:41 GMT

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

Re: Query optimization

Former Member — Mon, 31 Jan 2005 11:18:12 GMT

Hi Shehryar,

I have tried with INTO TABLE instead of INTO CORRESPONDING... But its not giving me much support and same case for Order of where conditions.

And i think one surprising news for you is, When i provide values in search-options then response time increases. It means that Query takes more time when any restriction is given in Search-option table.

Same query takes 15 sec. without any conditions in search option. But if i m specifying single value for Work center (S_ARBPL) then it takes 15 mins. for execution.

So now i m fetching all records without specifying search option conditions, and then i m deleting records from internal table based on the selection-option values.

So now i can get good performance result.

Re: Query optimization

Former Member — Mon, 31 Jan 2005 11:49:47 GMT

Hi,

The INTO TABLE will not make a formidable difference. The difference will be minimal.

The order of where conditions is as good as one of the indexes is being used by the DB Optimizer to select values. If the where condition is still not allowing the optimizer to choose an index, order won't make much difference. As I said, use ST05 to see if indexes are being used by the optimizer. Additionally, check your program's execution through SE30 to identify bottlenecks, if any.

Fetching more records than required is not recommended. While it might work for your program for now, it will impact the overall performance of the SAP system in the long run. The performance might degrade as well when data volume increases in the database.

If your query uses indexes properly, your program will give you nearly linear dependecy upon the data volume.

Sometimes, rather than sending a single complex query to DB, breaking it into simpler, faster searches that use indexes turn out to be a good option as well. See if this works for you.

There is certainly no silver button for performance optimization. The data/transaction volume in your DEV system might not reflect the one in PRD. Only you know the current/expected situation. That's why I mentioned the recommended guidelines that work in most of the cases.

Hope this helps.

Regards

Re: Query optimization

Former Member — Mon, 31 Jan 2005 12:05:05 GMT

Hi.

Sometimes it can be useful to create a db view instead of a very complex query. Then the query is more clear to read (by programmer) and usually works much faster.

Hope this helps.

Lukasz