[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13553223#comment-13553223
]
Mike Matrigali commented on DERBY-6011:
---------------------------------------
The costing is based a some very simplistic tests run at store level on a
machine from probably 10 years ago. I don't know if the actual relative
performance is valid anymore, but as an
explanation for what you are seeing I can give the following. The cost comes
from a couple of things, where #2 below is usually the reason why the cost of
looking up base row is higher
than index:
1) finding the beginning of the right row on the page (whether it is an index
or a base page)
2) copying the data out of store into the datastructure provided by language.
For #1 the rowlocation is not an actual offset on the page in the base table -
it is the ID of the row, so in the worst case you may have to do some sort of
sequencial scan of the offset table to
find the row. There are hints and caches that try to optimize this. In the
index case there is a binary search to find the row.
For #2 at least at the time was a very dominent part of the cost, and at least
for the estimate is linearly related to the length of the data being gotten.
With estimates necessary about the
length of the data based on datatype and I think the number of rows and the
number of pages in the table - this estimate may be a problem again in a very
short table or maybe does not handle allocated but not used pages very well.
In general index length is much shorter than data length. I know there are
problems with length cost estimates for blobs for instance.
> Derby performs very badly (seems to deadlock and timeout) in very simple
> multi-threaded tests
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-6011
> URL: https://issues.apache.org/jira/browse/DERBY-6011
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.7.1.1, 10.8.2.2, 10.9.1.0
> Environment: Lenovo laptop with SSD's, Windows 7, 64-bit, Sun JDK
> 1.6.xx
> Reporter: Karl Wright
> Attachments: derby.log, force-specific-index.diff, manifoldcf.log
>
>
> The Apache ManifoldCF project supports Derby as one of its underlying
> databases. Simple tests, however, demonstrate that Derby is apparently
> deadlocking and timing out repeatedly under multi-thread conditions. This
> problem is long-standing, and is not exhibited by any other database
> ManifoldCF supports, and makes a simple test take between 6x and 12x as long.
> There is a trivial test with demonstrates the problem vs. other databases.
> Please do the following (once you have java 1.6+, svn 1.7+, and ant 1.7+
> available):
> (1) Check out https://svn.apache.org/repos/asf/manifoldcf/trunk
> (2) Run the following ant target to download the dependencies: "ant
> make-core-deps"
> (3) Run the Derby test: "ant run-rss-tests-derby" . Note the time required -
> at least 180 seconds, can be up to 360 seconds.
> (4) Run the equivalent HSQLDB test: "ant run-rss-tests-HSQLDB". This test
> takes about 31 seconds to run.
> The output of the Derby test can be found in the directory
> "tests/rss/test-derby-output". Have a look at manifoldcf.log, where all
> long-running queries are reported. Derby.log is also included, which shows
> only that during the test's cleanup phase the database is deleted before it
> is shutdown, which is not pertinent to the performance issue.
> I am available to assist with ManifoldCF, if that seems to be required.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
