topic Re: Improve performance query in Application Development and Automation Discussions

Improve performance query

Former Member — Mon, 28 Jul 2008 23:32:02 GMT

Hy guys,

im going to improve the performance for one query.

the table has 2 000, 000 lines, and growing each day, (sales per day, per document)

there is a proces that extract sales averages per month, but that process run in about 4-6 hours.

And is always the last 4 months but the table contains 4 year of historical records.

here my options:

a) create a process that copy the last 4 months in a new table, and run the averages process only for this one. (so i need to extract with a query and insert in a new ztable)

b) create a process that copy the last 4 months in a internal table, and work with this one. (can be a it with 16,000 lines? is this going to improve the performance?)

c) create subquerys. (i dont know much about this.)

any ideas?

Re: Improve performance query

Former Member — Tue, 29 Jul 2008 02:05:10 GMT

It is hard to give a good answer as you haven't specified much about which table(s) you are referring to, but here are some general ways to improve performance.

-Do a Runtime analysis or SQL trace (Performance trace) to determine which selects are running slow (System -> Utilities ....menu)

-Add an index to the table(s)

-Make sure the DB statistics are up to date (transaction DB20)

Re: Improve performance query

Former Member — Tue, 29 Jul 2008 04:18:22 GMT

hiii,

usage of select endselect must be avoided.

check wheather unique select statements are not there in subroutines.

Re: Improve performance query

Former Member — Tue, 29 Jul 2008 04:51:28 GMT

Hi Javier.

1. Avoid more number of inner joins, try to use for all entries .

2. use tool ST05, Se30 for performance.

3. Use EWA (Early watch Analysis).

4. Se30 -click on tips and tricks there you can find out the differences of using various statements runtime analysis.

5. I believe performance directly proportional to Database.

You need to simplify the queries without risking data integrity.

The amount of data to be stored and transfered is quite significant you anyways has to look into the above performance measures and the earlier replys to the query.

Mohinder

Re: Improve performance query

Former Member — Tue, 29 Jul 2008 05:14:06 GMT

Javior,

am not sure we can give you exact solution for that.performance kind of issue never been soled without debuggeing your code and following all guidline of coding.

here are some common points:

1) Dont use nested select statements

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Amit.