Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 82654 invoked from network); 19 Sep 2008 11:05:54 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Sep 2008 11:05:54 -0000 Received: (qmail 75475 invoked by uid 500); 19 Sep 2008 11:05:51 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 75181 invoked by uid 500); 19 Sep 2008 11:05:49 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 75170 invoked by uid 99); 19 Sep 2008 11:05:49 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Sep 2008 04:05:49 -0700 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [192.18.6.24] (HELO gmp-eb-inf-2.sun.com) (192.18.6.24) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Sep 2008 11:04:49 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-2-fe2.eu.sun.com [192.18.6.11]) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m8JB5K7e028162 for ; Fri, 19 Sep 2008 11:05:20 GMT Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0K7F00B01VH6RR00@fe-emea-10.sun.com> (original mail from Knut.Hatlen@Sun.COM) for derby-user@db.apache.org; Fri, 19 Sep 2008 12:05:20 +0100 (BST) Received: from localhost ([80.233.255.73]) by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0K7F00N12W4G1Q30@fe-emea-10.sun.com> for derby-user@db.apache.org; Fri, 19 Sep 2008 12:05:05 +0100 (BST) Date: Fri, 19 Sep 2008 13:05:03 +0200 From: Knut Anders Hatlen Subject: Re: Derby performance question In-reply-to: Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/22.2 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Royi Ronen 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