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/26 20:22:34 UTC
[jira] Created: (DERBY-4526) 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.
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 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
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: small_db. 2 of 3. Combine all 3.zip
> 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
> Attachments: java. derby logs. sysinfo.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.zip
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
> Attachments: DERBY-4526 Bug Attachment.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (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.
Posted by "Phil (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805998#action_12805998 ]
Phil commented on DERBY-4526:
-----------------------------
Hi, Knut.
I used Eclipse with Sun's JVM (versions 4-6) for the derby development which
comes with default provider.
Use Eclipse or try to set more parameters for ij as following (I got them on
the Web, You might want to double check in case they do not help):
dataEncryption=true;
bootPassword="derby_password";
encryptionProvider=com.sun.crypto.provider.SunJCE;
encryptionAlgorithm=DESede/CBC/NoPadding;
If nothing works, let me know. I will redo the databases without encryption
then.
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z02
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z03)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: small_db. 3 of 3. Combine all 3.zip
> 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
> Attachments: java. derby logs. sysinfo.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4526:
--------------------------------------
Component/s: SQL
Affects Version/s: 10.5.3.0
> 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: 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)
> */
> 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.
[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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: en_US_caseinsensitive.jar
BugDemo.java
1) BugDemo.java is updated to compile
2. en_US_caseinsensitive.jar should be placed into JAVA_HOME's JRE\LIB\EXT to avoid message "Collator support not available ..."
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: small_db. 1 of 3. Combine all 3.zip
java. derby logs. sysinfo.zip
> 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
> Attachments: java. derby logs. sysinfo.zip, small_db. 1 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4526:
--------------------------------------
Issue & fix info: [Repro attached] (was: [Repro attached, Release Note Needed])
> 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: 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)
> */
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Environment:
see sysinfo.txt in attached 'Bug Submission.zip'
was:
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.
> 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'
> Reporter: Phil Michnik
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.z07, large_db.z08, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: small_db. 2 of 3. Combine all 3.zip)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z07)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z07
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (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.
Posted by "Phil (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806405#action_12806405 ]
Phil commented on DERBY-4526:
-----------------------------
I agree.
Thank you.
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.zip)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z06
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z02
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
> Attachments: DERBY-4526 Bug Attachment.z01, DERBY-4526 Bug Attachment.z02, DERBY-4526 Bug Attachment.z03, DERBY-4526 Bug Attachment.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z04)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z03
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
> Attachments: DERBY-4526 Bug Attachment.z01, DERBY-4526 Bug Attachment.z02, DERBY-4526 Bug Attachment.z03, DERBY-4526 Bug Attachment.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z08
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.z07, large_db.z08, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z03
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ 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)
*/
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 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
}
> 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)
> */
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z01
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
> Attachments: DERBY-4526 Bug Attachment.z01, DERBY-4526 Bug Attachment.z02, DERBY-4526 Bug Attachment.z03, DERBY-4526 Bug Attachment.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z04
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: small_db. 3 of 3. Combine all 3.zip)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z05
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z01
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z02)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z05
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806284#action_12806284 ]
Knut Anders Hatlen commented on DERBY-4526:
-------------------------------------------
Sorry, my mistake... I set the java.ext.dirs property instead of copying the attached jar file into lib/ext, but that apparently prevented the JVM from loading the crypto jars it needed from lib/ext unless I also listed lib/ext in java.ext.dirs. Works now:
Bug1 test start: large_db
Bug1 test end: large_db: elapsed 5618 millis
Bug1 test start: small_db
Bug1 test end: small_db: elapsed 53488 millis
Bug2 test start: large_db
Bug2 test end: large_db: elapsed 7824 millis
Bug2 test start: small_db
Bug2 test end: small_db: elapsed 37766 millis
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.zip
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z04
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: large_db.z07
> 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
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.z07, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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 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
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4526.
-------------------------------------
Resolution: Duplicate
Closing as 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.
[jira] Commented: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805565#action_12805565 ]
Knut Anders Hatlen commented on DERBY-4526:
-------------------------------------------
It looks like there are some files missing in the zip files:
1) The BugDemo class refers to a DerbyDb class that's not included, so it doesn't compile.
2) When I try to boot the databases in ij, I get this error message: ERROR XBM04: Collator support not available from the JVM for the database's locale 'en_US_caseinsensitive'.
> 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: 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)
> */
> 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z06)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ 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 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'
}
was:
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 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
}
Environment:
see sysinfo.txt in attached 'java. derby logs. sysinfo.zip'
was:
see sysinfo.txt in attached 'Bug Submission.zip'
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
> Attachments: java. derby logs. sysinfo.zip, large_db.z01, large_db.z02, large_db.z03, large_db.z04, large_db.z05, large_db.z06, large_db.z07, large_db.z08, large_db.zip, small_db. 1 of 3. Combine all 3.zip, small_db. 2 of 3. Combine all 3.zip, small_db. 3 of 3. Combine all 3.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z09
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z01)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ 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.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z10
> 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 'java. derby logs. sysinfo.zip'
> 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 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z06
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: DERBY-4526 Bug Attachment.z08
> 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 'java. derby logs. sysinfo.zip'
> 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.zip
>
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: small_db. 1 of 3. Combine all 3.zip)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: java. derby logs. sysinfo.zip)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z08)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[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 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.
Posted by "Phil Michnik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Phil Michnik updated DERBY-4526:
--------------------------------
Attachment: (was: large_db.z05)
> 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 'java. derby logs. sysinfo.zip'
> Reporter: Phil Michnik
>
> 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'
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805934#action_12805934 ]
Knut Anders Hatlen commented on DERBY-4526:
-------------------------------------------
Thanks Phil. I'm getting closer, but I still get an error:
java.sql.SQLException: Failed to start database 'large_db', see the next exception for details.
.
.
.
ERROR XBCXC: Encryption algorithm 'DES/CBC/NoPadding' does not exist. Please check that the chosen provider 'default' supports this algorithm.
I see this with Sun's Java 6. What kind of JVM are you using? Have you installed some custom cryptography provider?
> 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.
[jira] Commented: (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.
Posted by "Phil (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805613#action_12805613 ]
Phil commented on DERBY-4526:
-----------------------------
I will resubmit it today. Thanks.
> 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: 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)
> */
> 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.
[jira] Commented: (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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805538#action_12805538 ]
Knut Anders Hatlen commented on DERBY-4526:
-------------------------------------------
Do you also see this problem if you update the statistics for FILES and FILERESTOREPOINTS in the small database?
call syscs_util.syscs_update_statistics('<SCHEMANAME>', 'FILES', null);
call syscs_util.syscs_update_statistics('<SCHEMANAME>', 'FILESTOREPOINTS', null);
> 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)
> */
> 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.