Delay in OQL query execution

We are forming a query which fetches data based on user search conditions selection. We have a base query on top of which user selected conditions are added under where condition. Below is final query(substituted real entity names). select distinct     w.TID,        cast(w.EC_Date as string) as  EC_Date,    cast(w.TEC_Date as string) as TEC_Date,    w.TCS as TCS,    w.DTM as DTM,    a.TSN as TSN,    wol.WOL_NME as WOL_NME,    ph.PN as PN,    CASE         WHEN du.USER_MIDDLE_NME IS NULL THEN              du.USER_FIRST_NME+' '+du.USER_LAST_NME        ELSE            du.USER_FIRST_NME+' '+du.USER_MIDDLE_NME+' '+du.USER_LAST_NME        END             as TEs,    w.WID as WID,    CASE        WHEN MY.MYN IS NULL THEN            pub.pub_NME            + '-'            + vh.vh_NME            + '-'            + pr.pr_NME        ELSE            MY.MDL_YR_CD            + '-'            + pub.pub_NME            + '-'            + vh.vh_NME            + '-'            + pr.pr_NME    END                         as TID_NME,    CASE         WHEN asg.USER_MIDDLE_NME IS NULL THEN              asg.USER_FIRST_NME+' '+asg.USER_LAST_NME        ELSE            asg.USER_FIRST_NME+' '+asg.USER_MIDDLE_NME+' '+asg.USER_LAST_NME        END             as    PH_ID_NME,    a.TID_NME+ ';'+ p.t_NAME as DVID,     cast( (SELECT COUNT(cid.SMID) as scFROM G.t_SPLS ts1left join ts1/G.t_SPLS_SPL/G.SPL sleft join s/G.CID_SPL/G.CID cidwhere cid.AFG != 'N'AND ts1.AFG != 'N'AND ts1.TID = w.TIDGROUP BY ts1.TID) as string) as spl_mv,    cast( (SELECT                        COUNT(ts.TID) AS qp        FROM            G.t tst            left join tst/G.t_SPLS_t/G.t_SPLS ts         WHERE             ts.AFG  = 'Y' and tst.WID=w.WID        GROUP BY            tst.TID    ) as string) as qty   ,    CASE        WHEN w.DTM IS NULL THEN            'N'        ELSE            'Y'    END   as DTM,    CASE        WHEN a.TSN IS NULL THEN            'N'        ELSE            'Y'    END  as PSS,    cast(w.WS_DATE as string) as WS_DATE,    cast(sef.t_SS_DATE as string) as t_SS_DATEfrom G.WO wleft join w/G.WO_t/G.t aleft join w/G.WO_WO_LC/MD.WO_LC wolleft join a/G.t_t_CENTER/MD.t_CENTER tcleft join a/G.PH_t_MAP_t/G.PH_t_MAP ptmleft join ptm/G.PH_t_MAP_PH/G.PH phleft join ph/G.PH_DP/G.DP dvpleft join dvp/G.DP_MDL_YR/MD.MDL_YR MYleft join dvp/G.DP_pub_CUSTOMER_ID/ publeft join dvp/G.DP_vh/MD.vh vhleft join dvp/G.DP_pr/ prleft join ph/G.PH_Users2_PH_ONR_ID/MD.USERS duleft join a/G.t_PRG/G.PRG pleft join a/G.t_SP_RE/G.SP_RE SP_REleft join SP_RE/G.SP_RE_SPCTN/G.SPCTN specleft join w/G.WO_USERS_asg_TNCN_ID/MD.USERS asgleft join dvp/G.DP_Users2_DP_ONR_ID/MD.USERS dvp_ownerleft join a/G.t_SPLS_t/G.t_SPLS ts ON ts.AFG='Y' or ts.AFG is nullleft join ts/G.t_SPLS_SPL/G.SPL spl ON spl.AFG='Y' or spl.AFG is nullleft join spl/G.SPL_SPL_TYPE/G.SPL_TYPE spl_type ON spl_type.AFG='Y' or spl_type.AFG is nullleft join a/G.t_t_CPNY/MD.t_CPNY a_companyleft join a/G.t_USERS_t_ORGTR/MD.USERS a_orgtrleft join w/G.SCDL_TECH_MAP_WO/G.SCDL_TECH_MAP stmleft join w/G.SCDL_EQT_FORM_WO/G.SCDL_EQT_FORM sefleft join w/G.HTR_WO_WO/G.HTR_WO hwleft join a/G.t_TDM_RQT/G.TDM_RQT tdmLEFT JOIN dvp/G.DP_OT_DP_TYPE_ID/MD.OT op_tleft join MD.USERS ra on w.LAtARVD_BY=ra.USER_IDleft join MD.USERS rvr on w.LAtRVD_BY=rvr.USER_IDleft join MD.USERS a_sbtr on w.LAtasg_BY=a_sbtr.USER_ID where a.AFG='Y' and ph.AFG='Y'  AND ( pub.pub_ID ='4' ) ORDER BY w.TID  LIMIT 500.   Any suggestions on improving performance?   Same query returns results quickly in local than in QA, Dev or Prod environments(almost data is same). 
0 answers