Follow

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

Connection

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.

Command

The time in seconds to wait for the command to execute. The default is 30 seconds, though we set it as default to various numbers in different places in TX. 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.

Where to change the timeouts

You can change the timeouts in four places.

  1. Data source. Such as SQL. The standard settings is the same in all adapters except excel, text and to some degree Any Source. In Any source connectors there might be a field you can change that contains timeout settings. 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.

    01.PNG

  2. In the DSA/ODX Staging database. Here the default for Command timeout is 1800 seconds, as a lot of data cleansing will be done in here.

    2.PNG

  3. In the DWH databases. It is similar to the Staging database in its standard settings.

    3.PNG

  4. In Qlik Sense Server. This is a command timeout and a quite new feature. Setting it to 0 wont be the same as setting it to unlimited.
    4.PNG

Timeout errors and where to increase the timeouts

The standard settings are fine and most will not get any reasons to change them. Sometimes though you will start getting timeout errors.

Start by locating at what step the timeout occurred.

  1. Is it the first thing that happened when executing a table from a data source? Then it is the connection to the data source that needs to be increased. Place 1. in the list
  2. Is it during the transfer step of a table in the Stage or ODX database? Then you would have to increase the data source command timeout. Place 1. in the list.
  3. Is it is during data cleansing step of a table in the Stage or ODX database? Then you will need to increase the timeout on the stage db. Place 2. in the list.
  4. Is it happening on the DWH during transfer? Then you will need to increase the command timeout on the stage db. Place 2. in the list.
  5. Is it happening on the DWH during data cleansing. Then you will need to increase the command timeout on the DWH db. Place 3. in the list.
  6. Is it happening during the OLAP execution. Then you will need to increase the command timeout on the DWH db. Place 3. in the list.
  7. Is it happening during the Qlik execution. Then you will need to increase the timeout on the DWH db. Place 4. in the list.

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.
      5.PNG
    2. Some of the data source adapters have a batch size option. Decrease it, so you split out the execution in more parts.

      6.PNG

  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. Do you have a join that is not equal. E.G. larger than, or smaller than. Consider doing something similar to this
    5. 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
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.