Data load takes a lot of time

[RikEr]

Hey all,

Maybe the question I have has already been asked and answered and in that case let me know including a link to that subject. Otherwise I hope that some will be able to explain and help me with something.

We use Qlik Sense Enterprise on Windows and load our data with a Loadscript. The data has it's base in postgres. So that means that every night we load the data from postgres to qlik. And within the qlik environment we do some minor transformations to bring that data into QVD's for an App. So far so good I think.

The problem arises when we need to load a couple of million (20-50) rows from postgres. Within postgres it takes a couple of minutes to retrieve that data. But with our loadscripts it can take up to a couple of hours to get that data in.
We already have indices on the tables in postgres to make sure that the selection we do on the data will be fast. But that only wins us some time. The connection we have is defined as:

CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=postgres;host=[My Server];port=[My Port];db=datawarehouse;SSLMode=prefer;UseSystemTrustStore=false;ByteaAsLongVarBinary=0;TextAsLongVarchar=0;UseUnicode=1;FetchTSWTZasTimestamp=1;MaxVarcharSize=262144;UseDeclareFetch=1;Fetch=200;EnableTableTypes=1;MoneyAsDecimal=1;allowNonSelectQueries=false;QueryTimeout=30;"

Is there someone that can point me in the right direction as to where the delay might occur? Thanks in advance.

[igoralcantara · Accepted solution]

The delay could be caused by various factors within the data loading process. Here are some potential areas to investigate:

1. Network Latency: The time it takes to transfer data over the network between your PostgreSQL database and Qlik Sense server could contribute to the delay. Check the network latency and bandwidth between the two systems to ensure optimal performance. How physically far are the two servers?

2. Data Volume and Complexity: Loading millions of rows of data, even with optimizations in place, can still be time-consuming. Consider the complexity of the data being loaded and whether there are opportunities to optimize the load script further, such as by filtering unnecessary data or restructuring the load process.

3. Qlik Sense Load Script Optimization: Review your Qlik Sense load script to identify any inefficient operations or bottlenecks. Make sure that you're using optimized Qlik scripting techniques, such as using resident loads, incremental loads, or applying appropriate data model optimizations.

4. Data Transformation: You mentioned performing minor transformations within the Qlik environment. Evaluate whether these transformations are causing additional processing overhead and if there are opportunities to streamline or optimize them. I highly recomend to load the data from the database with no transformations, land it as a Layer 1 file (QVD or Parquet) and apply the transformations from Layer 1 as save as Layer 2. That improves DB load performance A LOT.

5. Qlik ODBC Connector Performance: Consider the performance characteristics of the Qlik ODBC connector you're using to connect to PostgreSQL. Investigate whether there are any known performance issues with the connector version you're using and whether updating to a newer version or exploring alternative connectors could improve performance. Also, ODBC are much slower than OLEDB.

6. Server Resources: Ensure that the Qlik Sense server has sufficient resources (CPU, memory, disk I/O) to handle the data loading process efficiently. Monitor server performance metrics during data loading to identify any resource constraints or bottlenecks.

Please, check those items. I am sure some of those will help you.

[marcus_sommer]

I don't know if this driver and/or the set configurations are the bottleneck but I think investigating it within a postgres-community would be more helpful.

Beside this might be also your network delaying the data. Tracking this is probably not easy - maybe you could bring both systems nearer to each other, for example installing a data-base dump on the Sense machine or the reverse approach. If postgres is capable to store the query-output as csv you may try this and then copy & paste it to Sense and/or loading from the csv. I think you should get valuable insights.

[RikEr]

Thank you for your response. And thanks for the different areas I need to look at to hopefully speed up the data load.

I have the felling that somewhere within one of those issues.