db-derby-dev mailing list archives

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

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


Mime
View raw message