OK, here's my problem. I am trying to query an external MS SQL 2000 server that is on our Intranet. I am able to connect perfectly fine via my applet. I am able to complete a query and get a resultset back relatively quickly (less than a second).

However, when I then go and LOOP through that ResultSet and print out values, things are unnacceptably slow. It prints out about 10 values per second, which is horrible! The total number of rows in the ResultSet is about 1500... so it's not that big. I have tried the JTDS drivers as well as Microsoft's JDBC drivers. Both give the same type of horrible performance.

I know it can't be our network, because we have an Access MDB file that interfaces with the SQL2000 server (using linked tables) and it's fine. 100 times faster. Directly accessing the SQL server via these drivers should be MUCH faster than the Access interface!! Something aint right here...

//"MAIN" LOOP

Singh_jSQL SQLServer = new Singh_jSQL("username", "password","JTDS");
SQLServer.connectDatabase("servername", "databasename");

String query = new String();
query = "SELECT * FROM tblPrbJob";

ResultSet result = SQLServer.queryDB(query);

int i=0;
try{
      while(result.next()){
                System.out.println(result.getString("wafer")+" "+i);
                i++;
      }
}
catch(java.sql.SQLException s){s.printStackTrace();}

System.out.println("DONE");
============================================

That WHILE loop only prints out about 10 values per second. That's just ridiculously slow!

And here's my database wrapper class. It is compatible with multiple drivers: JTDS and MS's driver. The above main loop loads the JTDS driver...

============================================
public class Singh_jSQL {
        private String          server;
     private String           database;
     private String          username;
     private String          password;
        private String          driver;
     private Connection      connection;
     private Statement      statement;
     private ResultSet      result;


     //CONSTRUCTOR
     //--------------------------------------------------------------------------
     public Singh_jSQL(String username, String password, String driver){
             this.password      = password;
             this.username      = username;

            //Load the JDBC/ODBC drivers
            try {
                    if(driver.equalsIgnoreCase("JDBC/ODBC Bridge")){
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                    }
                    else if(driver.equalsIgnoreCase("JTDS")){
                        Class.forName("net.sourceforge.jtds.jdbc.Driver");
                    }
                    else if(driver.equalsIgnoreCase("MS SQL 2000")){
                        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
                    }
                    else if(driver.equalsIgnoreCase("JSQL")){
                        Class.forName("com.jnetdirect.jsql.JSQLDataSource");
                    }

            }
            catch (Exception e) {
                    System.err.println("Failed to load "+driver+" driver.");
                    System.err.println(e.getMessage());
            }
            this.driver = driver;
     }

     //CONNECT TO A DATABASE
     //--------------------------------------------------------------------------
     public void connectDatabase(String server, String database) {
          String url      = new String();
                this.server     = server;
          this.database      = database;
                boolean errorEncountered = false;

                if(this.driver.equalsIgnoreCase("JDBC/ODBC Bridge"))
                    url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+this.database;
                else if(this.driver.equalsIgnoreCase("JTDS"))
                    url = "jdbc:jtds:sqlserver://"+this.server+"/"+this.database+";TDS=7.0;user="+this.username+";password="+this.password;
                else if(this.driver.equalsIgnoreCase("MS SQL 2000"))
                    url = "jdbc:microsoft:sqlserver://"+this.server+";databaseName="+this.database+";selectMethod=cursor;sendStringParametersAsUnicode=false;";
           else if(this.driver.equalsIgnoreCase("JSQL"))
                    url = "jdbc:JSQLConnect://"+this.server+"/database="+this.database+"/user="+this.username+"/password="+this.password;

                System.out.println("Using the '"+this.driver+"' database driver");
                System.out.println("URL="+url);

          try {
                    System.out.println("Getting database connection...");
                    this.connection = DriverManager.getConnection(url, this.username, this.password);
          }
            catch (Exception e) {
                 System.err.println("Problems getting connection to the database");
                 System.err.println(e.getMessage());
                        errorEncountered = true;
          }
          try {
                       if(this.driver.equalsIgnoreCase("JDBC/ODBC Bridge"))
                            this.statement = connection.createStatement();
                        else if(this.driver.equalsIgnoreCase("JTDS"))
                            this.statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                        else if(this.driver.equalsIgnoreCase("MS SQL 2000"))
                            this.statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                       else if(this.driver.equalsIgnoreCase("JSQL"))
                            this.statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

                       this.statement.setFetchSize(1000);

          }
          catch(SQLException s){
               System.err.println("Got a StatementCreate exception!");
                        System.err.println(s.getMessage());
                        errorEncountered = true;
                }

                if(!errorEncountered)
                    System.err.println("Connection established!");

     }

        public void closeConnection(){
            try{
                this.connection.close();
            }
            catch(java.sql.SQLException e){e.printStackTrace();}
        }

     //PERFORM DATABASE QUERY
     //--------------------------------------------------------------------------
     public ResultSet queryDB(String queryText){
          ResultSet rs = null;

                System.out.println(queryText);
          try {
                        //System.out.println("ERROR:"+queryText);
               rs = this.statement.executeQuery(queryText);

                        //rs.setFetchDirection(ResultSet.TYPE_SCROLL_INSENSITIVE);
          }
          catch(SQLException s){
               System.err.println("Got a QueryDB exception! ");
                        System.err.println(s.getMessage());
                        s.printStackTrace();
                }

          return rs;
     }

             //----------------------------------------------------------------------
     public ResultSet getRows(String tableName, String[] returnDataCols, Hashtable rowParams) {
          String selectCols = new String();

          if(rowParams.isEmpty())
               return null;

          if(returnDataCols == null || returnDataCols.length == 0)
                    selectCols = "*";
                else{
                    for(int i=0; i<returnDataCols.length; i++){
                        selectCols += returnDataCols[i];
                        if(i != returnDataCols.length-1)
                            selectCols += ",";
                    }
                }
          //Submit query to mySQL server
          String query = "SELECT "+selectCols+" FROM "+tableName;

          //Now we'll determine which row we want to get. This
          // will be done by using the $rowToGet name-value
          // variable
                if(!rowParams.isEmpty())
                    query += " WHERE ";

          //Cycle through and specify the rows we need to get
          for(Enumeration e = rowParams.keys(); e.hasMoreElements();){
               String currKey = e.nextElement().toString();
               query += currKey+"='"+rowParams.get(currKey).toString()+"'";
               if(e.hasMoreElements())
                    query += " AND ";
          }

          return this.queryDB(query);
     }

        public ResultSet getRows(String tableName, Hashtable rowParams){
            return getRows(tableName, null, rowParams);
        }

}

============================================

Here's the resulting output...

Using the 'JTDS' database driver
URL=jdbc:jtds:sqlserver://servername/databasename;TDS=7.0;user=username;password=password
Getting database connection...
SELECT * FROM tblPrbJob
Connection established!
02 0
02 1
02 2
02 3
... ... (<-- each one of these printlns takes about 1/10 to 2/10 of a second... INCREDIBLY SLOW!)
=====================================

Any ideas?? Something to do with my connection or statement settings? Do you think it could be that I shouldn't be using a ResultSet in tthis fashion? When I do a "getString" from a ResultSet, perhaps it has to go through the username/pass verification each time? Should I be doing something different so all the results of the query are automatically spit out at once? Or is there a better way to speed up the "getString" statements? Why do you think it's taking so dang long?

Thanks for your help!

Nicholas,

One of the reasons why you see this performance drop is certainly the scrollable ResultSet. The way I see it from your code you don't need one (in this case). Scrollable ResultSets mean that a server-side cursor is created and for each row a request is made and a response received from the server (as opposed to forward-only ResultSets which only consist of one request and one server response, containing all rows). In your case, this means 1500 requests/responses instead of one (admittedly, much larger).

Other than that, there might be a lot of other reasons, such as network latency, for example. I'm running the jTDS test suite (which consists of tens of thousands of requests/responses and transfers tens of MB) with the DB running on a different machine, over the network in under 20 seconds. That's thousands of requests per second, as opposed to 10 in your case, so there must be some other problem, too. If you will revert to forward-only ResultSets, latency will not be such a big issue, since you will only make one request (in this case network throughput will be the bottleneck).

Another reason I can think of (but it doesn't apply in your case, from what I can see) is the sendStringParametersAsUnicode parameter. This causes PreparedStatements and CallableStatements to send String parameters either as Unicode or in the database encoding, which can cause pretty serious performance degradation on the server. You can read more about this in the FAQ.

There is one thing that jTDS is not doing (yet) and that is use the fetch size you set for scrollable ResultSets (it does use it for forward-only ResultSets, though, but in that case it doesn't matter much). We are looking into improving jTDS and adding support for this feature, but I can't give you an estimate of when it's going to be done.

Hope this helps,
Alin.

VERY slow ResultSet Iteration w/ Scroll

Forums

Help

VERY slow ResultSet Iteration w/ Scroll

//"MAIN" LOOP

Here's the resulting output...

VERY slow ResultSet Iteration w/ Scroll

Forums

Help

VERY slow ResultSet Iteration w/ Scroll document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

//"MAIN" LOOP

Here's the resulting output...

VERY slow ResultSet Iteration w/ Scroll