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: neutral (hermes.apache.org: local policy)
Message-ID: <41DC2771.6090000@covad.net>
Date: Wed, 05 Jan 2005 11:44:17 -0600
From: Victor Cardona <vcardona@covad.net>
User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103)
MIME-Version: 1.0
To: Tomcat Users List <tomcat-user@jakarta.apache.org>
Subject: Re: Speed issues with SQL Server 2000 and JTDS
References: <CA44979F4F707E499D909DAC3464D9AC1140E2@ngsv24.my-netgain.com>
In-Reply-To: <CA44979F4F707E499D909DAC3464D9AC1140E2@ngsv24.my-netgain.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>
>
>--------------------------------------------------------------------- 
>
>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
>
>
>
>---------------------------------------------------------------------
>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
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
>.
>
>  
>
How are your tables set up? Do they use CHAR/VARCHAR or NCHAR/NVARCHAR?  
You might need to change the setting of the 
"sendStringParametersAsUnicode" attribute.  See 
http://jtds.sourceforge.net/faq.html for more details.

Victor


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