Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
To start out with, Qlik doesn't have the data when it reports "43 fields found: ". Here's a debug look into what is happening under the hood on each side with default settings:
The brief version of this is that Qlik will report the fields found in the first batch of information from PostgreSQL then continue to process the stream of data from PostgreSQL.
As background, PostgreSQL's ODBC driver (and Qlik's specific implementation) supports 4 modes:
- Retrieve Entire Result Set in Memory
- Use Multiple Statements
- Single Row Mode
- Use Declare / Fetch
Reference docs: https://docs.posit.co/drivers/1.7.0/pdf/Simba%20PostgreSQL%20ODBC%20Driver%20Install%20and%20Configu...
Ages ago I did some basic testing with these results:
The default behavior of the driver is to use a Use Declare / Fetch mode with a cache or page size of 200 rows of data.
Retrieve Entire Result Set In Memory, as the name implies will queue up a batch of data from the database and deliver it en masse to the Qlik Engine. We can see that the memory usage is relatively high compared to the Single Row and Use Declare / Fetch methods. It seems to be the fastest option to be sure, but only slightly in this relatively simple test.
Use Multiple Statements behaves a bit unexpectedly to my eyes. The memory usage is highest and the reload time is near the worst. It isn’t obvious how this mode is particularly relevant to a BI use case as our Engine will only issue a single SELECT statement so our queries will never come in multiples to take advantage of the theoretical benefits here.
Single Row Mode implies that the entire result set is stored in memory by PostgreSQL and the driver accepts a single row at a time. In terms of performance, it is slower than the fastest mode but not significantly so.
Notes:
- This is a synthetic test where Qlik Sense Desktop is connecting to a local PostgreSQL instance. My guess is that the Use Declare / Fetch (and the default setup) would perform worse in terms of performance when more realistic networking is involved as the batches are rendered on the PostgreSQL side and then seem to be delivered to our ODBC Connector.
- This was a very simple data set so I suspect the memory usage by the connector will show greater differences with more realistic data. I expect the Single Row Mode to be the lowest in terms of resource usage as the ODBC connector only needs to process a single row of data vs. the Use Declare / Fetch method (batches of multiple records) or the Entire Result Set In Memory method which likely would falter at scale in many environments including Qlik SaaS
How to implement: Set the key value pairs in the Advanced section of the connector config. Example setting SingleRowMode
Before delving into the technical, I would recommend assessing what you are trying to achieve. Via the advanced parameters, you have increased the throughput to Qlik, but at a cost of undesired strain on the database.
- How often do you plan to reload this application? A 4 minute reload for an application which will be reloaded during business hours is often acceptable.
- When does the source data get updated? If the underlying source data is updated via a CDC process every hour, then doing a reload sub-hourly serves no purpose.
- What is the variant of source data? Insert only? Insert + update? Insert, update, and delete?
Let’s presume that you want to have as fast a reload as possible simultaneously lowering the stress on the underlying data source as much as possible. The standard approach in Qlik is to use a technique called an incremental load:
The simple description of this is that you will:
- Perform an initial bulk load from the source and store this data to disk as a QVD file
- Subsequent loads will load historical data from this QVD, interrogate the field which is used in a WHERE clause in the SQL load to filter to the latest records.
There are a number of articles across Community on this concept.
