A query (bug1) or a view (bug2) from a smaller db is at least order of
magnitude slower than from a large 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
Environment: see sysinfo.txt in attached 'Bug Submission.zip'
I DO NOT SEE ANY LINK FOR ATTACHING A FILE. PLEASE, LET ME KNOW HOW TO ATTACH
MY FILE 'Bug Submission.zip' FOR REPRODUCING THE BUGS.
Reporter: Phil Michnik
See BugDemo.java in attached 'Bug Submission.zip'. 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
/*
* BUG 2: Derby ignores indexes on table FileRestorePoints or
rpTable when selecting from view 'rpView' of d...@small_db on tables
* ('Files', 'FileRestorePoints', and 'rpTable') although
indexes were created on each of the tables;
*
* but, in case of d...@large_db, indexes are used properly
(both databases were created from the same script)
*
* view rpView is created on Files, FileRestorePoints, and
rpTable
*/
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
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
