[
https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806318#action_12806318
]
Knut Anders Hatlen commented on DERBY-4526:
-------------------------------------------
Updating the statistics seems to help. I connected to the small_db database
with ij and executed these statements:
ij> call syscs_util.syscs_update_statistics('APP', 'FILES', null);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_update_statistics('APP', 'FILERESTOREPOINTS', null);
0 rows inserted/updated/deleted
Now, the demo runs faster with small_db than with large_db:
Bug1 test start: large_db
Bug1 test end: large_db: elapsed 6063 millis
Bug1 test start: small_db
Bug1 test end: small_db: elapsed 4476 millis
Bug2 test start: large_db
Bug2 test end: large_db: elapsed 8115 millis
Bug2 test start: small_db
Bug2 test end: small_db: elapsed 7870 millis
The tuning guide contains more information about the problem with stale
statistics: http://db.apache.org/derby/docs/10.5/tuning/ctunstats46438.html
There's another issue (DERBY-3788) tracking the work to get the statistics
updated automatically when they become stale. I suggest we close this issue as
a duplicate of DERBY-3788.
> A query (bug1) or a view (bug2) from a smaller db is at least order of
> magnitude slower than from a larger db despite indexes; the smalller db
> stores a subset of data stored in the larger db; the databases were created
> using the same script.
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4526
> URL: https://issues.apache.org/jira/browse/DERBY-4526
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.3.0
> Environment: see sysinfo.txt in the attachment
> Reporter: Phil Michnik
> Attachments: BugDemo.java, DERBY-4526 Bug Attachment.z01, DERBY-4526
> Bug Attachment.z02, DERBY-4526 Bug Attachment.z03, DERBY-4526 Bug
> Attachment.z04, DERBY-4526 Bug Attachment.z05, DERBY-4526 Bug Attachment.z06,
> DERBY-4526 Bug Attachment.z07, DERBY-4526 Bug Attachment.z08, DERBY-4526 Bug
> Attachment.z09, DERBY-4526 Bug Attachment.z10, DERBY-4526 Bug Attachment.zip,
> en_US_caseinsensitive.jar
>
>
> See BugDemo.java in the attachment. Below is a code snippet.
> public BugDemo()
> {
> /*
> * BUG 1: Although Derby uses indexes, "Hash table size =
> 31416" (see small_derby1.log) is the suspect in case of small_db
> */
> String sql = "select f.file_id, f.name, f.is_directory from
> Files f, FileRestorePoints p where p.file_id=f.file_id"
> +" and p.restore_point_id=1 and
> f.parent_folder_id=?";
> demo("Bug1", large_db, sql); //no problem, 96547 rows, 8312
> milliseconds on my PC
> demo("Bug1", small_db, sql); //too slow, 31416 rows, 504703
> milliseconds on my PC, see small_derby1.log in the attachment
>
> /*
> * BUG 2: Derby ignores indexes on table FileRestorePoints or
> rpTable when selecting from view 'rpView' of small_db on tables
> * ('Files', 'FileRestorePoints', and 'rpTable') although
> indexes were created on each of the tables;
> *
> * but, in case of large_db, indexes are used properly (both
> databases were created from the same script)
> */
> sql = "select file_id, name, length, is_directory from rpView
> where parent_folder_id=?";
> demo("Bug2", large_db, sql); //no problem, 96547 rows, 13718
> milliseconds on my PC
> demo("Bug2", small_db, sql); //too slow, 31416 rows, 204110
> milliseconds on my PC, see small_derby2.log in the attachment
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
