db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fedd Kraft" <f...@sql.ru>
Subject Derby internally converts char for bit data to hex string
Date Wed, 31 Mar 2010 06:26:02 GMT
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
(?)

Thanks,
Fyodor




Mime
View raw message