Performance Issue

[Former Member]

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.

[idi_kurniawan2]

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

[juan_suros]

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

[arindam_m]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[idi_kurniawan2]

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

[anilkumar_kalkivai]

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.