topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Wed, 26 Jun 2013 16:34:31 GMT

Hello Experts,

I have a program of which I need to improve the performance. This program has some selects written inside a loop. These select statements are actually join queries on tables like VBFA,OBJK, VBPA and KNA1.

The strange thing I have observed is that the same query in production takes 50 min and in a sandbox server takes 2 min.

The sanbox server is a replica of production server in our project and has just 10% lesser records compared to production. I have checked in both the systems for each of the tables mentioned above and found that approximately there is a difference of only 10% of data records in between PRD and SBX.

Now if anyone of you can please help me for the root cause of this issue as to why the same select queries in PRD server takes much longer time but in SBX takes much lesser time eventhough the number of records are quite same in number.

Re: Performance Issue

idi_kurniawan2 — Wed, 26 Jun 2013 19:52:51 GMT

Hi Jaydeep,

Regardless of the server, first thing you might want to do is to take the join queries out of the loop. You can possibly join VBPA - KNA1, but why would you want to join VBFA (Sales Doc. Flow) and OBJK (PM Object List)?

You can share the code here so that we can see what you're up to.

Regards,

Idi

Re: Performance Issue

juan_suros — Wed, 26 Jun 2013 22:41:08 GMT

Jaydeep,

I suggest use transaction ST05 and "SQL trace" to compare how much time is consumed by each system on each step when running an identical report.

Try to find the slowest process on the slowest machine and analyze why it is slow. Compare the analysis between SBX and PRD. The answer must lie there.

My first guess is that you may need to do some maintenance on the indexes of your tables.

Transaction SE14 may be useful.

Juan

Re: Performance Issue

arindam_m — Thu, 27 Jun 2013 01:34:29 GMT

Hi,

I don't know how you arrived at the 10% figure but i guess the data is making the difference. Also check on secondary indexes that might be active or created in Dev and not present in Prod.

Cheers,

Arindam

Re: Performance Issue

Former Member — Thu, 27 Jun 2013 01:35:21 GMT

Hi Jaydeep,

For issues like these, we usually ask our DBA to monitor the indexes of the affected tables. He may reorg or rebuild the indexes.

Did the SQLs in both environments use the same index?

Regards,

Chona

Re: Performance Issue

Former Member — Thu, 27 Jun 2013 03:06:18 GMT

Hi Jaydeep,

Firstly, the data will definitely make the difference even if we go by u'r estimate of 10%.

Secondly, for performance, the SELECTs within a loop is a strict No untill and unless the requirement cannot be achieved without the above approach.

You could follow below steps to help maintainance and increase performance :

1. Populate whole data using SELECTs outside the loop. Do more of processing of internal tables according to your requirement of data.

2.Within the loop, u can use the below pseudo code approach :

Loop 1

Loop 2

Read statement internal table

Endloop.1

Endloop 2

3. Using above pseudo code, if no of records in Loop 2 > no of records in Loop 1 , then use Parallel cursor processing technique,

4. For SELECTs ( which u'll use outside the loop ) , plz make sure that the fields u r fetching are either primary key fields or they being used as indexes.

5. make use of ST12 for Optimizer analysis

If you still face performance issue, u can share u'r code and Trace analysis.

Thanks

Vivek

Re: Performance Issue

Former Member — Thu, 27 Jun 2013 04:37:51 GMT

Hi Jaydeep,

Assuming data is almost same, three quick steps -

1) Run the SQL trace ST05 in both systems and check the queries which are taking time.

2) Put the Query in ST04 Transaction and check whether indexes are hit or not in both systems.

3) Tell us the discrepancy here.

BR.

Re: Performance Issue

Former Member — Thu, 27 Jun 2013 07:41:56 GMT

Hello All,

I checked and found that the indexes in all the tables are exactly same.

Tables which are involved and there number of records in PRD and SBX are as mentioned below

Table Data in PRD Data in Sandbox

====================================

VBFA 72,900,888 66,671,481

OBJK 3, 291,043 3,037,868

KNA1 86,192 83,365

VBPA 39,515,776 37,310,676

LIKP 2,280,553 2,136,961

T005T 1979 1979

SER01 225,038 216,262

Please let me know if there anything more we can try from ABAP side ?

Thanks all for your comments

Re: Performance Issue

Former Member — Thu, 27 Jun 2013 08:41:30 GMT

Hi Jaydeep,

did you run your programm on PRD every day at á fixed time or didi you check it several times a day?

While in SBX you may have all system ressources for your run exclusively, in PRD there will be several other dialogue and batch tasks at the same time. Maybe some of them are accessing the same tables as your program does. This will slow down the run on PRD.

Also an outdated or improper table index on PRD can cause such issues. Therefore it will be helpful to know, which table has a bad access, or will all tables in common are the causers of the longer runtime. A run time analysis or a sql trace comparing SBX with PRD can clear this question.

Regards,

Klaus

Re: Performance Issue

idi_kurniawan2 — Thu, 27 Jun 2013 08:56:15 GMT

Hi Jaydeep,

You cannot just keep comparing, you also need start improving the program's performance. You can start by doing a runtime analysis (SE30) on your program, there you can see where the bottleneck is.

Regards,

Idi

Re: Performance Issue

anilkumar_kalkivai — Thu, 27 Jun 2013 09:35:49 GMT

Hi Jaydeep,

ST05 - SQL Trace

SE30 - ABAP Run time Analysis

For Performance analysis , you have to check your program performance in the above 2 Tcodes.

-> And you Mentioned there is a select statement inside the loop.

Never use a select statement inside the loop.

Instead of that use Read Statement with Binary search

Use Parallel Cursor Method for the nested loop statements.

Regards,

Anil.