db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Michnik (JIRA)" <j...@apache.org>
Subject [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.
Date Tue, 26 Jan 2010 19:22:34 GMT
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.


Mime
View raw message