Royi Ronen <[EMAIL PROTECTED]> 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
