Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <752733512.79331264597834720.JavaMail.jira@brutus.apache.org>
Date: Wed, 27 Jan 2010 13:10:34 +0000 (UTC)
From: "Phil Michnik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [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.
In-Reply-To: <1258068516.43821264533754635.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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.