db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sanjaya premawardena <sanjaya.war...@gmail.com>
Subject Re: Derby Database Query/DB Tuning
Date Mon, 04 Feb 2013 03:16:12 GMT
Hi All,

Do you have any update on this?

Regards,
Sanjaya.

On 2/1/13, sanjaya premawardena <sanjaya.waruna@gmail.com> 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
>

Mime
View raw message