01-31-2005 8:14 AM
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
01-31-2005 10:22 AM
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
01-31-2005 11:18 AM
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.
01-31-2005 11:49 AM
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
01-31-2005 12:05 PM
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