[
https://issues.apache.org/jira/browse/DERBY-2752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stefan Cordes updated DERBY-2752:
---------------------------------
Priority: Minor (was: Major)
Urgency: Normal (was: Urgent)
Summary: PreparedStatement with parameters uses different (more worse)
access path than Statement with constants (was: PreparedStatement uses
different (more worse) access path than Statement)
Hi A B!
I agree that these are different queries from optimizers point of view.
The optimization hints for Derby a quite nice but as our application is
intended to run on z/OS DB2 (Online) and Derby (Offline)
without major changes, the "--DERBY-PROPERTIES" would be the last option to
choose for us.
As we have several possibilities now to influence the access path I changed the
prio of this issue to "Minor"
> PreparedStatement with parameters uses different (more worse) access path
> than Statement with constants
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2752
> URL: https://issues.apache.org/jira/browse/DERBY-2752
> Project: Derby
> Issue Type: Bug
> Components: Performance
> Affects Versions: 10.2.2.0
> Environment: Windows XP, JDK 1.4.2
> Reporter: Stefan Cordes
> Priority: Minor
> Attachments: derby-sql-26965.zip, DerbySQL26965.java,
> PreparedStatement.txt, PreparedStatementwithoutIndexIESVN01A.txt,
> Statement.txt
>
>
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
