Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <18180020.1180964496532.JavaMail.jira@brutus>
Date: Mon, 4 Jun 2007 06:41:36 -0700 (PDT)
From: "Stefan Cordes (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-2752) PreparedStatement uses different (more
 worse) access path than Statement
In-Reply-To: <26069791.1180963775799.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


     [ https://issues.apache.org/jira/browse/DERBY-2752?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

Stefan Cordes updated DERBY-2752:
---------------------------------

    Attachment: Statement.txt

The correct access path using Statement (same with and without index)

> 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: PreparedStatement.txt, PreparedStatementwithoutIndex=
IESVN01A.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 performanc=
e 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 iso=
lation level using instantaneous share row locking:=20
> (optimizer estimated cost:         2773,96 but Rows seen =3D 108014449)
> but better was
> Hash Scan ResultSet for ESVN01 using constraint SQL070530065000411 at rea=
d committed isolation level using instantaneous share row locking:=20
> (optimizer estimated cost:         2791,96 and Rows seen =3D 10393)
> Our solution was to remove the index IESVN01A and so force the PreparedSt=
atement not to use it (as the Statement already does).
> With the removed index there are still different access paths=20
> (PreparedStatement uses an additional "Index Row to Base Row ResultSet fo=
r ESVN01" before "Hash Scan ResultSet for ESVN01")
> See differenced between StatementwithoutIndexIESVN01A.txt and PreparedSta=
tementwithoutIndexIESVN01A.txt
> As it is not clear for us if in some other cases the PreparedStatementOpt=
imizer 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_CA=
E AS PO_Type, O4Work.ESVN01.NU_MCS_SPY AS PO_SupplierNo, O4Work.ESVN01.NU_S=
T3 AS PO_StatusNo, O4Work.ESVN01.DA_SPY_COY_PRT AS PO_SCPrintDate, O4Work.E=
SVN01.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.ES=
VN02.NU_CTT AS POP_ContractNo, O4Work.ESVN02.NU_ARO_CTT AS POP_ArosContract=
No, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_Creat=
ionDate, 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 POP=
D_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,=20
> 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 S=
ER_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.E=
SVN03.DA_EDD AS POPC_Edd, O4Work.ESVN03.DA_LDD AS POPC_Ldd, O4Work.ESVN03.D=
A_PAD AS POPC_Pad, O4Work.ESVN03.DA_SAD AS POPC_Sad, O4Work.ESVN03.PR_SCP A=
S POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS POPC_SupCstPrCurr, O4Work.E=
SVN03.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.D=
A_FLR_RDY AS POPC_FRM_DATE, O4Work.ESVN03.FL_CSG AS POPC_CS_FLAG, O4Work.ES=
VN03.NU_PAK_MOD_SPY AS POPC_PackSupplNo, O4Work.ESVN03.NU_PAK_MOD_DCR AS PO=
PC_PackingDCNo, O4Work.ESVN03.NU_PS2_MOD AS POPC_PresMethodNo, O4Work.ESVN0=
4.NU_RTL_CPY AS POPRC_RetailCode,=20
> O4Work.ESVN04.PR_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN08.NU_AVE AS POPR=
CA_AdvertNo, O4Work.ESVQ00.ID_SHP AS SHP_ShippingID, O4Work.ESVQ00.NU_SHP A=
S 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, O4Wo=
rk.ESVN07.FL_ALE_RMK AS POPCU_AllocRem, O4Work.ESVN0A.DA_TE1_RQT AS POPCT_T=
icketReq FROM O4Work.ESVN02 INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BU=
Y_CPY =3D O4Work.ESVN01.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVN=
01.NU_ODR LEFT OUTER JOIN O4Work.ESVNG3 ON O4Work.ESVN01.NU_BUY_CPY =3D O4W=
ork.ESVNG3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR =3D O4Work.ESVNG3.NU_ODR INN=
ER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVNA0.NU_BUY_=
CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVNA0.NU_ODR AND O4Work.ESVN02.NU_=
PST =3D O4Work.ESVNA0.NU_PST INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_S=
RL =3D O4Work.ESVP00.ID_SRL LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.=
NU_BUY_CPY =3D O4Work.ESVNA4.NU_BUY_CPY AND=20
> O4Work.ESVNA0.NU_ODR =3D O4Work.ESVNA4.NU_ODR AND O4Work.ESVNA0.NU_PST =
=3D O4Work.ESVNA4.NU_PST LEFT OUTER JOIN O4Work.ESVNA5 ON O4Work.ESVNA0.NU_=
BUY_CPY =3D O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR =3D O4Work.ES=
VNA5.NU_ODR AND O4Work.ESVNA0.NU_PST =3D O4Work.ESVNA5.NU_PST INNER JOIN O4=
Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVN03.NU_BUY_CPY AND O4=
Work.ESVN02.NU_ODR =3D O4Work.ESVN03.NU_ODR AND O4Work.ESVN02.NU_PST =3D O4=
Work.ESVN03.NU_PST LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CP=
Y =3D O4Work.ESVN04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN=
04.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR =3D O4Work.ESVN04.NU_ODR AND O4Work.=
ESVN03.NU_PST =3D O4Work.ESVN04.NU_PST LEFT OUTER JOIN O4Work.ESVN08 ON O4W=
ork.ESVN04.NU_BUY_CPY =3D O4Work.ESVN08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL=
_CPY =3D O4Work.ESVN08.NU_RTL_CPY AND O4Work.ESVN04.NU_MT1_CPY =3D O4Work.E=
SVN08.NU_MT1_CPY AND O4Work.ESVN04.NU_ODR =3D O4Work.ESVN08.NU_ODR AND O4Wo=
rk.ESVN04.NU_PST =3D O4Work.ESVN08.NU_PST INNER JOIN O4Work.ESVQ00 ON=20
> O4Work.ESVN03.ID_SHP =3D O4Work.ESVQ00.ID_SHP INNER JOIN O4Work.ESVNB0 ON=
 O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNB0.NU_BUY_CPY AND O4Work.ESVN03.NU=
_MT1_CPY =3D O4Work.ESVNB0.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR =3D O4Work.E=
SVNB0.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Work.ESVNB0.NU_PST INNER JOIN O=
4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNE1.NU_BUY_CPY AND O=
4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVNE1.NU_MT1_CPY AND O4Work.ESVN03.NU_O=
DR =3D O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Work.ESVNE1.NU_P=
ST LEFT OUTER JOIN O4Work.ESVN0A ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESV=
N0A.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN0A.NU_MT1_CPY AN=
D O4Work.ESVN03.NU_ODR =3D O4Work.ESVN0A.NU_ODR AND O4Work.ESVN03.NU_PST =
=3D O4Work.ESVN0A.NU_PST LEFT OUTER JOIN O4Work.ESVN07 ON O4Work.ESVN03.NU_=
BUY_CPY =3D O4Work.ESVN07.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Wor=
k.ESVN07.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR =3D O4Work.ESVN07.NU_ODR AND O=
4Work.ESVN03.NU_PST =3D O4Work.ESVN07.NU_PST=20
> WHERE O4Work.ESVN01.NU_BUY_CPY =3D 99 AND O4Work.ESVNA0.NU_CPY_GRP =3D 0 =
AND O4Work.ESVNE1.NU_COT_TYP =3D 1 AND O4Work.ESVP00.NU_CSY =3D 603 AND O4W=
ork.ESVNA0.NU_SSN_IDE =3D 10=20

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.