Manage Database Connection Threads

The main factor that determines job execution performance is the number of database connections that are made to the data warehouse. Report and element requests are submitted from Intelligence Server to the data warehouse through a database connection thread. Results of these requests are also returned to Intelligence Server through the database connection thread.

You must determine the number of threads that strikes a good balance between quickly serving each user request while not overloading the system. The overall goal is to prioritize jobs and provide enough threads so that jobs that must be processed immediately are processed immediately, and the remainder of jobs are processed as timely as possible. If your system has hundreds of concurrent users submitting requests, you must determine at what point to limit the number of database connection threads by placing user requests in a queue.

The number of available database connection threads falls in the range depicted as the Optimal use of resources in the illustration below.

To monitor whether the number of database connection threads in your system is effective, use the Database Connection Monitor. For more information about this tool, see Monitoring Database Instance Connections. If all threads are "Busy" a high percentage of the time, consider increasing the number of connection threads as long as your data warehouse can handle the load and as long as Intelligence Server does not become overloaded.

Once you have the number of threads calculated, you can then set job priorities and control how many threads are dedicated to serving jobs meeting certain criteria.

Limiting and Prioritizing the Number of Database Connections

To set the number of database connection threads allowed at a time, modify the database instance used to connect to the data warehouse. Use the Job Prioritization tab in the Database Instance Editor and specify the number of high, medium, and low connections. The sum of these numbers is the total number of concurrent connection threads allowed between Intelligence Server and the data warehouse. These settings apply to all projects that use the selected database instance.

You should have at least one low-priority connection available, because low priority is the default job priority, and low-priority jobs can use only low-priority database connection threads. Medium-priority connection threads are reserved for medium- and high-priority jobs, and high-priority connection threads are reserved for high-priority jobs only. For more information about job priority, including instructions on how to set job priority, see Prioritize Jobs.

If you set all connections to zero, jobs are not submitted to the data warehouse. This may be a useful way for you to test whether scheduled reports are processed by Intelligence Server properly. Jobs wait in the queue and are not submitted to the data warehouse until you increase the connection number, at which point they are then submitted to the data warehouse. Once the testing is over, you can delete those jobs so they are never submitted to the data warehouse.

Optimizing Database Connection Threads Using ODBC Settings

In addition to limiting the number of database connection threads created between Intelligence Server and the data warehouse, it is a good practice to efficiently use those connection threads once they are established. You want to ensure that the threads are being used and are not tied up by processes that are running too long. To optimize how those threads are used, you can limit the length of time they can be used by certain jobs. These limits are described below.

Maximum Cancel Attempt Time

When a user runs a report that executes for a long time on the data warehouse, the user can cancel the job execution. This may be due to an error in the report's design, especially if it is in a project in a development environment, or the user may simply not want to wait any longer. If the cancel is not successful after 30 seconds, Intelligence Server deletes that job's database connection thread. The Maximum cancel attempt time (sec) field controls how long you want Intelligence Server to wait in addition to the 30 seconds before deleting the thread.

Maximum Query Execution Time

This is the maximum amount of time that a single pass of SQL can execute on the data warehouse. When the SQL statement or fetch operation begins, a timer starts counting. If the Maximum query execution time (sec) limit is reached before the SQL operation is concluded, Intelligence Server cancels the operation.

This setting is very similar to the SQL time out (per pass) VLDB setting (see Limit a Report's SQL Per Pass). That VLDB setting overrides the Maximum query execution time (sec) setting. This setting is made on the database connection and can be used to govern the maximum query execution time across all projects that use that connection. The VLDB setting can override this setting for a specific report.

Maximum Connection Attempt Time

This is the maximum amount of time that Intelligence Server waits while attempting to connect to the data warehouse. When the connection is initiated, a timer starts counting. If the Maximum connection attempt time (sec) limit is reached before the connection is successful, the connection is canceled and an error message is displayed.

Limiting Database Connection Caches

Establishing a database connection thread is expensive in terms of time and resources. Because of this, Intelligence Server caches the threads so that every SQL pass and job execution it performs does not need to create a new connection. Rather, those processes use an existing cached thread. However, the RDBMS may, after a certain time limit, delete the connection threads without notifying Intelligence Server. If this happens and an Intelligence Server job tries to use a cached connection thread, the user sees an error message. To avoid this, you can limit the length of time that a database connection cache can exist. You can limit the maximum lifetime of a database connection (see Connection Lifetime), and you can limit the amount of time an inactive database connection remains open (see Connection Idle Timeout).

To set these limits, edit the database instance, then modify the database connection (at the bottom of the Database Instances dialog box), and on the Database Connections dialog box, select the Advanced tab. For these settings, a value of -1 indicates no limit, and a value of 0 indicates that the connection is not cached and is deleted immediately when execution is complete.

Connection Lifetime

The Connection lifetime (sec) limit is the maximum amount of time that a database connection thread remains cached. The Connection lifetime should be shorter than the data warehouse RDBMS connection time limit. Otherwise the RDBMS may delete the connection in the middle of a job.

When the Connection lifetime is reached, one of the following occurs:

If the database connection has a status of Cached (it is idle, but available) when the limit is reached, the connection is deleted.
If the database connection has a status of Busy (it is executing a job) when the limit is reached, the connection is deleted as soon as the job completes. The database connection does not go into a Cached state.

Connection Idle Timeout

The Connection idle timeout (sec) limit is the amount of time that an inactive connection thread remains cached in Intelligence Server until it is terminated. When a database connection finishes a job and no job is waiting to use it, the connection becomes cached. If the connection remains cached for longer than this timeout limit, the database connection thread is then deleted. This prevents connections from tying up data warehouse and Intelligence Server resources if they are not needed.