Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 8756 invoked from network); 24 Sep 2009 01:22:49 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 24 Sep 2009 01:22:49 -0000 Received: (qmail 26549 invoked by uid 500); 24 Sep 2009 01:22:49 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 26481 invoked by uid 500); 24 Sep 2009 01:22: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 26467 invoked by uid 99); 24 Sep 2009 01:22:49 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Sep 2009 01:22:49 +0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [173.15.87.35] (HELO dbrack01.segel.com) (173.15.87.35) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Sep 2009 01:22:38 +0000 Received: from Desktop02 (173-15-87-33-Illinois.hfc.comcastbusiness.net [173.15.87.33]) by dbrack01.segel.com (Postfix - We shoot spammers on site.) with ESMTP id B020F4F10F for ; Wed, 23 Sep 2009 20:30:02 -0500 (CDT) Reply-To: From: Sender: "Michael Segel" To: "'Derby Discussion'" References: <97687.71494.qm@web58507.mail.re3.yahoo.com> Subject: RE: Horrible performance - how can I reclaim table space? Date: Wed, 23 Sep 2009 20:22:16 -0500 Organization: MSCC Message-ID: <08871F4922474678A13D6250EBF7EE42@Desktop02> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01CA3C8B.8B327160" X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <97687.71494.qm@web58507.mail.re3.yahoo.com> Thread-Index: Aco8tA39ovHXmTcpTLCJKT78ZhpNCAAASCrQ X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------=_NextPart_000_0006_01CA3C8B.8B327160 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit I'm confused. How many rows are actually in the table? Also the blob is stored in the same table not stored separately? (Sorry, but I'm working in a couple of different databases so I'm always forgetting if Derby allows for detached blobs .) But if your table really only has 13 rows, it will always do a sequential scan. _____ From: T K [mailto:sanokistoka@yahoo.com] Sent: Wednesday, September 23, 2009 8:04 PM To: derby-user@db.apache.org Subject: Horrible performance - how can I reclaim table space? We have a horrific performance issue with a table of 13 rows, each one containing a very small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan: Source result set: Table Scan ResultSet for SOMETABLE at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of columns fetched=4 Number of pages visited=8546 Number of rows qualified=13 Number of rows visited=85040 optimizer estimated cost: 787747.94 So I assume I have over 85,000 dead rows in the table, and compressing it does not reclaim the space. In fact, because we keep adding and deleting rows, the performance gets worse by the hour, and according to the above plan, Derby has processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want to optimize this table scan before I resort to indices and/or reusing rows. This is with Derby 10.3 Any thoughts? Thanks ------=_NextPart_000_0006_01CA3C8B.8B327160 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

I’m = confused.

 

How many rows are actually in the = table? Also the blob is stored in the same table not stored = separately?

(Sorry, but I’m working in a = couple of different databases so I’m always forgetting if Derby allows for detached blobs = …)

 

But if your table really only has = 13 rows, it will always do a sequential scan.

 

From: T K [mailto:sanokistoka@yahoo.com]
Sent: Wednesday, = September 23, 2009 8:04 PM
To: = derby-user@db.apache.org
Subject: Horrible = performance - how can I reclaim table space?

 

We have a = horrific performance issue with a table of 13 rows, each one containing a very = small blob, because the table is presumably full of dead rows and we are table-scanning; here's part of the explain plan:

            &= nbsp;           Source result set:
            &= nbsp;           &n= bsp;       Table Scan ResultSet for SOMETABLE at read committed isolation level = using instantaneous share row locking chosen by the optimizer
            &= nbsp;           &n= bsp;           &nb= sp;   Number of columns fetched=3D4
            &= nbsp;           &n= bsp;           &nb= sp;   Number of pages visited=3D8546
            &= nbsp;           &n= bsp;           &nb= sp;   Number of rows qualified=3D13
            &= nbsp;           &n= bsp;           &nb= sp;   Number of rows visited=3D85040
            &= nbsp;           &n= bsp;           &nb= sp;   optimizer estimated cost:       = 787747.94

So I assume I have over 85,000 dead rows in the table, and compressing = it does not reclaim the space. In fact, because we keep adding and deleting = rows, the performance gets worse by the hour, and according to the above plan, = Derby has = processed over 32MB of data just to match 4 of the 13 rows. For the time being, I want = to optimize this table scan before I resort to indices and/or reusing rows. = This is with Derby 10.3

Any thoughts?

Thanks

 

------=_NextPart_000_0006_01CA3C8B.8B327160--