Difference in execution time of SELECT query between Production and Quality despite almost same volume of data.

Hello Experts,

We are facing an issue, the execution time for the below select query is taking more than 1 hour in production system
and in quality system it is taking less than 5 min for the same selection criteria.

We understand that JOIN statement is causing the issue. But we are not able to understand why it us taking more time
for execution in production system alone and and it is taking very less time in quality system. despite the volume of data is almost same in both Production and quality system.

SELECT vbup~vbeln vbup~posnr vbup~besta vbup~lfsta vbup~uvall vbup~uvp01 vbap~matnr vbap~matkl
vbap~arktx vbap~pstyv vbap~posar vbap~fkrel vbap~abgru vbap~prodh vbap~zmeng vbap~grkor
vbap~faksp vbap~spart vbap~netwr vbap~waerk vbap~kwmeng vbap~vrkme vbap~lprio vbap~werks
vbap~lgort vbap~vstel vbap~route vbap~erdat vbap~ernam vbap~erzet vbap~netpr vbap~kpein
vbap~kmein vbap~shkzg vbap~prctr vbap~matwa vbap~ean11 vbap~charg vbap~wavwr vbap~kzwi2
vbap~mvgr2 vbap~mvgr4 vbap~wgru2 vbap~zzesource vbap~zzcm vbap~zzrrcode vbap~zzcoupon_code
vbap~kdmat vbap~ZZSUPRTIBCO vbap~zzevent mara~ISMINITSHIPDATE
FROM ( vbup
INNER JOIN vbak ON vbak~vbeln = vbup~vbeln
INNER JOIN vbuk ON vbak~vbeln = vbuk~vbeln
INNER JOIN vbap ON vbap~vbeln = vbup~vbeln AND
vbap~posnr = vbup~posnr
INNER JOIN mara ON mara~matnr = vbap~matnr
INNER JOIN knvv ON knvv~kunnr = vbak~kunnr )
UP TO p_maxrec ROWS
INTO TABLE t_order_item_data
WHERE vbup~vbeln IN s_vbeln
AND ( vbak~ernam IN s_ernam OR
vbak~vsnmr_v IN s_ernam )
AND vbak~audat IN s_audat
AND vbak~auart IN s_auart
AND vbak~augru IN s_augru
AND vbak~submi IN s_submi
AND vbak~vkorg IN s_vkorg
AND vbak~vtweg IN s_vtweg
AND vbak~spart IN s_spart
AND vbak~vkgrp IN s_vkgrp
AND vbak~vkbur IN s_vkbur
AND vbak~vsbed IN s_vsbed
AND vbak~bstnk IN s_bstnk
AND vbak~bsark IN s_bsark
AND vbak~ihrez IN s_ihrez
AND vbak~kunnr IN s_kunag
AND vbak~kvgr5 IN s_kvgr5
AND vbak~bukrs_vf IN s_bukrs
AND vbak~zzcoupon_code IN s_couph

AND vbuk~abstk IN s_abstk
AND vbuk~cmgst IN s_cmgst
AND vbuk~fsstk IN s_fsstk
AND vbuk~lsstk IN s_lsstk
AND vbuk~spstg IN s_spstg

AND vbap~matnr IN s_matnr
AND vbap~matkl IN s_matkl
AND vbap~pstyv IN s_pstyv
AND vbap~fkrel IN s_fkrel
AND vbap~abgru IN s_abgru
AND vbap~prodh IN s_prodh
AND vbap~kdmat IN s_kdmat

AND vbap~lprio IN s_lprio
AND vbap~werks IN s_werks
AND vbap~lgort IN s_lgort
AND vbap~vstel IN s_vstel
AND vbap~mvgr4 IN s_mvgr4
AND vbap~zzcoupon_code IN s_coupi

AND vbap~erdat IN s_erdat

AND vbap~matwa IN s_matwa
AND vbap~zzevent IN s_event

AND vbup~besta IN s_besta
AND vbup~lfsta IN s_lfsta
AND vbup~uvp01 IN s_uvp01
AND mara~mtart IN s_mtart
AND mara~spart IN s_spartp.

The total data volume in Quality and production system for the tables are as follows.

Table/System

Quality

Production

VBAK

8,214,881

8,506,810

VBAP

57,256,862

59,483,343

VBUK

29,716,272

30,793,909

VBUP

98,744,700

102,651,966

VBFA

333,974,838

347,307,069

KNVV

1,249,283

1,254,434

MARA

953,419

961,660

What could be the reason for this vast difference in execution time between production and quality system.

Please note, the recent data copy to quatlity from production system happned on 31st of August. We went live with
ugrade to ECC6 in this November begining.

Could upgrade has discturbed some indexes or something.

Please help.

Kind Regards,

Srirangarajan.