Java timing out when making connection to SQL server

[mluken]

I have an application that queries a SQL2000 database remotely. It has to make multiple queries though and is timeing out because there are so many connections. What I am wondering is if I am doing this the most efficient way:

private void getResults() {
	SQLConnectionFactory factory = new SQLConnectionFactory(getSQLSettings());
	DatabaseConnection connection = null;
	SelectResult result1 = null;
	SelectResult result2 = null;
	SelectResult result3 = null;
	SelectResult result4 = null;

	try {
		connection = factory.getConnection();
		result1 = getRemoteSelectResult(sqlString1,connection);
		result2 = getRemoteSelectResult(sqlString2,connection);
		result3 = getRemoteSelectResult(sqlString3,connection);
		result4 = getRemoteSelectResult(sqlString4,connection);
	} catch (Exception e) {
		System.out.println(e);
		} finally {
			if(connection != null && connection.isConnected()) {
				connection.disconnect();
			}		
		}
	}
}
private SelectResult getRemoteSelectResult(String sql,DatabaseConnection connection) throws DataException {
	SelectStatement statement = new SelectStatement();
	statement.setConnection(connection);
	statement.setMetaData(new StatementMetaData());
	statement.getMetaData().setSQL(sql);
		
	return execute(statement);	
}

My class SQLConnectionFactory creates the connection string and returns the java.sql.Connection object. Anybody have any thoughts? I am closing the connection after executing all the queries. Should I close it after each connection and create a new object? Why would this be timing out? I am actually making about 10-12 queries - this example just shows 4. Thoughts?

Thanks!!!

 

[sedj]

When you say "remote", how remote - I mean are your client servers & your SQL server in the same building, or are you going over 100s of miles ?

If you think your problem is that you have too many client connections, then I would use a connection pool that queues requests, and makes sure your db is not overloaded. The link to the connection pool below does this

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk