500pts: Extremely slow ResultSet retrieval using JTDS and Microsoft's JDBC drivers on an MS SQL 2000 database

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!


velocityidpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

girionisCommented:
What happens if you take out the try... catch block?
0
Giant2Commented:
I see you use:
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.CONCUR_READ_ONLY

they could make slow the access to the rows.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
softenggCommented:
Try using forward only resultset. Use "ResultSet.TYPE_FORWARD_ONLY" instead of "ResultSet.TYPE_SCROLL_INSENSITIVE". This shouild be much faster than the previous one.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Venci75Commented:
Have you tried different driver? For exmaple the jdbc/odbc bridge?
0
RuadRauFlessaCommented:
One thing to be aware of is that when you execute a query and receive a resultset you don't have the data on your side yet. All you have is a means of retrieving that data from the SQL server. So now everytime you call the next method on the resultset it actually goes and fetches the next element from the server. This is generally why it would be slow. So your link might verry well have something to do with it. If you have a slow link you might get your resultset relatively quickly since none of the actual data has been transfered at that point. I would suggest you try and buffer the data that you get from the resultset in a way. Sometimes this is not possible due to the sheer volume of data that one retrieves but retrieving a couple of records at a time (say 1000) and working with them before reading anything again would greatly help your cause.
0
velocityidpAuthor Commented:
Giant2 & Softengg:
----------------------
I need to have scrollability, so that's a must. I have tried FORWARD_ONLY, though. I recall getting an error message. (will post later) Using scrolling should not slow down the retrieval of the query THAT much... this is just ridiculously slow.

I could use FORWARD_ONLY and then place everything in a hash or something.... but that sounds cumbersome


Venci75:
---------
My wrapper class is compatible w/ ODBC/JDBC bridge... however, like I said, I need to have scrollability. Only the Type4 drivers (JTDS, MS) have this. I have a hard time believing SCROLL introduces that much delay compared to FORWARD_ONLY. Otherwise, who in their right mind would use scrollability?


RuadRaulFlessa:
------------------
This is what I was thinking about, too. I was thinking about buffering the data as well. I tried setting the fetch size to 100 (or 500) and then did the same loop through the ResultSet. No change. Do I need to be doing something other than change the setFetchSize attribute? (or whatever the name was... don't have code in front of me)

I can see the Access MDB file does buffering, so I know the server can do it. But it doesn't seem to have an effect in my code. Are there any specific settings I need to include -- either on my end or on the SQL 2000 server?

Thanks guys...



0
velocityidpAuthor Commented:
girionis:
---------
No i have not. I think I'd get a compile-time error though... I'll give it a shot once I get in at work. I really doubt that's a problem though...
0
RuadRauFlessaCommented:
What I was referring to was for you to do your own buffering.

Create a list or multi dimensional array or something in which you could hold a couple of thousand entries or something and then load partial sets of data into it. From that list or array you could then compute your stuff.

Something like

while ( result.next() ) {
  //add record to a list or something
  if (list.length > 1000) {
     //loop through the list and do your computing or output
     // remember to clear the list or create a new one after iterating through it.
  }
}
0
RuadRauFlessaCommented:
If you remove the try catch you will receive a compile time error since there is a java.sql.SQLException thrown in that clause on the result.getString("wafer") call
0
velocityidpAuthor Commented:
>>What I was referring to was for you to do your own buffering. Create a list or multi dimensional array or something...

But I would be doing the exact same thing as I am right now, only putting everything into an array instead of printing. I don't think that will make any difference...

Let me make sure I'm hearing you correctly -- you're suggesting something like this?


============
while(result.next()){
     hashtable.put(result.getString("wafer"));
}
============

I would still have the same speed issues in this case. The problem lies at the database interface level.
0
Venci75Commented:
ok - but can you try to execute the query with another driver and let us know the result?
0
RuadRauFlessaCommented:
Venci75's option might work.

0
velocityidpAuthor Commented:
OK... but I've tried this with two very popular drivers... something is wrong with the code I think.
0
RuadRauFlessaCommented:
I think it is more your connection to the database itself.

How fast is it?
0
velocityidpAuthor Commented:
>>I think it is more your connection to the database itself. How fast is it?

I have no specific numbers, but we have an MS Access MDB file linking to it (using linked tables). It is easily able to buffer hundreds of rows on-screen in a matter of milliseconds.

That's through MS Access... this Java interface should be FASTER, not slower! Thus, I know this is not an issue with the "speed" of the database.

Perhaps every time I try to do a "getString" it's re-logging-in and re-accessing the database each time? Perhaps that's what's slowing it down? I think the buffered response (by setting the fetch size) would work... but it doesn't seem to have an effect.

This should not be happening....
0
RuadRauFlessaCommented:
I agree with you on that mate. It should not be happening. But the matter of the fact is that it does happen.
0
Venci75Commented:
That is why I proposed to use another driver. Try to execute and browse the results of your query in the Query Analyzer. If it is slow - then it is something related to the connectivity to the database. Otherwise - execute the same in your program, but using the ODBC driver (which probably is used by the Query Analyzer). If it is slow again - then it is may be in your code. Otherwise - it is a problem of the driver. Please - they this just to know where to look for the problem.
0
velocityidpAuthor Commented:
Remember... the JDBC/ODBC bridge driver only works if the server is LOCAL. So that's not an option. I will try to use FORWARD_ONLY on the JTDS and MS drivers and see if that's fast or not...

But still... are there any tricks to using setFetchSize? That's gotta be a solution...
0
velocityidpAuthor Commented:
Hmm... tried FORWARD and no change. The MS driver returned an error.

I tried not specifying ANYTHING in the createstatement... for both the JTDS and MS drivers... NOW IT IS VERY FAST. By a factor of 100. This is the kind of speed I expected.

However, I've yet to determine if the resultset will be scrollable. This would be ideal.

Looks like Giant2 is right-on. However, I'm going to try some stuff out on Friday... if anyone has any info on the scrollability and setfetchsize issues... please post them.

Will post more info as it comes available.
0
RuadRauFlessaCommented:
Verry interesting indeed. I haven't had the need of a scrollable resultSet yet so didn't really know as to the major speed diffirence you get with it.

Hmmm... Interesting indeed.

*Wonders if this is also the case with Oracle*
0
softenggCommented:
Well from desgin perspective, its not good to keep the resultset open and connection associated with it hanging around till the life of resultset. A better design approach is to use forward only resultset, popuilate the objects and then close the resultset and free the connection. If u think this is combersome, then u can take advantage of O/R mapping tools like Hibernate. And u can use Spring as abstraction over Hibernate. Tools like Hibernate do automatic persistance and loading of data from the db. They also provide pagination and scrolling capabilities whichi is just a matter of setting some properties in the config file.
0
velocityidpAuthor Commented:
>>Well from desgin perspective, its not good to keep the resultset open and connection associated with it hanging around till the life of resultset. A better design approach is to use forward only resultset, popuilate the objects and then close the resultset and free the connection.

Not a problem... I can do that. However, what object type would you say is best for this? I was thinking to use a Vector with a Hashtable or String Array in each element. But that might not be the most efficient... I haven't looked into any specialized objects yet. If anyone has any ideas, please let me know!

Thanks for the other info softengg... will look into this
0
softenggCommented:
well it depends on the what u want to achieve with the final object list. If u want to be able to access the objects via some key e.g. u r populating employee objects and u want to be able to access an employee object from the datastricutre based on its empoloyeeid, then u should use hashtable. Hashtable is normally expensive and should be avoided if poosible. If u want r getting sorted list of objects, then u can use sortedset, or treeset etc. If u dont have any of the above requirments, then u can better be off with arraylist, hashset.
For populating objects i would again suggest to look at these 2 links http://www.springframework.org and www.hibernate.org. Spring framework provides an excellent abstraction over hibernate.
0
Giant2Commented:
Thanks. Happy to help you.
:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.