You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "George Xu (JIRA)" <ji...@apache.org> on 2011/02/24 04:50:38 UTC
[jira] Created: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
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
[jira] [Commented] (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13464001#comment-13464001 ]
Kathey Marsden commented on DERBY-5066:
---------------------------------------
Have you tried updating the statistics or using a later Derby version where that is automatic?
> 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
> Labels: derby_triage10_9
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
Attachment: (was: LogXData.rar)
> 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
[jira] [Updated] (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "Mamta A. Satoor (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5066:
-----------------------------------
Issue & fix info: High Value Fix (was: High Value Fix,Newcomer)
Urgency: Normal (was: Urgent)
Labels: derby_triage10_9 (was: )
> 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
> Labels: derby_triage10_9
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
Attachment: LogXData.rar
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
1) Indeed, the number of rows is much less in the execution plan because I used a smaller case to generate the plan.
2) It is true that I did not do fetch. As the result is pretty sometimes (up to 500k or 1000k for example), the app never expects an user to fetch all rows. We page first 2000 rows actually. I assume that the "executeQuery" would only compile the plan and return the pointer to the first row. However, this does not look like the case in Derby. I run in IJ, in both cases, there is such a long delay before the rows sart to move.
3) The code is a complete snipplet that we are return the first row, and then it is up to the user to page the resultset.
4) The reason the I had "PID > 0" is represent a case when a user entered a zero in UI, than made the sql look like that. It is expected the "PID >0" can cause full table scan because all PID is bigger than zero, however, the full table scan shall not happen when the fetch start...not do the actual full table scan.
I tried the following code and it did not help
CallableStatement cs2 = con.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?,?,?)");
cs2.setString(1, "APP");
cs2.setString(2, "LOGDATA871218");
cs2.setShort(3, (short) 1);
cs.execute();
The new plan looks like this
Statement Text:
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 LOGDATA786780 WHERE PID > 0 ORDER BY PID
Parse Time: 93
Bind Time: 16
Optimize Time: 62
Generate Time: 16
Compile Time: 187
Execute Time: 4750
Begin Compilation Timestamp : 2011-02-24 17:58:27.071
End Compilation Timestamp : 2011-02-24 17:58:27.258
Begin Execution Timestamp : 2011-02-24 17:58:27.258
End Execution Timestamp : 2011-02-24 17:58:32.117
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 69612
Rows returned = 0
Eliminate duplicates = false
In sorted order = false
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
constructor time (milliseconds) = 0
open time (milliseconds) = 4750
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 69612
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 672
close time (milliseconds) = 16
restriction time (milliseconds) = 0
projection time (milliseconds) = 16
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
Source result set:
Table Scan ResultSet for LOGDATA786780 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 69612
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 656
close time (milliseconds) = 16
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 8, 9, 10, 11, 13, 14, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28}
Number of columns fetched=19
Number of pages visited=1608
Number of rows qualified=69612
Number of rows visited=69612
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 9
Operator: <=
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
> 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
[jira] Commented: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-5066:
----------------------------------
It is great you posted the 2 query plans, that is a good first step to getting help. If possible you should post db and actual test program. If the
data is not appropriate to post it is usually not to hard to come up with dummy ddl and dummy data to load into the db so that everyone can
see exactly what is going on.
I think there is something wierd going on here as I am not sure why one plan is being picked over the other. In the 2 plans that
you posted were there 500k rows? It looks like the optimizer is estimating around 69k total rows and the difference in the 2 plans
is that it thinks the >0 is going to return only around 22k rows, but looks like it return all or almost all the rows. This could be an issue with
statistics or index maintenance. Do you see any difference if you run SYSCS_UTIL.SYSCS_COMPRESS_TABLE() and rerun the queries. This
will get rid of any old cruft in the index which helps with histogram estimates for queries, and also will generate new row count estimates and
cardinality statistics if appropriate.
But if the code you posted is really what is being used your
test is flawed as it never looks at the result which is why I think you are seeing orders of magnitude differences. Sometimes
Derby will begin returning rows before the query has finished, I think this happens in all cases except when a sort is required.
So your test is sort of measuring time to first row, rather than time to process the whole query. To be a true measure of time to first row
it should include a fetch of the first row and then end the measurement. When the query includes a sort this will be the
time to process all the rows, while with no sort this will return the first row very quickly. I know the optimizer does not take
time to first row into account for optimization.
Looking at the query plans you posted at least from the server side the difference is total time from begin compilation to end execution is much closer,
with one being around 3 seconds and one being 8 seconds.
Also comparing these 2 queries can easily feed on each other. If run one after another then derby cache will help the second. Even if the server
is cycled the OS cache is very likely to help the second query.
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
Attachment: LogXData.rar
test case with sample java code
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
Additional information with the execution plan... the full table scan happened.
==========================================================
Statement Text:
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 LOGDATA786780 where PID > 0 ORDER BY PID
Parse Time: 94
Bind Time: 31
Optimize Time: 63
Generate Time: 15
Compile Time: 203
Execute Time: 4937
Begin Compilation Timestamp : 2011-02-24 16:14:04.895
End Compilation Timestamp : 2011-02-24 16:14:05.098
Begin Execution Timestamp : 2011-02-24 16:14:05.114
End Execution Timestamp : 2011-02-24 16:14:12.411
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 69612
Rows returned = 0
Eliminate duplicates = false
In sorted order = false
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
constructor time (milliseconds) = 0
open time (milliseconds) = 4937
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 69612
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 842
close time (milliseconds) = 32
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
Source result set:
Table Scan ResultSet for LOGDATA786780 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 69612
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 827
close time (milliseconds) = 32
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 8, 9, 10, 11, 13, 14, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28}
Number of columns fetched=19
Number of pages visited=1608
Number of rows qualified=69612
Number of rows visited=69612
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 9
Operator: <=
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
optimizer estimated row count: 22153.56
optimizer estimated cost: 230567.20
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
I am loadng the complete test case. The numbe of rows is 69612 in the example. In this particular example, it is 16 times slower. My normal testcase has 422709 rows, the performance is 100 times slower.
Need to figure out how to upload the files
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
I don't think if it is a good idea to change my apps to accommodate the database. To me, this is an incorrect execution plan. When evaludation against where PID > 0 reveals that the index shall no be used, the behavior shall be same as the default SQL, where "where PID > 0" is not present. or oder by pid shall be picked up, and the index shall be used.
> 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
[jira] Commented: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12999219#comment-12999219 ]
Mike Matrigali commented on DERBY-5066:
---------------------------------------
there is probably some tuning that could be done on the sort, as bryan mentions. It could be you need to start your jvm with some bigger initial heaps. But as I understand the goal of the app derby is never going to give you what you are looking for if it does a sort rather than
do sort avoidance.
You have 3 choices:
1) assuming PID is a unique key, first issue an order select to get all the PID's of interest and then as you fill each row in the UI do a stored prepared statement to look up the data associated with the PID. I believe if you just select the stuff in the index it will never do a sort.
2) use optimizer hints to force the use of the index in your query.
3) use fetch/offset feature added recently to derby to get "chunks" of data. I don't know much about this feature so am not sure if it will avoid
the sort or not.
> 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
[jira] Commented: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12999384#comment-12999384 ]
George Xu commented on DERBY-5066:
----------------------------------
the database is not big here at all in terms of total size in the sample, and my heap is 512 MB, that is plenty(?)
> 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
[jira] Updated: (DERBY-5066) full table scan when index is used,
taking extremely long time in JDBC
Posted by "George Xu (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
George Xu updated DERBY-5066:
-----------------------------
However the following query used the indexed scan.
Statement Text:
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 LOGDATA786780 ORDER BY PID
Parse Time: 78
Bind Time: 16
Optimize Time: 47
Generate Time: 15
Compile Time: 156
Execute Time: 0
Begin Compilation Timestamp : 2011-02-24 16:16:44.318
End Compilation Timestamp : 2011-02-24 16:16:44.474
Begin Execution Timestamp : 2011-02-24 16:16:44.474
End Execution Timestamp : 2011-02-24 16:16:47.209
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 67132.00
optimizer estimated cost: 335371.11
Source result set:
Index Row to Base Row ResultSet for LOGDATA786780:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0, 8, 9, 10, 11, 13, 14, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 157
optimizer estimated row count: 67132.00
optimizer estimated cost: 335371.11
Index Scan ResultSet for LOGDATA786780 using index NXPID786780 at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 157
scan information:
Bit set of columns fetched={1}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=0
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=3
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 67132.00
optimizer estimated cost: 335371.11
> 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