QMC data reload Performance Issue

[#1]

Hi,

I am facing an scenario where database is oracle , tables count is 150 and data size is around 500 GB, Now, I am executing it 4 times is or data refresh cycle is 4 times in day but each time it takes like 15 to 20 min to reload or refresh, Can anyone help me how to Increase the performance , less data refresh time ,

Point here is I am looking for an approach , how to start analyzing , ETL part, Modeling, App creation, So I will have a way to follow to increase performance ...

This is a very common requirement comes by client

[vlad_komarov · Accepted solution]

Are you using a full reload every time? If not, you can organize your script to do an "incremental" reload (loading only rows that were updated after the last reload was completed).

It should be the simplest way to improve your performance....

Regards,

Vladimir

[vlad_komarov]

Adding Binary load should help as well.

VK

[#4]

Hi Vlad,

Thanks for the suggestion, See what I understand by Binary load , It just for access the qvf or qvw file. Can you please help me to understand how do binary load improve performance or data refresh ?

[vlad_komarov]

Hi Rohit,

Binary load is useful when you are loading all your data into main (UI-based) application .

I do not know if you are using 2-3 tier architecture in your application (or using data load from database to your app directly), but this is what I would recommend in your case:

Tier 1: Organize your data extraction to handle incremental load(s):

Data:

  LOAD

     *

  ;

    SQL SELECT *

  FROM "Oracle.Data.Table"

  WHERE period_start_date > '$(MaxDateTime)'

;

Store  Data into _LastLoad.qvd;

Tier 2:

Do necessary transformations

....

Tier 3 (could be combined with or used instead of T2)

Binary [lib://QS Apps/aaaaaa-abbbbbb-cccc-dddddddddd];

// DataFull table will be loaded

DataFull:

concatenate load

*

FROM _LastLoad.qvd;

__________________________________________________________

This approach will allow you to save a lot of time during overall reload process.

In my case (20 M total records, application must be updated every hour) - direct load took ~20-25 mins.

Using incremental and binary loads allowed me to refresh the app within ~2-3 minutes.

Regards,

Vladimir

[#6]

Hi Vlad,

Thanks for your inputs!

One point here is , Why do we need binary load , I agree Incremental process saves lots of time but creating a new application and then by Binary load access the previous application which has incremental code and transformation scripting. My question is how come Binary load saves time while refreshing ?

[vlad_komarov]

Rohit,

The Binary process was designed to use "optimized" load for all data from the "data model" application into your "UI-app".

It's like using QVD load without transformation, but not for individual QVD files, but for the whole data structure.

Qlik will load your data significantly faster in this case.

So the 2-3 tier approach requires additional development, but saves total reload time..

I hope I've answered your question.

Regards,

Vlad

[#8]

Hi Vlad,

Thanks for the optimistic answer. So from now , I will make two applications for a project, First contains incremental and Scripting part and Second will access first one and all the visualization will be done on second application.

Hope you are saying same above.Please correct me if I am wrong

[vlad_komarov]

Rohit,

That's correct.

Vlad

[#10]

Thanks ,

One more thing ,can you please tell me how to do this binary load on Qlik sense server ?