[
https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
George Xu updated DERBY-5066:
-----------------------------
just loaded the testcase. There is no memory shortage in the system as far as
I understand.
> 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
> Attachments: LogXData.rar
>
>
> 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
