Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Execution time of same program makes big difference

Hello all,

The execution time of same program in PRD system and QAS system makes big difference.

The difference of data is not much(as system copy was run on a regular time schedule. And the system enviroments are exactly the same. However, while the program only cost 2-3 seconds in QAS, it cost 7-8 minutes in PRD.

It only happens when trying to join some tables together.

I've checked the execution plans of same search, they are different:

QAS:

SQL Statement

-


SELECT

T_00.RANL , T_00.XALLB , T_00.REPKE , T_00.REWHR , T_00.HKONT , T_00.ZTMNAIBRX , T_00.GSART ,

T_00.ZTMHOYMNX , T_00.ZTMSBKBNX , T_00.ZTMSHDAYZ , T_00.ZTMMBHZKP , T_01.BAL_SH_CUR ,

T_01.ZTMSIHONP , T_02.SECURITY_ID , T_02.SECURITY_ACCOUNT

FROM

ZTM0108 T_00, ZTM0135 T_01, TRACV_POSCONTEXT T_02

WHERE

T_00.MANDT = '350' AND T_00.BUKRS = 'MC51' AND T_00.ZTMMCSNGX = '200806' AND

T_02.SECURITY_ACCOUNT = '0001' AND T_01.MANDT = '350' AND T_01.BUKRS = T_00.BUKRS AND

T_01.ZTMMCSNGX = T_00.ZTMMCSNGX AND T_01.PARTNER = T_00.REPKE AND T_02.MANDT = '350' AND

T_02.SECURITY_ID = T_00.RANL

Execution Plan

-


SELECT STATEMENT ( Estimated Costs = 666 , Estimated #Rows = 72 )

--- 12 HASH JOIN

( Estim. Costs = 666 , Estim. #Rows = 72 )

Estim. CPU-Costs = 37,505,220 Estim. IO-Costs = 663

Access Predicates

-- 9 HASH JOIN

( Estim. Costs = 268 , Estim. #Rows = 51 )

Estim. CPU-Costs = 18,679,663 Estim. IO-Costs = 267

Access Predicates

-- 6 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 38 )

Estim. CPU-Costs = 264,164 Estim. IO-Costs = 25

-- 4 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 27 )

Estim. CPU-Costs = 258,494 Estim. IO-Costs = 25

-- 2 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

( Estim. Costs = 25 , Estim. #Rows = 24 )

Estim. CPU-Costs = 253,454 Estim. IO-Costs = 25

Filter Predicates


1 INDEX RANGE SCAN DIFT_POS_IDENT~SA

( Estim. Costs = 1 , Estim. #Rows = 554 )

Search Columns: 1

Estim. CPU-Costs = 29,801 Estim. IO-Costs = 1

Access Predicates


3 INDEX RANGE SCAN TRACT_POSCONTEXTID

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates


5 INDEX UNIQUE SCAN TZPA~0

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

--- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

( Estim. Costs = 242 , Estim. #Rows = 2,540 )

Estim. CPU-Costs = 10,811,361 Estim. IO-Costs = 241


7 INDEX RANGE SCAN ZTM0108~0

( Estim. Costs = 207 , Estim. #Rows = 2,540 )

Search Columns: 3

Estim. CPU-Costs = 9,790,330 Estim. IO-Costs = 207

Access Predicates Filter Predicates

--- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

( Estim. Costs = 397 , Estim. #Rows = 2,380 )

Estim. CPU-Costs = 11,235,469 Estim. IO-Costs = 396

-


10 INDEX RANGE SCAN ZTM0135~0

( Estim. Costs = 323 , Estim. #Rows = 2,380 )

Search Columns: 3

Estim. CPU-Costs = 10,288,477 Estim. IO-Costs = 323

Access Predicates Filter Predicates

PRD:

Execution Plan

-


SELECT STATEMENT ( Estimated Costs = 209 , Estimated #Rows = 1 )

--- 12 NESTED LOOPS

( Estim. Costs = 208 , Estim. #Rows = 1 )

Estim. CPU-Costs = 18.996.864 Estim. IO-Costs = 207

-- 9 NESTED LOOPS

( Estim. Costs = 120 , Estim. #Rows = 1 )

Estim. CPU-Costs = 10.171.528 Estim. IO-Costs = 119

-- 6 NESTED LOOPS

Estim. CPU-Costs = 27.634 Estim. IO-Costs = 0

-- 4 NESTED LOOPS

Estim. CPU-Costs = 27.424 Estim. IO-Costs = 0


1 INDEX RANGE SCAN TZPA~0

Search Columns: 1

Estim. CPU-Costs = 5.584 Estim. IO-Costs = 0

Access Predicates

--- 3 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Filter Predicates


2 INDEX RANGE SCAN DIFT_POS_IDENT~PT

Search Columns: 1

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates


5 INDEX RANGE SCAN TRACT_POSCONTEXTID

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

--- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

( Estim. Costs = 120 , Estim. #Rows = 1 )

Estim. CPU-Costs = 10.143.893 Estim. IO-Costs = 119


7 INDEX RANGE SCAN ZTM0108~0

( Estim. Costs = 119 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 10.142.167 Estim. IO-Costs = 119

Access Predicates Filter Predicates

--- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

( Estim. Costs = 89 , Estim. #Rows = 1 )

Estim. CPU-Costs = 8.825.337 Estim. IO-Costs = 88

-


10 INDEX RANGE SCAN ZTM0135~0

( Estim. Costs = 88 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 8.823.742 Estim. IO-Costs = 88

Access Predicates Filter Predicates

Could anyone tell me the reason? I've found note 724545 but not sure.

And, how to read the execution plan?(1 first or 12 first?)

Best Regards,

Robin

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question