tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wade Chandler <wchand...@redesetgrow.com>
Subject Re: Speed issues with SQL Server 2000 and JTDS
Date Wed, 05 Jan 2005 20:28:44 GMT
Charles P. Killmer wrote:
> Thanks.  I had already done this but maybe not communicated them as
> concisely.  
> 
> Thanks 
> Charles
> 
> -----Original Message-----
> From: Parsons Technical Services [mailto:parsonstechnical@earthlink.net]
> 
> Sent: Tuesday, January 04, 2005 6:23 PM
> To: Tomcat Users List
> Subject: Re: Speed issues with SQL Server 2000 and JTDS
> 
> Trying a few test should help you narrow things down a bit.
> 
> 1. Run without DB connection. (Done  runs fast)
> 
> 2. Run with a DB connect but no query. (Done runs slow)
> 
> 3. Run with a simple query and do nothing with it.
> 
> 4. Run with a simple query and post results in page. Only move forward
> through result set.
> 
> 5. Run with a simple query and post results in page. Move around in the
> result set (Only if you do this in your page).
> 
> At some point in these test you should see a dramatic jump in the
> response time. If it is a steady climb, then you may have multiple
> issues.
> 
> Report back what you find and we'll make suggestions from there.
> 
> Doug
> 
> 
> ----- Original Message -----
> From: "Charles P. Killmer" <charlesk@netgaintechnology.com>
> To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
> Sent: Tuesday, January 04, 2005 11:23 AM
> Subject: RE: Speed issues with SQL Server 2000 and JTDS
> 
> 
> Could this speed issue be caused by a poor setup?  When I remove the
> database connection from my code, the pages run fast.  Though I
> obviously need the database portion of the code in there.
> 
> Thanks
> Charles
> 
> 
> -----Original Message-----
> From: David Boyer [mailto:david@bvu.edu]
> Sent: Monday, January 03, 2005 9:05 AM
> To: tomcat-user@jakarta.apache.org; Charles P. Killmer
> Subject: RE: Speed issues with SQL Server 2000 and JTDS
> 
> You could try using something like jProfiler to see where the bottleneck
> is.
> 
> I don't see anything unusual in your code example, although it looks
> like the only thing it does is create the connection. I use jTDS and it
> works fine without doing anything exceptional.
> 
> 
> 
> 
>>>>charlesk@netgaintechnology.com 01/03 8:55 am >>>
> 
> 
> This is some representative code that is being very slow.
> 
> 
> import java.sql.*;
> 
> 
> public class SomeClass {
> 
>    public Connection conn;
> 
> public int ID;
> 
> public String Name;
> 
> public String Address;
> 
> public String City;
> 
> public String OtherStuff;
> 
> 
>    public SomeClass() throws Exception {
> 
>        try {
> 
>            Class.forName(net.sourceforge.jtds.jdbc.Driver);
> 
>        } catch (ClassNotFoundException ex) {
> 
>        }
> 
>        try {
> 
>            conn =
> 
> DriverManager.getConnection(jdbc:jtds:sqlserver://111.222.333.444:1433/
> 
> someDB;user=someuser;password=somepassword);
> 
>        } catch (Exception e) {
> 
>            throw e;
> 
>        }
> 
>    }
> 
> 
>   public int Audit() throws Exception {
> 
>      return 5;
> 
>   }
> 
> 
>   public ResultSet GetData() throws Exception {
> 
>      ResultSet rs = null;
> 
>      return rs;
> 
>   }
> 
> 
>   public int DeleteSomething() throws Exception {
> 
>      return 2;
> 
>   }
> 
> }
> 
> 
> I don't have anything special in any XML files.  I will try to make my
> 
> code work like yours is.  But if someone has an idea why the way I have
> 
> it written is slow, I would love to hear it.
> 
> 
> Thank You
> 
> Charles
> 
> 
> -----Original Message----- 
> 
> From: Randall Svancara [mailto:rsvancara@adaweb.net]
> 
> Sent: Monday, January 03, 2005 8:20 AM
> 
> To: Tomcat Users List
> 
> Subject: RE: Speed issues with SQL Server 2000 and JTDS
> 
> 
> I have been using JTDS with SQL Server 2000 in conjunction with Tomcat
> 
> without any problems.  Perhaps if you post some your database connection
> 
> 
> code, someone could provide you with assistance.  You might also try
> 
> posting to the JTDS Mailing list.  Are you using Database Connection
> 
> Pooling (DBCP)??
> 
> 
> I am including an example the code I use to access a stored procedure on
> 
> 
> SQL Server 2000 using DBCP.
> 
> 
> /*  Here are the things I import */
> 
> import java.sql.Connection;
> 
> import java.sql.Statement;
> 
> import java.sql.ResultSet;
> 
> import java.sql.SQLException;
> 
> import javax.naming.*;
> 
> import javax.sql.*;
> 
> 
> 
> Public class SomeClass{
> 
> 
> 
> * A public class that returns an Applicant object
> 
> * @return the applicant as applicant
> 
> */
> 
> public Applicant SomeApplicantMethod(){
> 
>        Applicant app = new Applicant();
> 
>        Connection conn = null;
> 
>        Statement stmt = null;
> 
>        ResultSet rst = null;
> 
> 
> 
>        try{
> 
>            Context ctx = new InitialContext();     /* Declare initial
> 
> context */
> 
>            if(ctx == null ){
> 
>                logger.error(Error creating new context for some
> 
> reason);
> 
>                throw new Exception(No context);
> 
>            }
> 
>            /* Throw an exception if it is null */
> 
>            DataSource ds =
> 
> (DataSource)ctx.lookup(java:comp/env/jdbc/summitexec);
> 
> 
> 
>            conn = ds.getConnection();
> 
> 
> 
>            if(conn != null)  {
> 
> 
> 
>                stmt = conn.createStatement();
> 
> 
> 
>                rst = stmt.executeQuery(sp_SelectApplicant +
> 
> canidateid);
> 
> 
> 
>                while(rst.next()){
> 
> 
> // Add result set to applicant object,
> 
> NOT SHOWN HERE!!!
> 
> 
>                }
> 
> 
> 
> 
>    //Make sure you close everything, else you end up
> 
> with object leaks....
> 
>                if(stmt != null){
> 
>                    stmt.close();
> 
>                }
> 
> 
> 
>                if(rst != null){
> 
>                    rst.close();
> 
>                }
> 
> 
> 
>                if(conn != null){
> 
>                    conn.close();
> 
>                }
> 
>            }
> 
>        }catch(Exception E){
> 
>            logger.error(EXCEPTION ERROR Getting Applicant: +
> 
> E.toString());
> 
>        } finally{
> 
> 
> 
>            // Close out of any open connections if they exist, this is
> 
> important
> 
>            // in order to release resources for connection pooling
> 
>            try{
> 
>                if(stmt != null){
> 
>                    stmt.close();
> 
>                    stmt=null;
> 
>                }
> 
>            }catch(SQLException E){}
> 
> 
> 
>            try{
> 
>                if(rst != null) {
> 
>                    rst.close();
> 
>                    rst = null;
> 
>                }
> 
>            }catch(SQLException E){}
> 
> 
> 
>            try{
> 
>                if(conn != null) {
> 
>                    conn.close();
> 
>                    conn = null;
> 
>                }
> 
>            }catch(SQLException E){}
> 
>        }
> 
> 
> }
> 
> }
> 
> 
> 
> This is the quirky part about Tomcat, in version 5.0 or ealier, I have
> 
> to use this xml code in my webapp context file for DBCP.
> 
> 
> <Resource name=jdbc/summitexec auth=Container
> 
> type=javax.sql.DataSource />
> 
> <ResourceParams name=jdbc/summitexec>
> 
>    <parameter>
> 
>        <name>factory</name>
> 
> 
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> 
>    </parameter>
> 
> <parameter>
> 
> <name>url</name>
> 
> 
> <value>jdbc:jtds:sqlserver://192.168.0.2:1433/summitexec;User=someuser;P
> 
> 
> assword=somepassword</value>
> 
> </parameter>
> 
> <parameter>
> 
> <name>driverClassName</name>
> 
> <value>net.sourceforge.jtds.jdbc.Driver</value>
> 
> </parameter>
> 
>   <parameter>
> 
> <name>user</name>
> 
> <value>someuser</value>
> 
> </parameter>
> 
> <parameter>
> 
> <name>password</name>
> 
> <value>somepassword</value>
> 
> </parameter>
> 
> </ResourceParams>
> 
> 
> In 5.5 I have to use this xml for DBCP.  If someone could provide
> 
> details why this is, I would appreciate it.
> 
> 
>        <Resource name=jdbc/summitexec
> 
>                  auth=Container
> 
>                  type=javax.sql.DataSource
> 
>                  driverClassName=net.sourceforge.jtds.jdbc.Driver
> 
> 
> url=jdbc:jtds:sqlserver://192.168.0.3:1433/summitexec
> 
>                  username=someuser
> 
>                  password=somepassword
> 
>                  maxActive=20
> 
>                  maxIdle=10
> 
>                  maxWait=10/>
> 
> 
> Thanks,
> 
> 
> Randall
> 
> 
> -----Original Message----- 
> 
> From: Charles P. Killmer [mailto:charlesk@netgaintechnology.com]
> 
> Sent: Sunday, January 02, 2005 2:13 PM
> 
> To: Tomcat Users List
> 
> Subject: Speed issues with SQL Server 2000 and JTDS
> 
> 
> I bought the Core Servlets and Java Server Pages and read it over the
> 
> weekend.  Happy New Year to me.  I did get out to a few parties though.
> 
> ;)  I am having trouble getting JTDS to return results quickly.
> 
> 
> Has anyone got any example code for how to properly query a SQL Server
> 
> 2000 database?  The code I write needs to work with both SQL Server 2000
> 
> 
> and SQL Server 7.  In creating the connection, I am specifying
> 
> TYPE_SCROLL_INSENSITIVE and TYPE_CONCUR_READ_ONLY.  I tried not
> 
> specifying anything and got errors about not being able to scroll the
> 
> results.  Is the only solution to this, use FORWARD_ONLY and buffer the
> 
> contents myself?  I hoping there is a better way.
> 
> 
> Thank you
> 
> Charles Killmer
> 

Have you profiled/analyzed the queries you are using in query analyzer? 
    Is the query running fast and the looping over the results the slow 
section of the code?  How many rows are you getting back?  How long does 
it take to loop over say 100 records vs 1000 records?  Do the queries 
return fast in query analyzer?  Does this "slowness" occur outside of 
tomcat?  If the slowness occurs outside of tomcat I would write to the 
developers or help list on the jtds project home page.

You should be able to even debug the jtds source code if need be.  It 
wouldn't be hard.  I suggest writing a simple app to run a query that is 
taking a long time and figure out exactly where the slow point is first. 
  Then determine if it is only happening in tomcat or if it happens 
every where else and if it happens when you only create the connection 
object directly without using a lookup service or with a lookup service. 
  Break it down to know exactly what to ask.

Wade


---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message