topic Re: PERFORMANCE in Application Development and Automation Discussions

PERFORMANCE

Former Member — Mon, 23 Jul 2007 09:23:01 GMT

Hi,

We have to get data from GLPCA, which is having lakhs of records. Taking lot of time to run. Is there any method to improve the performance??

Note: Select query follows the index.

PRa

Re: PERFORMANCE

Former Member — Mon, 23 Jul 2007 09:28:27 GMT

Hi,

The best way to increase the performance is

using all the primary keys in the where clause of a select statement and u can also specify the indexes if exists for that table.

Thanks

Srinivas

Re: PERFORMANCE

Former Member — Mon, 23 Jul 2007 09:28:49 GMT

You can improve performance by optimizing your SELECT statements in your program. You can use the ST05 transaction to see what SELECT staterments are performing badly.

Regards,

Abhay.

Re: PERFORMANCE

Former Member — Mon, 23 Jul 2007 09:30:12 GMT

Some of my favs at SDN,

SAP Network Blog: Welcome to the Performance Forum!

/people/joerg.nalik/blog/2005/08/04/welcome-to-the-performance-forum

SAP Network Blog: Analyzing performance problems on a production system

/people/markus.kohler/blog/2006/09/14/analyzing-performance-problems-on-a-production-system

SAP Network Blog: Performance improvement hints: date, time, timestamps

/people/harry.dietz/blog/2005/10/11/performance-improvement-hints-date-time-timestamps

SAP Network Blog: Performance of Nested Loops

/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops

SAP Network Blog: Performance improvement hints 4: loop at itab where...

/people/harry.dietz/blog/2005/11/03/performance-improvement-hints-4-loop-at-itab-where

SAP Network Blog: Query Performance, Pareto, Goldratt's TOC & Me

/people/vikash.agrawal/blog/2006/11/15/query-performance-pareto-goldratt146s-toc-me

SAP Network Blog: Query Performance – Is "Aggregates" the way out for me?

/people/vikash.agrawal/blog/2006/04/17/query-performance-150-is-aggregates-the-way-out-for-me

SAP Network Blog: Performance Tuning - an overlooked activity

/people/hema.rao/blog/2006/09/25/performance-tuning--an-overlooked-activity

SAP Network Blog: Performance improvement hints 3: internal table - fill and read

/people/harry.dietz/blog/2005/10/28/performance-improvement-hints-3-internal-table--fill-and-read

SAP Network Blog: When Old Data Affects Performance. And When Not.

/people/helmut.stefani/blog/2006/02/13/when-old-data-affects-performance-and-when-not

SAP Network Blog: Getting Through the Maze of Query Performance Improvement–a Quantitative Approach

/people/vikash.agrawal/blog/2006/10/19/getting-through-the-maze-of-query-performance-improvement150a-quantitative-approach

SAP Network Blog: Using Field Symbols in Loop Statements - Performance Boost?

/people/rich.heilman2/blog/2006/03/07/using-field-symbols-in-loop-statements--performance-boost

SAP Network Blog: Query Optimization

/people/prakash.darji/blog/2006/01/26/query-optimization

SAP Network Blog: Internal table - fill and read: how to measure

/people/harry.dietz/blog/2005/12/12/internal-table--fill-and-read-how-to-measure

SAP Network Blog: Query Creation Checklist

/people/prakash.darji/blog/2006/01/27/query-creation-checklist

SAP Network Blog: Simple ABAP Benchmark

/people/sergio.ferrari2/blog/2006/01/18/simple-abap-benchmark

SAP Network Blog: The power of the "Code Inspector"

/people/community.user/blog/2006/11/30/the-power-of-the-147code-inspector148

SAP Network Blog: Parallel Processing in ABAP

/people/naresh.pai/blog/2005/06/16/parallel-processing-in-abap

SAP Network Blog:

/people/nitzan.levi/blog/2004/10/03/code-review-150-it146s-a-kind-of-magic

SAP Network Blog: Writing Optimized query-Basic

/people/puru.govind/blog/2006/09/21/writing-optimized-query-basic

SDN WIKI

https://wiki.sdn.sap.com/wiki/display/HOME/ABAPPerformanceand+Tuning

Regards

ABhay Singh.

Re: PERFORMANCE

Former Member — Mon, 23 Jul 2007 10:46:34 GMT

Abhay,

I clearly mentioned i am using index fields in select query and in select clause it has only few which are needed ( reducing data transfer)

what are other options by which I can improve the performance..

PRa

Re: PERFORMANCE

seshatalpasai_madala — Mon, 23 Jul 2007 10:49:34 GMT

Hi,

If yo ualreayd have INDICES on the table then try to use AS MANY FIELDS IN THE WHERE CLUASE as you have in the INDEX. Also make sure that you give the fields in the WHERE CLAUSE in the same sequnce as the sequence you used to define the INDEX.

From LEFT TO RIGHT you should not leave any GAP in the fields that you are using in the WHERE CLAUSE.

IF you have 1 2 3 4 as fields of index then make sure you put 1 2 3 4 in the same sequence with out missing any field.

The number of fields you use in WHERE CLAUSE and the sequence in which you use decides the INDEX being used or ignored.

Also make your query as simple as possible my pushing all the DATA ANALYSIS to ABAP programming and keeping the query simple.

Regards,

Sesh

Re: PERFORMANCE

Former Member — Mon, 23 Jul 2007 11:00:38 GMT

Hi Madala,

I confirm my query includes all key fields of a seconday index fully and i maintained the same sequence.

Only one concern is I've used an aggreagate function SUM on one field by using Grouping clause...

any inputs other than index related..

Pra

Message was edited by:

Praneet

Re: PERFORMANCE

seshatalpasai_madala — Mon, 23 Jul 2007 11:20:51 GMT

Hi,

Since you have used Aggregate function, which requires an analysis at the database side, there is a high chance that your INDEX is not used and a table scan is forced.

Can you do your SUM using the internal table operation SUM instead of doing it in the SELECT query.

Apart from this I dont see any other options of improvement for your query.

Regards,

Sesh

Re: PERFORMANCE

Former Member — Tue, 24 Jul 2007 05:07:45 GMT

Hi Madala,

What u said is right. At the same time if I do grouping I am able to retrieve 1400+ records among lakhs of records.

If I follow ur suggestion, then I need to retrieve the lakhs of records from database (which increases data transfer volume ) then I need to process them to have SUM.

Don't u think what I am doing is right?

Correct me if I am wrong!!!

PRa

Re: PERFORMANCE

kiran_k8 — Tue, 24 Jul 2007 05:15:37 GMT

Praneet,

Just some additional info.

Do you have a selection screen and selecting the data based on the values given in the selection screen?

Then do one thing.

<b>Get all the data into the internal table using only the primary keys or fields in index in the where clause(don't give the selection screen fields here if they are not a primary keys or index fields).</b>

Now you have all the data in the internal table irrespective of the fields in the selection screen.

Now loop at the internal table and delete the records which are not w.r.t the values given in the selection screen.This way now you will have only the records w.r.t the values in the selection screen.

This will definitely improve the performance.Generally we tend to fetch the data w.r.t the selection screen fields in the where clause of the select statement.When these selection-screen fields are neither a primary key nor an index field then it will drastically reduce the performance.So,always see to that in the where clause of the select statement we provide only primary key fields or index fields.

K.Kiran.

Re: PERFORMANCE

seshatalpasai_madala — Tue, 24 Jul 2007 05:20:09 GMT

Hi,

If there is no other way to ristrict the number of records to read from the database then I advise you to go ahead and use the Aggregate function if it can reduce your number of records since memory is also one concern.

If you can reduce the number of records by using something else in the WHERE clause that is also advised, but gettnig lacks of records into the program is not advised since this can lead to dumps at runtime.

One more option is to use SELECT ENDSELCT along with PACKAGE SIZE option to get the data in some packets and process them, but this will again reduce the DB performance.

Regards,

Sesh