Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Message-Id: <s1d90aeb.001@ngw.bvu.edu>
Date: Mon, 03 Jan 2005 09:05:14 -0600
From: "David Boyer" <david@bvu.edu>
To: <tomcat-user@jakarta.apache.org>,<charlesk@netgaintechnology.com>
Subject: RE: Speed issues with SQL Server 2000 and JTDS
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part95B5003A.0__="

--=__Part95B5003A.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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 
 
 
--------------------------------------------------------------------- 
 
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org 
 
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org 
 
 
--------------------------------------------------------------------- 
 
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org 
 
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org 
 

--=__Part95B5003A.0__=--