Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense performance

Hi

We intend to use Qlik Sense for exposing analyticcapabilities to a Self Service application that contains data for millions of customers and allowing them to analyze there data for the last 12-18 months.

I was thinking to use "Incremental Loading" mechanism to load into Qlik Sense data for the last day incrementally every night (this includes also removing old data from Qlik Sense).

However I see that "Incremental Load" implies reading and then re-writing QVD files. Taking into account the big amount of data (around 500 Gb) this can be quite time consuming.

I see that in the documentation it is written that loading data from QVD is 10 - 100 times faster that from regular DB, but I didn't see any actual numbers.

Can anyone say what can be the actual read/write speed from/to QVD files in bytes/second?

Thank you

3 Replies
Employee
Employee

Re: Qlik Sense performance

Those are true performance numbers but they are not guaranteed. What will help is to ensure the QVD read is an OPTIMIZED read and not a STANDARD read.  What causes the read to be standard depends entirely on the transformations that you are doing (adding a WHERE clause). There is a bit here in the help and there is an excellent collection of topics in the next thread from Marcus sommer. the principles pertain to qlikview and sense but the syntax is slightly different in sense

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/work-with-QVD-files... qvd

Advanced topics for creating a qlik datamodel

Putting the calculations in the SQL clauses will help remove qlik calculations and logic that would prevent optimized reloads so that everything is 'baked into' the QVD when you are ready to rapidly read it during the incremental refresh.

Its true that the initial seeding of the large QVDs will take time but the incremental load and QVD load SHOULD be very quick .

Not applicable

Re: Qlik Sense performance

Let me make my question a bit more clear...

Here is an example of incremental load from help.qlik.com:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;

STORE QV_Table INTO File.QVD;

In this example:

1. The "Concatenate LOAD PrimaryKey, X, Y FROM File.QVD" statement will load the entire "File.QVD";

2. The "STORE QV_Table INTO File.QVD" statement will re-write the entire "File.QVD" file.

If this file is huge, then it will take time to first load and then write it.

The question is:

How long such an incremental load will take for a 500Gb "File.QVD" whlie the incremental data is 10Gb?

Employee
Employee

Re: Qlik Sense performance

hmm...  not sure I can provide a great estimate based on GB .

I've heard of 150,000 rows / minute is common for regular sql queries and an optimized qvd load will be substantially faster.  does that help  ?

Community Browser