slow connection from conection cache

[baumgarten]

Hi,

I have a cmp session bean that does direct sql request on the database. I take a connection from the servers connection cache and run a SELECT statement that returns a ResultSet of 200000 primary keys of a table. I cache the results in an int[] array. The hole task takes 11 seconds (1 sec for the SELECT and 10 for iteration the Resultset). If I do the same on a connection created by DriverManager.getConnection it takes only 700 millisec.

I use JBoss 3.2.3 and SQL Server 2000.

Why is iterating the ResultSet so slow on the connection from connection cache?

regards, Sven

[baumgarten]

sorry instead of "conection cache" I mean "conection pool"!

[darranl]

Are both connections using the same transaction settings?

[baumgarten]

I found the difference: pool connections use SelectMethod=cursor. This is much slower but necessary to prevent Exception "Cannot start a cloned connection while in manual transaction mode.".
I use now a second pool with SelectMethod=direct for readonly SQL access.