Details
Minor Description
On our application we are executing the SQL below via a PreparedStatement. The execution time is minutes.
When trying to reproduce the problem with the DBVisualizer the performance was good.
After writing a small test program (see attachement) I found out that the access path with PreparedStatement is different to the one of Statement:
Hash Scan ResultSet for ESVN01 using index IESVN01A at read committed isolation level using instantaneous share row locking:
(optimizer estimated cost: 2773,96 but Rows seen = 108014449)
but better was
Hash Scan ResultSet for ESVN01 using constraint SQL070530065000411 at read committed isolation level using instantaneous share row locking:
(optimizer estimated cost: 2791,96 and Rows seen = 10393)
Our solution was to remove the index IESVN01A and so force the PreparedStatement not to use it (as the Statement already does).
With the removed index there are still different access paths
(PreparedStatement uses an additional "Index Row to Base Row ResultSet for ESVN01" before "Hash Scan ResultSet for ESVN01")
See differenced between StatementwithoutIndexIESVN01A.txt and PreparedStatementwithoutIndexIESVN01A.txt
As it is not clear for us if in some other cases the PreparedStatementOptimizer fails this issue should be solved anyway.
— SQL with values: —
SELECT O4Work.ESVN01.NU_BUY_CPY AS PO_BuyCompanyNo, O4Work.ESVN01.NU_ODR AS PO_Number, O4Work.ESVN01.NA_SEN AS PO_ShortName, O4Work.ESVN01.FL_ODR_CAE AS PO_Type, O4Work.ESVN01.NU_MCS_SPY AS PO_SupplierNo, O4Work.ESVN01.NU_ST3 AS PO_StatusNo, O4Work.ESVN01.DA_SPY_COY_PRT AS PO_SCPrintDate, O4Work.ESVN01.FL_SAS AS PO_SeasFlag, O4Work.ESVNA5.ID_PTE AS POPA_PictureID, O4Work.ESVNG3.NU_ODR AS ON_ID, O4Work.ESVN02.NU_PST AS POP_Position_Id, O4Work.ESVN02.NU_CTT AS POP_ContractNo, O4Work.ESVN02.NU_ARO_CTT AS POP_ArosContractNo, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_CreationDate, O4Work.ESVN02.NA_CAE_USE AS POP_CreationUID, O4Work.ESVN02.DA_LAT_AMD AS POP_LastAmendDate, O4Work.ESVN02.NA_LAT_AMD_USE AS POP_LastAmendUID, O4Work.ESVN02.NA_SPY_STL AS POP_SuppStyle, O4Work.ESVNA0.NU_SSN_IDE AS POPD_SeasonInd, O4Work.ESVNA0.NA_PDE AS POPD_ProductName, O4Work.ESVNA0.NA_PDE_VSN AS POPD_ProdVerName, O4Work.ESVNA0.NU_STL_ID1 AS POPD_StyleId, O4Work.ESVNA0.NU_SRY_ID1 AS POPD_StoryID,
O4Work.ESVNA0.NU_LC1 AS POPD_LicenseID, O4Work.ESVNA0.NA_DSN_ID1 AS POPD_DesignID, O4Work.ESVNA4.NA_HAG AS POPDH_HangerName, O4Work.ESVP00.NU_CSY AS SER_ClassNo, O4Work.ESVP00.NU_COE AS SER_CodeNo, O4Work.ESVP00.NU_SRL AS SER_SerialNo, O4Work.ESVP00.NU_PIK_MOD AS SER_PickingM, O4Work.ESVN03.NU_MT1_CPY AS POPC_MasterCpyNo, O4Work.ESVN03.QU_ODR AS POPC_OrderedQty, O4Work.ESVN03.DA_EDD AS POPC_Edd, O4Work.ESVN03.DA_LDD AS POPC_Ldd, O4Work.ESVN03.DA_PAD AS POPC_Pad, O4Work.ESVN03.DA_SAD AS POPC_Sad, O4Work.ESVN03.PR_SCP AS POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS POPC_SupCstPrCurr, O4Work.ESVN03.NU_ST3 AS POPC_StatusNo, O4Work.ESVN03.NU_COY_FRM_ODR AS POPC_Src_PO_Number, O4Work.ESVN03.NU_COY_UTL_ODR AS POPC_Tgt_PO_Number, O4Work.ESVN03.DA_FLR_RDY AS POPC_FRM_DATE, O4Work.ESVN03.FL_CSG AS POPC_CS_FLAG, O4Work.ESVN03.NU_PAK_MOD_SPY AS POPC_PackSupplNo, O4Work.ESVN03.NU_PAK_MOD_DCR AS POPC_PackingDCNo, O4Work.ESVN03.NU_PS2_MOD AS POPC_PresMethodNo, O4Work.ESVN04.NU_RTL_CPY AS POPRC_RetailCode,
O4Work.ESVN04.PR_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN08.NU_AVE AS POPRCA_AdvertNo, O4Work.ESVQ00.ID_SHP AS SHP_ShippingID, O4Work.ESVQ00.NU_SHP AS SHP_ShippingNo, O4Work.ESVNB0.NU_NTL_PDE_ID1 AS POPDC_NationalID, O4Work.ESVNB0.NU_EQP AS POPDC_EquipNumber, O4Work.ESVNE1.PE_OMU AS POPCC_OMU, O4Work.ESVN07.FL_ALE_RMK AS POPCU_AllocRem, O4Work.ESVN0A.DA_TE1_RQT AS POPCT_TicketReq FROM O4Work.ESVN02 INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN01.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN01.NU_ODR LEFT OUTER JOIN O4Work.ESVNG3 ON O4Work.ESVN01.NU_BUY_CPY = O4Work.ESVNG3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR = O4Work.ESVNG3.NU_ODR INNER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVNA0.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVNA0.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVNA0.NU_PST INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_SRL = O4Work.ESVP00.ID_SRL LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.NU_BUY_CPY = O4Work.ESVNA4.NU_BUY_CPY AND
O4Work.ESVNA0.NU_ODR = O4Work.ESVNA4.NU_ODR AND O4Work.ESVNA0.NU_PST = O4Work.ESVNA4.NU_PST LEFT OUTER JOIN O4Work.ESVNA5 ON O4Work.ESVNA0.NU_BUY_CPY = O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR = O4Work.ESVNA5.NU_ODR AND O4Work.ESVNA0.NU_PST = O4Work.ESVNA5.NU_PST INNER JOIN O4Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN03.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN03.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVN03.NU_PST LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN04.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN04.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN04.NU_PST LEFT OUTER JOIN O4Work.ESVN08 ON O4Work.ESVN04.NU_BUY_CPY = O4Work.ESVN08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL_CPY = O4Work.ESVN08.NU_RTL_CPY AND O4Work.ESVN04.NU_MT1_CPY = O4Work.ESVN08.NU_MT1_CPY AND O4Work.ESVN04.NU_ODR = O4Work.ESVN08.NU_ODR AND O4Work.ESVN04.NU_PST = O4Work.ESVN08.NU_PST INNER JOIN O4Work.ESVQ00 ON
O4Work.ESVN03.ID_SHP = O4Work.ESVQ00.ID_SHP INNER JOIN O4Work.ESVNB0 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNB0.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNB0.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNB0.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNB0.NU_PST INNER JOIN O4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNE1.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNE1.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNE1.NU_PST LEFT OUTER JOIN O4Work.ESVN0A ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN0A.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN0A.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN0A.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN0A.NU_PST LEFT OUTER JOIN O4Work.ESVN07 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN07.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN07.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN07.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN07.NU_PST
WHERE O4Work.ESVN01.NU_BUY_CPY = 99 AND O4Work.ESVNA0.NU_CPY_GRP = 0 AND O4Work.ESVNE1.NU_COT_TYP = 1 AND O4Work.ESVP00.NU_CSY = 603 AND O4Work.ESVNA0.NU_SSN_IDE = 10