From derby-user-return-12562-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Apr 06 06:18:01 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 8228 invoked from network); 6 Apr 2010 06:18:01 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 6 Apr 2010 06:18:01 -0000 Received: (qmail 39178 invoked by uid 500); 6 Apr 2010 06:18:00 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 39092 invoked by uid 500); 6 Apr 2010 06:18:00 -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 39085 invoked by uid 99); 6 Apr 2010 06:18:00 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Apr 2010 06:18:00 +0000 X-ASF-Spam-Status: No, hits=-0.1 required=10.0 tests=AWL,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [91.186.27.86] (HELO mail.actualogic.com) (91.186.27.86) by apache.org (qpsmtpd/0.29) with SMTP; Tue, 06 Apr 2010 06:17:52 +0000 Received: from faspfedd ([217.67.191.38]) by mail.actualogic.com ; Tue, 6 Apr 2010 07:17:32 +0100 X-AntiVirus: Checked by Dr.Web [version: 5.0, engine: 5.00.2.03300, virus records: 1212978, updated: 6.04.2010] From: "Fedd Kraft" To: "'Derby Discussion'" References: <001801cad09b$09aef650$1d0ce2f0$@ru> <4BB31C34.6030301@sun.com> In-Reply-To: <4BB31C34.6030301@sun.com> Subject: RE: Derby internally converts char for bit data to hex string Date: Tue, 6 Apr 2010 10:17:30 +0400 Message-ID: <000601cad550$d6eebab0$84cc3010$@ru> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcrQuG6BHgmAmko3RWiDQxEWP2MNwAElmfHA Content-Language: ru Sorry for a followup.. So there are two problems: my problem is the wrong query plan, and the derby problem is (not very efficient) hex string creation when scanning an index to find a char for bit data value, with unordered nulls semantics. The latter is worth posting to Jira (I'd do that if I'd be able to reproduce it outside my project); but me personally wouldn't face that problem at all if derby chose right index. When the right index is chosen there are no phrases like "qualifiers:...Ordered nulls: false", and when debugging, the execution skips that strange 'toString' place. Thank you and please help me with the plan. Maybe I could call "select count(*) from res" periodically to update the stats? Does that really update the stats in Derby and some other systems? Fyodor -----Original Message----- From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM] Sent: Wednesday, March 31, 2010 1:56 PM To: derby-user@db.apache.org Subject: Re: Derby internally converts char for bit data to hex string On 03/31/10 08:26 AM, Fedd Kraft wrote: > Hello, > > When profiling an application I've noticed that sometimes when performing a > simple query to one table without joins, like > > select kee from res where qid=? and kee=? > > it looks like Derby searching for a row makes a lot of comparisons by > converting binary data to hex strings and then comparing them. > > And (here I am not sure) chooses to do a full scan. As the result, the whole > thing works too slow. > > The table is defined like this. > > create table res( > qid char (16) for bit data not null, > kee char (32) for bit data not null, > dnk char (32) for bit data not null > ); > create unique index res_unq on res (qid, kee, dnk); > create index res_idx on res (qid, dnk); > create index res_ix2 on res (qid, kee); > > It also looks like it does this hex string comparison when the table is > quite big. And when it is quite small, call normal byte array comparison, > and there is no 'fullscan' classes in the stack trace, and it works > ultrafast. > > Sorry there no reproducing scripts yet ready; please tell me may be I just > missing something. I thought that converting makes sense when we compare > values of different types or which may be null, but mine is not that case > (?) > Hi Fyodor, Comparisons of binary data are supposed be performed byte-wise, without converting the data to strings first, so if you can provide scripts or code to reproduce this, please file a bug report here so that it can be investigated: https://issues.apache.org/jira/browse/DERBY You may first want to check that you're not using a debug build of Derby, though, since it might be that the debug builds convert byte arrays to hex strings to do some sanity checks. (If derby.jar contains a class called SanityManager, it's a debug build.) -- Knut Anders