You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Phil Michnik (JIRA)" <ji...@apache.org> on 2010/01/27 14:22:34 UTC
[jira] Updated: (DERBY-4526) 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.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Description:
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)
*
* 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 in the attachment
}
was:
See BugDemo.java in attached 'java. derby logs. sysinfo.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 in 'java. derby logs. sysinfo.zip'
/*
* BUG 2: Derby ignores indexes on table FileRestorePoints or rpTable when selecting from view 'rpView' of db@small_db on tables
* ('Files', 'FileRestorePoints', and 'rpTable') although indexes were created on each of the tables;
*
* but, in case of db@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 in 'java. derby logs. sysinfo.zip'
}
Environment: see sysinfo.txt in the attachment (was: see sysinfo.txt in attached 'java. derby logs. sysinfo.zip'
)
Summary: 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. (was: 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.)
Unzip the attachment file DERBY-4526 Bug Attachment.zip
See and run file BugDemo.java
> 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
> Environment: see sysinfo.txt in the attachment
> Reporter: Phil Michnik
> Attachments: 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
>
>
> 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)
> *
> * 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 in the attachment
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.