Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bigbirdtommy
Starting Member
4 Posts |
 Posted - 2005-02-15 : 13:38:57
|
| Hello all,We have a java application that establishes connections into SQL Server and manages it's own connection pooling. We establish a small number of connections (10) and then use the connection pooling to hand out those connections as needed to the application. The application makes a variety of calls through these connections, most of the form:try {Connection conn = getConnectionFromPool();PreparedStatement statement = conn.prepareStatement("select * from site");ResultSet rs = statement.executeQuery();while (rs.next()) {int i = rs.getInt("thing1");}}catch(Exception e){}finally { freeConnectionInPool(conn); }And this works fine and dandy with 1 exception... When I monitor the number of user connections that this app uses with perf monitor on win2003 server, I get a VERY large number (5000+) of user connections into SQL server as the connections are used and reused. The connection pooling is NOT creating any new connections, those connections are just being used many times, successfully. I have played around with no success in "closing" result sets and PreparedStatements, though it seems to not impact the number of user connections displayed in the slightest. The application runs consistently and the number of connections in the pool remains consistent and happy at the number of connections initialized.But Perf Monitor thinks there are thousands of connections in use.?!??My understanding of what I am doing doesn't match the picture returned by Perf Monitor.... Can anyone shed some light on what is going on with this? Does reusing a connection create additional connections? Am I missing something in how this is supposed to work? Any suggestions theory with connection pooling? Should the connections only be open for a limited lifetime?Thanks for any insights you may have, >>BigbirdtommyTom MyersEinsof Inc. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-15 : 14:18:25
|
| I don't think you want to try to create your own connection pool.I think if you were to open connections when you need them, and close them right after you get your data, that's going to work better for ya.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
bigbirdtommy
Starting Member
4 Posts |
Posted - 2005-02-15 : 14:35:17
|
| Thanks for the suggestion, However, the purpose behind having a connection pool is to reduce overhead associated with reestablishing a new connection everytime a method needs to hit the DB. (And it is already written and works happily with MYSQL currently, and SQLserver -other than this issue) It doesn't fit our application requirements to have a new connection created for every request. The latency and overhead would not be acceptable. My question really is about what is ACTUALLY going on behind the scenes when Connections are reused. Does anyone have any other in-depth insight into this?-Thanks Tom MyersEinsof Inc. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-02-15 : 14:40:23
|
| If I'm not mistaken I think in .NET when you tell the connection to close and you have connection pooling set up it doesn't actually close the database connection but frees it from the pool. Perhaps the same thing happens in Java too.Dustin Michaels |
|
|
|
bigbirdtommy
Starting Member
4 Posts |
Posted - 2005-02-15 : 14:50:07
|
| The "freeConnection" method merely makes the connection available to be used by another query, it never actually "closes()" the physical connection to SQLserver while the connection is in the pool. The connections are opened on init, and remain open while the application is using the pool for the entire time. Is there an issue with leaving a connection open indefinitely in SQL server? This same code has been running happily in MYSQL for >4 years... Inconsistency between the servers?-TomTom MyersEinsof Inc. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-15 : 15:44:25
|
| What are you using to make the connections? ODBC?When in a Microsoft world of ADO / ADO.net it's best to open as late as possible and close as soon as possible. The reason for this is that there's connection pooling going on behind the scene's for you. When you .Close() the connection, it really is putting it back into the pool. It will stay there for a short period of time (30-90 seconds methinks) and then it will actually close the connection if nothing else uses it.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-16 : 14:24:36
|
| We did our end connection pooling in an application some years ago - this was before MS had introduced connection pooling, but even after MS introduced it we found ours was more efficient - primarily because it was tailored to how we used the connections - so I'm with you on the approach!My only suggestion is that there is something in your code which is causing a connection to be created - and presumably NOT closed - worth getting the Fine Toothcomb out and having a look I reckon.I don't know how MYSQL works, but SybaseAnywhere and Oracle used to allow us to push multiple queries through a single connection, whereas SQL would give "Connection is already busy with another query" or somesuch error.So if you do "SELECT * FROM MyBigTable" and only pull a few rows then that connection will still be "busy", and this will prevent another query being pushed through that connection. I don't think this will cause the problem you are seeing, but its potentially a different behaviour from what you have in MYSQL.Kristen |
|
|
|
bigbirdtommy
Starting Member
4 Posts |
Posted - 2005-02-17 : 14:10:51
|
| Hello all, Thanks for the thoughtfull responses to this issue. I ended up finding the following post:http://forum.java.sun.com/thread.jspa?threadID=394002&messageID=2455259on the Sun java forums that pointed at the JDBC connector driver that I was using: the Microsoft JDBC connector.From the post, it seems this connector is a "bad idea" and I replaced it with the connector from the jTDS JDBC driver project at sourceforge: http://jtds.sourceforge.net/This is installed, running happily and NOTICEABLY faster on the production system.This moved my # of connections from 4800+ to 200. Which was fanatastic when I allocated those 200. :)Problem solved. Thanks again!BigbirdtommyTom MyersEinsof Inc. |
|
|
|
|
|
|
|
|
Possible Issue with number of user connections