Derby performance question

[Royi Ronen <ro...@us.ibm.com>]

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.

I'll be happy to hear any advice regarding how to make the query run faster
on Derby.

Thanks,
Royi

Re: Derby performance question

[Knut Anders Hatlen <Kn...@Sun.COM>]

Royi Ronen <ro...@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