Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
From: "Bergquist, Brett" <BBergquist@canoga.com>
To: "derby-dev@db.apache.org" <derby-dev@db.apache.org>
Date: Tue, 30 Aug 2011 10:10:28 -0700
Subject: RE: Performance problem with derby
Thread-Topic: Performance problem with derby
Thread-Index: AcxnIW1vGf0E2l71QmmHi7Z6EfLWJgAFkmwx
Message-ID: <97EB699F861AD841B5908C7CA9C9565601607A6B0E5A@VSERVER1.canoga.com>
References: <32364853.post@talk.nabble.com>
In-Reply-To: <32364853.post@talk.nabble.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Take a look at using the runtime statistics to retrieve the query plan and =
make sure your index is being used.=20

http://db.apache.org/derby/docs/10.8/tuning/tuningderby.pdf

I think that maybe the index is not being used for the ordering and a disk =
based sort is being done.

________________________________________
From: mogoye [sylvain.roulet@eloquant.com]
Sent: Tuesday, August 30, 2011 10:29 AM
To: derby-dev@db.apache.org
Subject: Performance problem with derby

We are experiencing some performance problem with a Derby DB.
To make short we have a table with the following columns :
clientId | callRef | callStart | callEnd | contact | .... and some others

The primary key is : (clientId, callRef).

I'm trying the following request :
SELECT * FROM CALLCDRACD WHERE clientId=3D'xxxx' ORDER BY CALLSTART OFFSET =
10
ROWS FETCH NEXT 20 ROWS ONLY

The request is executed during 15 sec with this table containing 460 000
entries (seems a lot of time).


I've added the following index : CREATE INDEX IND_CCA_CALLSTART ON
APP.CALLCDRACD(CLIENTID,CALLSTART) but now the  request took approximativel=
y
the same time to be processed... My index seems to have no effects.

We made and other test using mySQL and the same DB configuration, mySQL
returns the results within 200ms

Does someone have any explanation about this 15 sec required to process the
request on a derby DB.

--
View this message in context: http://old.nabble.com/Performance-problem-wit=
h-derby-tp32364853p32364853.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.