Select Query Taking Long time

[Former Member]

Hi,

We have query which is inner join of two tables with for all entries, The query takes long time to run and its almost stuck nothing really happens. This has been running in past successfully. Below is the query:

SELECT a~opsl_gl_sys_no INTO CORRESPONDING FIELDS OF TABLE it_sysno

        FROM oiuh_rv_opsl AS a

        INNER JOIN oiuh_rv_gl AS b

        ON a~opsl_gl_sys_no = b~gl_gl_sys_no

        FOR ALL ENTRIES IN it_selection_fields

        WHERE a~opsl_own_no = it_selection_fields-owner_no

          AND a~opsl_own_seq_no = it_selection_fields-owner_seq_no

          AND b~gl_pd_cd = it_selection_fields-product_code

          AND b~gl_or_lvl_1_no = ls_company_org1-org_level1_no

          AND b~gl_sa_dt >= d_min_time

          AND b~gl_sa_dt < d_max_time.

If we query the View with same conditions in se16, we get the desired results. But the ABAP code takes lot of time.

Best Regards,

Pinto

[matt]

How is it_selection_fields filled? If it's by a selection before this bit of code, then including that in your inner join may well improve things. (Inner joins are usually better than FAE).

What are the fields of the available indexes on oiuh_rv_opsl and oiuh_rv_gl?

Run this program with an SQL trace on it (ST05). Then trace SE16 on the view. Compare the execution paths (or post them here, if you have difficult understanding them).

[former_member191562]

Matthew Billingham wrote:
(Inner joins are usually better than FAE).

But many people say and teach othewise.

[matt]

Yes they do. And they are wrong. This has been discussed many times on this site, and if I see someone saying that FAE is better than INNER JOIN, their post will be rejected. "FAE better than INNER JOIN" is a myth. It isn't true.

[Former Member]

Hi Pinto,

Before the FAE put initial check on it_selection_fields. More over put the query in ST04 and analyse whether you can leverage any index or not as sugg. by Matthew.

BR.

[former_member196490]

Try to run the query by removing  INTO CORRESPONDING FIELDS OF TABLE it_sysno instead use 'INTO TABLE it_sysno'

[matt]

Neha Sinha wrote:
Try to run the query by removing  INTO CORRESPONDING FIELDS OF TABLE it_sysno instead use 'INTO TABLE it_sysno'

There is no essential difference in performance. This is another myth, discussed many times on this site.

Evidence here: https://scn.sap.com/thread/1714070 and especially here http://scn.sap.com/docs/DOC-33976.

[ThangaPrakash]

Hi,

As per the suggestion from Matthew, try to check if the index is available in the table.

Use “SELECT INTO TABLE” rather than “SELECT INTO CORRESPONDING FIELDS OF TABLE”.

CHECK that the internal table used in FOR ALL ENTRIES is NOT empty as this will retrieve all entries from the table.

Regards,

Thanga Prakash T

[matt]

Thanga Prakash wrote:
Use “SELECT INTO TABLE” rather than “SELECT INTO CORRESPONDING FIELDS OF TABLE”.
Regards,
Thanga Prakash T

No, don't. It makes no essential difference to the performance and is more error prone.

Evidence here: https://scn.sap.com/thread/1714070 and especially here: http://scn.sap.com/docs/DOC-33976

[Former Member]

If the query was running successfully in the past and failing now ...may be it_selection_fields has huge number of entries, why don't you use the same join statements on some limited number of entries to check the query basic functioning.

[Former Member]

1) While using FOR ALL ENTRIES you need to ensure that there exists atleast one row in internal table.

It may be possible that in the past , the same internal table might get filled with rows and for now due to some exceptions, it remains empty.

2) Compute statistcs of the database tables and indexes involved in the query. If your optimization mode is cost-based, then it may well depend on your statistical data of tables and indexes.

Regards,

Vishram

[Former Member]

Hello All,

There is only record in the it_selection_fields internal table, If you directly query the corresponding view there are only 391 records in the database. But still in the code when it encounters this block it gets stuck for long time.

SAP did some analysis and came back to us saying that OIUH_RV_OPSL statistics has been locked, Can anyone elaborate on this. Can this could be reason for the delay.

Best Regards,

Pinto

[matt]

That's something to ask your Basis team. Or over in one of the Netweaver spaces.

[Former Member]

Hi Pinto,

your SELECT can be done with database view V_OIUCWOPSL_GL. But you should notice that view field names differ from database table field names (OIUH_RV_GL-GL_SA_DT = V_OIUCWOPSL_GL-SA_DT, ...).

You should change your code to view access. But I don't think this will fasten your program. Maybe you have a missing index on database or something like that.

Make a SQL trace of a program run to see the indexes used and post this trace.

Regards,

Klaus

[Former Member]

Hello Klaus,

This code has been running successfully from past so many years, This query is  trying to fetch only for for only owner which has just 391 records. We are just not bale to find the reason, Same query runs successfully in the test env. Are there any basis parameters that i need specifically look for.

Best Regards,

Pinto

[Former Member]

Hi Pinto,

with the growth of database tables all queries without matching database indexes will slow down, if the query causes a full table scan for fetching the chosen data. In test env you will have less data in both database tables.

Please make a sql trace and show it to us. I think you will find a full table scan on database table oiuh_rv_gl then. This might show us a missing index for the fields

• gl_pd_cd
• gl_or_lvl_1
• gl_sa_dt

Regards,

Klaus

[Former Member]

Hello Klaus.

Find the trace :

01.07.2013 - 22:31:22 CET - Info for SAP by Thiago Cavalheiro
_fix_control (7168184) add with value "7168184:OFF" _fix_control (13077335) add with value "13077335:ON" _fix_control (6120483) add with value "6120483:OFF" ________________________________________________________________________SQL Statement SELECT T_00."OPSL_GL_SYS_NO" FROM "OIUH_RV_OPSL" T_00 INNER JOIN "OIUH_RV_GL" T_01 ON T_01."MANDT"=:A0 AND T_00."OPSL_GL_SYS_NO"=T_0 1."GL_GL_SYS_NO" WHERE T_00."MANDT"=:A1 AND T_00."OPSL_OWN_NO"=:A2 AND T_00. "OPSL_OWN_SEQ_NO"=:A3 AND T_01."GL_PD_CD"= :A4 AND T_01."GL_OR_LVL_1_NO"=:A5 AND T_01."GL_SA_DT">=:A6 AND T_01. "GL_SA_DT"<:A7 Execution Plan Explain from gv$sql_plan: Address: 00000008FB8F4FF0 Hash_value: 1863233137 Child_number: 0 Instance_ID: 1 Sql_id: 8zxj1y1rhxbmj Parse Timestamp: 20130627 11:37:49 SELECT STATEMENT ( Estimated Costs = 1,422 , Estimated #Rows = 0 ) 7 FILTER Filter Predicates 6 NESTED LOOPS 4 NESTED LOOPS ( Estim. Costs = 1,421 , Estim. #Rows = 1,826 ) Estim. CPU-Costs = 11,319,293 Estim. IO-Costs = 1,421 2 TABLE ACCESS BY INDEX ROWID OIUH_RV_OPSL ( Estim. Costs = 325 , Estim. #Rows = 1,826 ) Estim. CPU-Costs = 2,508,660 Estim. IO-Costs = 325 1 INDEX RANGE SCAN OIUH_RV_OPSL~I02 ( Estim. Costs = 2 , Estim. #Rows = 1,826 ) Search Columns: 3 Estim. CPU-Costs = 90,371 Estim. IO-Costs = 2 Access Predicates 3 INDEX UNIQUE SCAN OIUH_RV_GL~0 Search Columns: 2 Estim. CPU-Costs = 3,229 Estim. IO-Costs = 0 Access Predicates 5 TABLE ACCESS BY INDEX ROWID OIUH_RV_GL ( Estim. Costs = 1 , Estim. #Rows = 1 ) Estim. CPU-Costs = 4,825 Estim. IO-Costs = 1 Filter Predicates Table OIUH_RV_OPSL Last statistics date 04/10/2013 05:46 Analyze Method Sample 451,491 Rows Number of rows 150,497,000 Number of blocks allocated 4,156,342 Number of empty blocks 10,233 Average space 6,721 Chain count 0 Average row length 174 Partitioned NO UNIQUE Index OIUH_RV_OPSL~0 Column Name #Distinct MANDT 1 OPSL_GL_SYS_NO 150,497,000 Last statistics date 06/12/2013 14:43 Analyze Method ple 150,923,118 Rows Branch levels of B-Tree 3 Number of leaf blocks 358,434 Number of rows 150,923,118 Number of distinct keys 150,923,118 Average leaf blocks per key 1 Average data blocks per key 1 Clustering factor 47,142,511 Partitioned NO Global stats NO Compress Prefix length 1 LAST DDL Date 06/12/2013 20:13 NONUNIQUE Index OIUH_RV_OPSL~I02 Column Name #Distinct MANDT 1 OPSL_OWN_NO 2,833 OPSL_OWN_SEQ_NO 100 OWN_INT_TYPE_CD 17 OWN_INT_SEQ_NO 48 OPSL_PAY_CD 3 Last statistics date 06/12/2013 15:03 Analyze Method ple 150,923,118 Rows Branch levels of B-Tree 3 Number of leaf blocks 695,499 Number of rows 150,923,118 Number of distinct keys 82,425 Average leaf blocks per key 8 Average data blocks per key 1,612 Clustering factor 132,942,499 Partitioned NO Global stats NO LAST DDL Date 06/12/2013 20:13 NONUNIQUE Index OIUH_RV_OPSL~Z01 Column Name #Distinct MANDT 1 OPSL_GL_SYS_NO 150,497,000 OPSL_OWN_NO 2,833 OPSL_OWN_SEQ_NO 100 Last statistics date 06/12/2013 15:17 Analyze Method ple 150,923,118 Rows Branch levels of B-Tree 3 Number of leaf blocks 589,921 Number of rows 150,923,118 Number of distinct keys 150,923,118 Average leaf blocks per key 1 Average data blocks per key 1 Clustering factor 47,142,511 Partitioned NO Global stats NO Compress Prefix length 1 LAST DDL Date 06/12/2013 20:13 Table OIUH_RV_GL has around 1.2 bil rows.

[Former Member]

Hi Pinto,

first it seems you have a "bad" secondary index Z01 on table OIUH_RV_OPSL which contains the primary unique key field OPSL_GL_SYS_NO and additionally the fields OPSL_OWN_NO and OPSL_OWN_SEQ_NO, which are the leading fields of stadard secondary index I02.

For OPSL_GL_SYS_NO is an unique identifier for the primary index and, if it is empty, both other fields are handled with secondary index I02, this additional index Z01 is needless and may slow down your system.

Also make sure that the internal table it_selection_fields isn't empty when processing the SELECT:

IF  NOT it_selection_fields[] IS INITIAL.

  SELECT ...

ENDIF.

Also you have to to the more restricting selection 1st.

Maybe it's more selective to switch table access to:

SELECT a~opsl_gl_sys_no INTO CORRESPONDING FIELDS OF TABLE it_sysno

        FROM  oiuh_rv_gl AS b

        INNER JOIN oiuh_rv_opsl AS a

        ON a~opsl_gl_sys_no = b~gl_gl_sys_no

        FOR ALL ENTRIES IN it_selection_fields

        WHERE b~gl_or_lvl_1_no = ls_company_org1-org_level1_no

          AND b~gl_pd_cd = it_selection_fields-product_code

          AND b~gl_sa_dt >= d_min_time

          AND b~gl_sa_dt < d_max_time.

          AND a~opsl_own_no = it_selection_fields-owner_no

          AND a~opsl_own_seq_no = it_selection_fields-owner_seq_no.

This should effect data access from OIUH_RV_GL with index I09 and from OIUH_RV_OPSL with primary key.

Please check this with a sql trace.

Regards,

Klaus