Solved

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

Posted on 2004-09-13
24
2,697 Views
Last Modified: 2012-06-27
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!


0
Comment
Question by:velocityidp
  • 8
  • 7
  • 3
  • +3
24 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 12051692
What happens if you take out the try... catch block?
0
 
LVL 12

Accepted Solution

by:
Giant2 earned 500 total points
ID: 12051739
I see you use:
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.CONCUR_READ_ONLY

they could make slow the access to the rows.
0
 
LVL 1

Expert Comment

by:softengg
ID: 12051950
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
 
LVL 9

Expert Comment

by:Venci75
ID: 12052090
Have you tried different driver? For exmaple the jdbc/odbc bridge?
0
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12052224
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
 

Author Comment

by:velocityidp
ID: 12053232
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
 

Author Comment

by:velocityidp
ID: 12053240
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
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12053293
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
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12053312
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
 

Author Comment

by:velocityidp
ID: 12053359
>>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
 
LVL 9

Expert Comment

by:Venci75
ID: 12053374
ok - but can you try to execute the query with another driver and let us know the result?
0
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12053415
Venci75's option might work.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:velocityidp
ID: 12053594
OK... but I've tried this with two very popular drivers... something is wrong with the code I think.
0
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12053979
I think it is more your connection to the database itself.

How fast is it?
0
 

Author Comment

by:velocityidp
ID: 12054493
>>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
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12054591
I agree with you on that mate. It should not be happening. But the matter of the fact is that it does happen.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 12054826
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
 

Author Comment

by:velocityidp
ID: 12059927
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
 

Author Comment

by:velocityidp
ID: 12060029
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
 
LVL 3

Expert Comment

by:RuadRauFlessa
ID: 12061550
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
 
LVL 1

Expert Comment

by:softengg
ID: 12062090
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
 

Author Comment

by:velocityidp
ID: 12064042
>>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
 
LVL 1

Expert Comment

by:softengg
ID: 12092019
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
 
LVL 12

Expert Comment

by:Giant2
ID: 12157982
Thanks. Happy to help you.
:)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now