Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Timeouts on databases

There are a variety of places where you can set up timeouts. They will work differently based on where you change it. Below I will explain the different types and where they are set up and used.

Also it should be noted that if you add 0 it means that it will wait a infinite amount of time. 

The two types of timeout


The time in seconds, to wait for a connection to open. The default value is 15 seconds. It should be increased the slower the connection is. This is mainly relevant on external data sources. They could be placed on the other side of the globe physically, so an increase might be in order.


The time in seconds, to wait for the command to execute. We set it as default to various numbers in different places in the Jet Data Manager. This is the amount of time we wait from one command to the next, it could be a part of a data cleansing procedure. This is something you increase if you have high data loads that needs to run through multiple changes. So it wont be necessary when using a one to one copy, but when using lookup fields that uses a Top, or similar.

Timeout errors and where to change the timeouts

You can change the timeouts in four places.  Where you change the timeout settings will depend on where the error originates from.

  1. Is it the first thing that happened when executing a table from a data source? Is it during the transfer step of a table in the Stage or SQL database? Then it is the connection to the data source that needs to be increased. The standard settings are the same in all adapters except excel, and text. The defaults are Connection 15 and Command 100. The reason it is waiting 100 and not higher, is because it only does the transfer from one server to the other and isn't doing any of the data cleansing.


  2. Is it is during data cleansing step of a table in the Staging database?  Is it happening on the DWH during transfer? Then you will need to increase the command timeout on the stage db. Here the default for Command timeout is 1800 seconds, as a lot of data cleansing will be done in here.


  3. Is it happening on the DWH during data cleansing? Is it happening during the OLAP execution?  Then you will need to increase the command timeout on the DWH db. It is similar to the Staging database in its standard settings.


What to do about the timeouts

Increasing the amount of time you wait can work for some issues, but it is not something that solves all issues. Mostly it is figuring out how to make the execution faster. This is a big thing, but below is some of the most common solutions.

First you should figure out why it happens.

  1. Does it happen during the nightly execution with a timeout on the data source? Then it might be a loss of connection to the data source server. Maybe it has a restart service job at night, or something similar.
    1. One thing is that you can choose what to do when it fails.
    2. Some of the data source adapters have a batch size option. Decrease it, so you split out the execution in more parts.


  2. Does it happen during the transfer step and on what table? How is that table set up and how much data is it containing.
    1. Set up incremental load on this table.
    2. If automatic index generation turned off, then turn it on.
  3. Does it happen during the data cleansing step of any table? What is happening during this, like how many lookup fields are there and how many tables does it relate to?
    1. If automatic index generation turned off, then turn it on.
    2. Set up incremental load on that table.
    3. Is the lookup type Partition by, or Top? Then change it if possible to Group by.
    4. If you can change the lookup fields, so as many as possible is coming from the same table, there will be made a overall group by containing all these fields.
Was this article helpful?
0 out of 0 found this helpful