db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Royi Ronen <ronen...@us.ibm.com>
Subject Derby performance question
Date Thu, 18 Sep 2008 21:29:07 GMT


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.


View raw message