db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fedd Kraft" <f...@sql.ru>
Subject RE: Derby internally converts char for bit data to hex string
Date Tue, 06 Apr 2010 06:17:30 GMT
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?


-----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
> 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
> 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

View raw message