Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-5066

full table scan when index is used, taking extremely long time in JDBC

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: None
    • Component/s: Eclipse Plug-in, JDBC
    • Environment:
      Windows XP environment
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance

      Description

      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(); }


      }
      }

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              georgex George Xu

              Dates

              • Created:
                Updated:

                Issue deployment