Hi All, Do you have any update on this?
Regards, Sanjaya. On 2/1/13, sanjaya premawardena <[email protected]> wrote: > Hi All, > > > I am new to Derby database and I am trying to execute below query > against Derby 10.2 database on win 7 32bit. (512MB RAM, Intel Celeron > 2GhHz) > > > SELECT E.ID_EV, TY_EV_MNT, TS_EV_MNT_EF, TS_EV_MNT_EP, UN_PRI_EV, > UN_DG_LS_PRC, MO_CHN_PRN_UN_PRC, TY_CHN_PRN_UN_PRC, PPCI.MO_OVRD_PRC > FROM > CO_EV E > JOIN > CO_EV_MNT EVMNT > ON EVMNT.ID_EV = E.ID_EV AND EVMNT.ID_STR_RT = E.ID_STR_RT > JOIN > MA_PRC_ITM IPM ON > IPM.ID_EV = EVMNT.ID_EV AND IPM.ID_STR_RT = EVMNT.ID_STR_RT > JOIN > TR_CHN_PRN_PRC PPC ON > PPC.ID_EV = IPM.ID_EV AND PPC.ID_STR_RT = IPM.ID_STR_RT > JOIN > MA_ITM_PRN_PRC_ITM PPCI ON > PPCI.ID_EV = PPC.ID_EV AND PPCI.ID_STR_RT = PPC.ID_STR_RT > LEFT JOIN > HI_ITM_PRC_ACTN HIPA ON > HIPA.ID_EV = E.ID_EV AND HIPA.ID_STR_RT = E.ID_STR_RT AND HIPA.ID_ITM > = PPCI.ID_ITM > WHERE > TS_EV_MNT_EF <= '2013-01-22-09.18.48.104000' AND PPCI.ID_STR_RT = > '07792' AND PPCI.ID_ITM = '100168361' AND (HIPA.ACTN_REQ='F' OR > HIPA.ACTN_REQ IS NULL) > > This query is taking 11-12 secs to execute, consuming 100% CPU usage. > > Without the last Left Join and last "AND" clause in the WHERE clause; ie > "LEFT JOIN > HI_ITM_PRC_ACTN HIPA ON > HIPA.ID_EV = E.ID_EV AND HIPA.ID_STR_RT = E.ID_STR_RT AND HIPA.ID_ITM > = PPCI.ID_ITM ....AND (HIPA.ACTN_REQ='F' OR HIPA.ACTN_REQ IS NULL)" > The query is executing fine within 1-2 sec of time. > > All the tables have a row count of ~54000 except for "HI_ITM_PRC_ACTN" > table. (positioned in the last LEFT JOIN) It contains only 6 rows. Is > there any way to tune this query? I can't move the LEFT JOIN upward in > the join order, because of this condition, HIPA.ID_ITM = PPCI.ID_ITM. > > According to the execution plan, no full table scans are happening. Is > there any way to tune this query/db setting or do I have to increase > CPU or RAM on the machine? > > Following indexes are also in place; > > CO_EV:- > CO_EV_IDX --> ID_EV_EXT,ID_STR_RT > SQL130122081202580 --> ID_EV,ID_STR_RT Primary Index > > HI_ITM_PRC_ACTN:- > SQL130122081201100 --> ID_EV,ID_ITM,ID_STR_RT Primary Index > > CO_EV_MNT:- > IDX_CO_EV_MNT_1 --> TS_EV_MNT_EF,TS_EV_MNT_EP > IDX_CO_EV_MNT_2 --> TS_EV_MNT_EF > SQL130122081202650 --> ID_EV,ID_STR_RT Primary Index > > MA_PRC_ITM:- > SQL130122081202740 --> ID_EV,ID_STR_RT Primary Index > > TR_CHN_PRN_PRC:- > SQL130122081202900 --> ID_EV,ID_STR_RT Primary Index > > MA_ITM_PRN_PRC_ITM:- > IDX_MA_ITM_PRN_PRC_CHN_1--> ID_ITM,ID_STR_RT > SQL130122081202770 --> ID_EV,ID_STR_RT,ID_ITM Primary Index > > > > Really appreciate your help on this. > > Regards, > Sanjaya >
