[
https://issues.apache.org/jira/browse/DERBY-2752?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12505409
]
A B commented on DERBY-2752:
----------------------------
Thank you for providing a repro, Stefan.
I ran the test program against the DB4O4 database and confirmed that the fourth
query does indeed take much longer than the first three.
> I found out that the access path with PreparedStatement is different to the
> one of Statement
Just to be clear here, the queries that are executed by Statement and
PreparedStatement are *not* identical. The former (Statement) uses constants
in the query predicates while the latter (PreparedStatement) uses parameters.
The reason I bring this up is because when I first saw this issue, I thought
the exact same statement was running more slowly with a PreparedStatement than
with a Statement. But that is not the case.
I think the real issue here is "constants vs parameters", not "Statement vs
PreparedStatement". That is to say, if the query includes constants then the
optimizer chooses a much better plan than if the query includes parameters. Is
that an accurate assessment of the problem you are seeing?
If so, I think this behavior actually makes sense to *some* degree. The reason
is that when using constants, the optimizer can look at row count statistics to
figure out how many rows in the table are going to match the predicates. Then
it can pick a query plan that more closely aligns with the expected row count.
If the constants are replaced with parameters, though, the optimizer does not
know how many rows are actually going to match the predicate, and so must take
a guess as to what that number will be. This guess introduces some uncertainty
into the optimizer's cost estimates. And when combined with other known issues
in Derby--esp. DERBY-1905 and DERBY-1907--this uncertainty in the cost
estimates could then cause the optimizer to choose a bad plan.
Note that I agree with Stefan: the query in question should not be taking over
an hour to execute, so something is definitely off here. I just wanted to
clarify that the issue appears to be the use of parameters (rather than the use
of PreparedStatements per se).
> Our solution was to remove the index IESVN01A and so force the
> PreparedStatement not to use
> it (as the Statement already does).
Another option for working around this issue would be to use optimizer
overrides to indicate that you want a specific constraint to be used (instead
of the index). See:
http://db.apache.org/derby/docs/dev/tuning/ctunoptimzoverride.html
You can also specify a join strategy if you find one that works best. I
understand that that is not ideal, but it may provide you with more control of
your queries in the short term...
> PreparedStatement uses different (more worse) access path than Statement
> ------------------------------------------------------------------------
>
> 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
> 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.
