SELECT take more time ONLY in one environment

[Former Member]

Hi

I have the following code in a fct module

DATA : BEGIN OF ls_hu_info,

werks TYPE werks_d,

lgnum TYPE lgnum,

matnr TYPE matnr,

charg TYPE charg_d,

lgort TYPE lgort_d,

lfart TYPE lfart,

bwlvs TYPE bwlvs,

bwart TYPE bwart,

statu TYPE zqm_qa_status.

DATA : END OF ls_hu_info.

SELECT SINGLE d_{werks a}lgnum d_{matnr d}charg d~lgort

b_{lfart e}bwlvs e~bwart

INTO CORRESPONDING FIELDS OF ls_hu_info

FROM vekp AS a

INNER JOIN vepo AS d ON d_{venum EQ a}venum

INNER JOIN likp AS b ON b_{vbeln EQ d}vbeln

INNER JOIN lips AS e ON e_{vbeln EQ d}vbeln AND

e_{posnr EQ d}posnr

WHERE a~exidv EQ p_exidv.

My problem is that in one of my SAP environmment, this select statement take more than 5 minutes

In the other environments, the result is instantanous (1 seconde)

How is it possible ?

Thanks

[Sm1tje]

That can depend on several things, but my first guess would be the number of records in your database tables. Normally in development environment 'every' select works just find since the number of records is limited. In production environment however you can have tousands or even millions of records. That will have huge impact on your overall performance.

But again, it can depend on numerous of other things as well, system settings, database statistics etc.

BTW: I have not taken a look at your select statement whether it was optimized or not, I only gave you a hint on how the difference could be explained.

Edited by: Micky Oestreich on Feb 23, 2010 3:31 PM

[former_member194613]

it depends on the data and on the active indexes.

Just run a SQL trace in two or more systems and compare

[Former Member]

Hi

There are many factors that affects the performance of the ABAP Statements. Some of them depends heavily on the system settings that are configured in the ABAP Application Server. Also, the current network load will influence the data transfer between the Presentation Servers and the Application Server.

So, we can seperate the performance factors broadly into 2 categories. The first one being the factors such the system settings, database and application server buffering, etc. And the second one being the performance factors like the using field symbols, keeping the data transfer small and number of database access minimum etc which we, the application developers can control.

You can have a look at the ABAP Code Performance examples which will give a lot of finer points that affect the database performance. I don't have an ERP ABAP System to check the given code for performance.

Hope this helps.

Thanks,

Samantak.

[ThomasZloch]

Next to the already mentioned causes, I used to observe this in the past in two systems with a different database release level. After a database upgrade (Oracle 9.x to 10.x), the cost based optimizer would not find the best access path anymore, which would be cured by an update of the table statistics, or a database bug fix, or a "hint" in the problematic open SQL statement (last ressort).

Thomas

[maciej_domagaa]

1. Display table VEKP in SE11

2. Click "Indexes" button to display the list of indexes for that table

3. Make sure there is an index containing fields MANDT, EXIDV (normally this is index named C - "External Identification") - activate it if it's not active (make sure it exists in the database, not only in ABAP dictionary)

If this index does exist in the database and you still keep getting bad performance, you can update its statistics by performing the following steps:

1. Run transaction DB02

2. Click button "Detail analysis" and type table name: VEKP

3. Mark an entry with index name: VEKP~C and click button "Update" (update statistics)

regards