You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Fedd Kraft <fe...@sql.ru> on 2010/03/31 08:26:02 UTC
Derby internally converts char for bit data to hex string
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
RE: Derby internally converts char for bit data to hex string
Posted by Fedd Kraft <fe...@sql.ru>.
Knut, thank you, updating the statistics helped very much!
(I added a tunable stats updater for different servers to my software)
Fyodor
-----Original Message-----
From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
Sent: Tuesday, April 06, 2010 1:30 PM
To: derby-user@db.apache.org
Subject: Re: Derby internally converts char for bit data to hex string
///
This section in the tuning guide lists when the index cardinality
statistics are automatically updated:
http://db.apache.org/derby/docs/10.5/tuning/ctunstats57373.html
Additionally, there is a system procedure that can be used to update the
statistics manually:
http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html
Re: Derby internally converts char for bit data to hex string
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 04/ 6/10 08:17 AM, Fedd Kraft wrote:
> 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);
Thanks for posting the extra info. I've logged this in JIRA as
DERBY-4608, and I think there is a simple fix for it (use isNull()
instead of getString() to check if the value is null when comparing the
values).
> 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.
>
You can tell the optimizer to always use a certain index with an
optimizer override, like this:
select kee from res --derby-properties index=name_of_index
where dnk=? and qid=?
See these sections in the tuning guide for more details:
http://db.apache.org/derby/docs/10.5/tuning/ctundepthoptover.html
http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html
> 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?
>
It will update the estimated row count for the table, but it won't
update the index cardinality statistics that are used by the optimizer
to find which index to choose.
This section in the tuning guide lists when the index cardinality
statistics are automatically updated:
http://db.apache.org/derby/docs/10.5/tuning/ctunstats57373.html
Additionally, there is a system procedure that can be used to update the
statistics manually:
http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html
Hope this helps,
--
Knut Anders
RE: Derby internally converts char for bit data to hex string
Posted by Fedd Kraft <fe...@sql.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
Re: Derby internally converts char for bit data to hex string
Posted by Fedd Kraft <fe...@sql.ru>.
31.03.2010 13:56, Knut Anders Hatlen пишет:
> 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.)
>
>
Hi Knut,
unfortunately I cannot yet reproduce this without my bigger project. My
version in use has no SanityManager, and if I'd want to see which method
Derby uses for the comparison, I'd have to use the debug version...
Anyway, looking at the code, I see (db-derby-10.5.3.0-src.zip) that in
the method org.apache.derby.iapi.types.SQLBinary.compare(int op,
DataValueDescriptor other, boolean orderedNulls, boolean unknownRV) is
called with orderedNulls parameter set to false, it always creates hex
strings, see:
public final boolean compare(int op,
DataValueDescriptor other,
boolean orderedNulls,
boolean unknownRV)
throws StandardException
{
if (!orderedNulls) // nulls are unordered
{
// sanity manager stuff skipped
String otherString = other.getString(); // <==== line 574 HERE IS HEX
STRING CREATION
if (this.getString() == null || otherString == null) // <=== AND HERE
return unknownRV;
}
return super.compare(op, other, orderedNulls, unknownRV);
}
The hex string is created through StringBuffer tens of thounsands times
according to profiler; (and the debugger stopped on line 574, when used
a debug build). Well, I was wrong that hex strings are compared; they
are prepared only to know whether any of the values is null.
There is also a query plan problem with this table, and also when the
table grows big:
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);
The query is
select kee from res where dnk=? and qid=?
And here are two plans, one for big "res" table, and another for small.
The first plan shows that the wrong index was chosen, and we read too
many records to find the result. The table is populated right before it
gets queried; Maybe there is some statistic stuff, but I would like to
avoid running some Derby-specific calls in my application...
Thank you!
=================================
2010-04-05 18:27:29.062 GMT
Thread[mety.store-("zipcode",("city",(*VALUE*,(&5022740b-a32a-4d4a-8a38-511811e32dc5)))),5,m
ain] (XID = 1364486), (SESSIONID = 5), select kee from res where dnk=?
and qid=? ******* Project-Restrict ResultSet (2):
Number of opens = 34
Rows seen = 34
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1488,65
optimizer estimated cost: 12706,02
Source result set:
Index Scan ResultSet for RES using index RES_UNQ at read committed
isolation level using instantaneous share row
locking chosen by the optimizer
Number of opens = 34
Rows seen = 34
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=1711
Number of rows qualified=1
Number of rows visited=43192
Scan type=btree
Tree height=4
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
Column[0][0] Id: 2
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 1488,65
optimizer estimated cost: 12706,02
==========================================================================
2010-04-05 18:54:19.171 GMT
Thread[mety.store-("zipcode",("city",(*VALUE*,(&42d96ab6-4dc3-45d0-87d9-2cdea975db97)))),5,m
ain] (XID = 33823), (SESSIONID = 5), select kee from res where dnk=? and
qid=? ******* Project-Restrict ResultSet (3):
Number of opens = 214
Rows seen = 274
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 28,53
optimizer estimated cost: 277,09
Source result set:
Index Row to Base Row ResultSet for RES:
Number of opens = 214
Rows seen = 274
Columns accessed from heap = {0, 1, 2}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 28,53
optimizer estimated cost: 277,09
Index Scan ResultSet for RES using index RES_IDX at read committed
isolation level using instantaneous s
hare row locking chosen by the optimizer
Number of opens = 214
Rows seen = 274
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=4
Number of rows qualified=1
Number of rows visited=2
Scan type=btree
Tree height=3
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 2 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 28,53
optimizer estimated cost: 277,09
Re: Derby internally converts char for bit data to hex string
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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