db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2752) PreparedStatement uses different (more worse) access path than Statement
Date Fri, 15 Jun 2007 23:57:25 GMT

    [ 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.


Mime
View raw message