db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-5066) full table scan when index is used, taking extremely long time in JDBC
Date Fri, 25 Feb 2011 01:12:38 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12999174#comment-12999174
] 

Bryan Pendleton commented on DERBY-5066:
----------------------------------------

> Sort information: 
> Number of merge runs=38 
> Number of rows input=69612 
> Number of rows output=69612 
> Size of merge runs=[1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791,
1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791,
1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791, 1791] 
> Sort type=external 

How big are the rows? A 38-run external sort to process 69 thousand rows seems incredible!
External sort merges, particularly with multiple runs, normally only happen when sorting vast
gigabytes of data...

Is the system for some reason extremely short on memory?


> full table scan when index is used, taking extremely long time in JDBC
> ----------------------------------------------------------------------
>
>                 Key: DERBY-5066
>                 URL: https://issues.apache.org/jira/browse/DERBY-5066
>             Project: Derby
>          Issue Type: Bug
>          Components: Eclipse Plug-in, JDBC
>    Affects Versions: 10.7.1.1
>         Environment: Windows XP environment
>            Reporter: George Xu
>
> When a very large table (500k rows) is used with a column is indexed.   select * from
tab where pid > 0 order by pid takes extremely longer time than select * from tab order
by pid.  Actually, it is 100 times slower.  However, in IJ, ther performance seems to be similar.
 PID column is indexed.
> Here is the code snipplet
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> public class TestPerformance {
> 	//20343 mill-sec
> 	static String s1 = "SELECT TIMESTAMP, HOSTPORT AS \"HOST ID\", PID, SESSIONID, REQUESTID,
" +
> 	"SUBREQUESTID, STEPID, TID, COMPONENT, BUILDNUM, " +
> 	"LOGLEVELORIG AS \"LEVEL\", LOGGER, OPERATION, OBJECTTYPE, OBJECTPATH, " +
> 	"STATUS, MESSAGE, DATA, NDX FROM LOGDATA871218 where PID > 0 ORDER BY PID";	
> 	//297 million sec.
> 	static String s2 = "SELECT TIMESTAMP, HOSTPORT AS \"HOST ID\", PID, SESSIONID, REQUESTID,
" +
> 	"SUBREQUESTID, STEPID, TID, COMPONENT, BUILDNUM, " +
> 	"LOGLEVELORIG AS \"LEVEL\", LOGGER, OPERATION, OBJECTTYPE, OBJECTPATH, " +
> 	"STATUS, MESSAGE, DATA, NDX FROM LOGDATA871218 ORDER BY PID";	
> 	public static void main(String[] args) throws InstantiationException, IllegalAccessException,
ClassNotFoundException {
> 		Statement stmt3;
> 		try {
> 			//connect'jdbc:derby:C:/devroot/runtime-New_configuration/LogXData';
> 			String db = "C:/devroot/runtime-New_configuration/LogXData";
> 			String driver = "org.apache.derby.jdbc.EmbeddedDriver";
> 			Class.forName(driver).newInstance();
> 			Connection con = DriverManager.getConnection("jdbc:derby:"+db);			
> 			stmt3 = con.createStatement(ResultSet.FETCH_FORWARD, 
> 			        ResultSet.TYPE_FORWARD_ONLY);
> 			long startTime3 = System.currentTimeMillis();
> 			ResultSet rs3 = stmt3.executeQuery(s1); 
> 			long elapsed3 = System.currentTimeMillis() - startTime3;
> 			System.out.println("Statment.executeQuery Dup: " + elapsed3); 
> 		} catch (SQLException e) {
> 			// TODO Auto-generated catch block
> 			e.printStackTrace();
> 		}			
> 	}
> }

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message