Issue
The way UW connects to a Database to run queries, it uses a single connection for each DB object created. Thus if you have multiple queries that are run against that connection, they are processed in order, one at a time. A very long query can therefore hold up multiple shorter queries, as they must wait in the queue.
Solution
Enable Database Connection Pooling to provide more than one connection.
You should have an existing file that will allow for the configuration and enabling of Database Connection Pooling:
/stoneware/config/DatabaseConnectionPool.xml.sample.
This feature requires making some extra Database Connection objects, but what it will do is allow the loader to randomly use a connection from a small pool, instead of having to queue all queries to a single connection.
For Example, what you would do is create 4 more database connection objects (we'd recommend to have at least 5 total to increase the randomness), so that you have the following:
- CN=dbs-sql,OU=stoneware,dc=com (This is Your current DB connection object.)
- CN=dbs-sql-2,OU=stoneware,dc=com
- CN=dbs-sql-3,OU=stoneware,dc=com
- CN=dbs-sql-4,OU=stoneware,dc=com
- CN=dbs-sql-5,OU=stoneware,dc=com
Then put those in as a pool in the above referenced file.
Rename the file to remove the .sample extension.
Restart Report Services on the loader, via the 8090 Management Console.
Now when a query is run against the default dbs-sql connection, the server will either use that connection or one of the others connections in the pool to run the query.
The following debug options will provide information on the status of the database pools:
- com.stoneware.service.pubic.report.connectionpool
- com.stoneware.service.pubic.report.connectionpoolbuilder