topic Re: Select Query Taking Long time in Application Development and Automation Discussions

Select Query Taking Long time

Former Member — Mon, 01 Jul 2013 17:50:33 GMT

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

Re: Select Query Taking Long time

matt — Tue, 02 Jul 2013 06:04:59 GMT

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).

Re: Select Query Taking Long time

Former Member — Tue, 02 Jul 2013 06:12:07 GMT

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.

Re: Select Query Taking Long time

former_member196490 — Tue, 02 Jul 2013 06:54:00 GMT

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

Re: Select Query Taking Long time

ThangaPrakash — Tue, 02 Jul 2013 07:18:01 GMT

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

Re: Select Query Taking Long time

Former Member — Tue, 02 Jul 2013 07:33:57 GMT

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.

Re: Select Query Taking Long time

Former Member — Tue, 02 Jul 2013 08:40:50 GMT

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

Re: Select Query Taking Long time

former_member191562 — Tue, 02 Jul 2013 12:21:59 GMT

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

But many people say and teach othewise.

Re: Select Query Taking Long time

matt — Tue, 02 Jul 2013 12:55:59 GMT

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.

Re: Select Query Taking Long time

matt — Tue, 02 Jul 2013 12:56:59 GMT

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.

Re: Select Query Taking Long time

matt — Tue, 02 Jul 2013 12:58:17 GMT

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

Re: Select Query Taking Long time

Former Member — Tue, 02 Jul 2013 15:03:34 GMT

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

Re: Select Query Taking Long time

matt — Wed, 03 Jul 2013 04:46:54 GMT

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

Re: Select Query Taking Long time

Former Member — Wed, 03 Jul 2013 05:15:05 GMT

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

Re: Select Query Taking Long time

Former Member — Wed, 03 Jul 2013 07:42:06 GMT

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

Re: Select Query Taking Long time

Former Member — Thu, 04 Jul 2013 05:14:54 GMT

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

Re: Select Query Taking Long time

Former Member — Thu, 04 Jul 2013 05:52:52 GMT

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.

Re: Select Query Taking Long time

Former Member — Thu, 04 Jul 2013 10:46:26 GMT

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