db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby performance question
Date Fri, 19 Sep 2008 11:05:03 GMT
Royi Ronen <ronenroy@us.ibm.com> writes:

> Hi,
>
> I am running the following query with Derby. All columns are indexed and of
> type varchar. ID is a primary key.
>
> SELECT distinct event.ID, people.ID
>
> FROM pstore AS event, pstore AS people, queries as q, queries as q2, xindex as
> x, xindex as x2
>
> WHERE event.ID = 100393 AND
> people.TYPEURI = 'http://www.research.ibm.com/maricopav2/wefinance/people' AND
> q.xpath = '/fn:filenetEvent/fn:actor/text()' AND
> q.queryid = x.queryid AND
> x.result = 'lisa' AND
> x.pstoreid = event.appID AND
> x2.result = x.result AND
> q2.xpath = '/p:employee/fn:actor/text()' AND
> q2.queryid = x2.queryid AND
> x2.pstoreid = people.appID
>
> Already at a few hundred rows for the tables pstore and xindex (table queries
> is very small), the performance is terribly low.
>
> Running the same query with the exact same indexes and data on DB2 gives good
> running times results, even for much larger tables.

Hi Royi,

It may be the case that the index statistics have become out of
date. This is a problem many others have come across, and there is work
in progress to get the statistics updated automatically. Please take a
look at this posting and see if the suggestions help:
http://article.gmane.org/gmane.comp.apache.db.derby.user/9707

-- 
Knut Anders

Mime
View raw message