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
