Timeouts on Databases
This article explains what timeout types there are, and how you can change timeout settings to prevent timeout errors. By adjusting the settings, you can make sure that there is a good balance between how long the application needs to wait for actions to complete before triggering an error.
Use the links below to jump to the corresponding section:
The two types of timeout
Connection
Connection timeout is the time in seconds to wait for a connection to open. The default value is 15 seconds. If you have a slow connection, you might need to increase this value. This is mainly applicable to external data sources, i.e. if a server is on the other side of the world, the timeout will most likely need to be increased.
Command
Command timeout is the time in seconds to wait for a command to execute. It is the amount of time we wait between one command and the next. It could, for example, be a part of a data cleansing procedure.
If you have high data loads that need to run through multiple changes, you might need to increase this value. When using a one-to-one copy, you won't need to, but when using lookup fields that use a Top, or similar, it might be necessary.
Timeout errors and where to change the timeouts
You can change the timeouts in multiple places. Where you change the timeout settings depends on where the error originates from.
Compare your situation with the options described below and adapt your timeout settings accordingly.
| Situation | Solution |
| The timeout error is the first thing that occurs when executing a table from a data source. |
Increase the connection timeout to the data source. The standard settings are the same in all adapters, except Excel and text. The defaults are Connection 15 and Command 100. It only waits 100 seconds because it only does the transfer from your data source to your staging database, and it isn't doing any of the data cleansing. |
| The timeout error occurs during the transfer step of a table in the Stage or SQL database. | |
| The timeout error occurs during the data cleansing step of a table in the Staging database. |
Increase the command timeout for the Staging Database. Here, the default for Command timeout is 1800 seconds, as a lot of data cleansing will be done in this step. |
| The timeout error occurs on the DWH during transfer. | |
| The timeout error occurs on the DWH during data cleansing. |
Increase the command timeout for the Data Warehouse database. Here, the default for Command timeout is 1800 seconds, as a lot of data cleansing will be done in this step. |
| The timeout error occurs during the OLAP execution. |
What to do about timeouts
Increasing the amount of time you wait can work for some issues, but it might not solve all issues. Usually, you'd like to figure out how to make the execution faster. This is a larger discussion, and you can find some common solutions below.
First, you should figure out why the timeout error happens.
| Situation | Solution |
| Timeout on the data source during the nightly execution. |
This could indicate a loss of connection to the data source server. It might have a restart service job at night, or something similar. The following options might help:
|
| Timeout happens during the transfer step, on a specific table. | Investigate how the table is set up and how much data it contains. Set up incremental load on this table. |
| Timeout happens during the data cleansing step of a table. |
Investigate how many lookup fields are present in the table, and to how many tables it relates.
|