[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Oracle Datasource Slow (Max CPU) while JDBC super-fast

[JustinCRL]

If I create a direct connection to Oracle using JDBC I can execute a simple query that pulls back about 1,400 records in 100-200 ms.   If I used a JBOSS defined data source, the query takes 23 SECONDS!

I'm running:
JBoss 4.02
Oracle 10gR2 (10.2.0.2.0) on Linux
JDK 1.5
WinXP


If I execute the following code, it is very fast.

Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@linoradev1.crlcorp.com:1521:CRLDEV", "stsetup", "stsetup");

Statement smt = conn.createStatement();
ResultSet rs = smt.executeQuery("SELECT STUDY.STUDY_PKID AS ID, STUDY.STUDY_CODE AS CODE, STUDY.STUDY_NAME AS STUDY_NAME, SPONSOR.SPONSOR_NAME AS SPONSOR_NAME FROM STUDY, SPONSOR where STUDY.SPONSOR_PKID = SPONSOR.SPONSOR_PKID");

while (rs.next()) {
	int id = rs.getInt("ID");
	String code = rs.getString("CODE");
	String studyName = rs.getString("STUDY_NAME");
	String sponsorName = rs.getString("SPONSOR_NAME");
	StudySummary study = new StudySummary();
	study.setID(id);
	study.setStudyCode(code);
	study.setStudyName(studyName);
	study.setSponsorName(sponsorName);
	studies.add(study);
}

If I replace the first two statements with:

DataSource dataSource = (DataSource) new InitialContext().lookup("java:/crlcorp/tls/accessioning");
conn = dataSource.getConnection();

Then it takes 23 seconds.

My -ds.xml file contains:

  <local-tx-datasource>
	<jndi-name>crlcorp/tls/accessioning</jndi-name>
	<connection-url>jdbc:oracle:thin:@linoradev1.crlcorp.com:1521:CRLDEV</connection-url> 
			<driver-class>oracle.jdbc.driver.OracleDriver
  </driver-class> 
   </local-tx-datasource>



I'm using Classes12.zip for the driver.

>From logging I can tell that the query executes quickly.  The slow down occurs when reading the result set.  I have also noticed that my CPU is maxed when I'm doing this.   This problem has been replicated on a separate desktop.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4050606#4050606

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4050606