Data loading into QLIK Sense Desktop is very slow

[Joanna_M]

Hi Everybody,

I have a huge problem with very long uploading data to QLIK Sense Desktop. It takes almost an hour to add updated data to dashboard.

What could be the problem? How to solve this?

I'd be grateful for Your help...

[Stoyan_Terziev]

Hi Joanna,

Here is a similar topic I recently encountered:

https://community.qlik.com/t5/QlikView-App-Development/Loading-time/m-p/1646502#M447804

Please let me know if that helps!

Kind regards,

S.T

[Joanna_M]

Thank You Stoyan for reply. It was quite helpful.

One of the probable reasons for slow data loading in my case is huge (and still growing) volume of data.

I have just one question: You wrote "Try to put exit scripts in between steps and see which one runs the slowest. You can identify the problem based on what step takes most time."

Could You please explain how to do this? I'm quite new in QLIK and I don't have too much knowledge about programming and scripts.

[Stoyan_Terziev]

Hi Joanna,

Simplest option:

The 'Exit Script;' command statement terminates the script. While reloading you get this Script Execution Progress which tells you how much time has passed:

You can try it out, running it several times, changing the place where your 'Exit Script;' is and finding exactly where in your script you have a bottleneck.

I hope that helps!

Regards,

S.T.

[lanlizgu]

What process do you have to upload the data?

Which is the source of the data you are uploading (excel, Oracle, SQL...)?

What data transformations are you doing?

How many records do you have in your fact table?

Without knowing the previous points I would recommend you to have 3 layers for the data upload:

1. Extraction of the data as it is and storage in .qvd files
2. Transformation of the data
3. Load of the data and visual layer

[Joanna_M]

"What process do you have to upload the data?":

My data concerns sales (Clients, sales value, margin, quantity, part names and codes, discounts etc). Through Navigation panel I choose Data Manager, then I delete old and create new connection via OLE DB Provider with MS Access database placed on cloud.

"Which is the source of the data you are uploading (excel, Oracle, SQL...)?":

My source is in MS Access database (one main query that collects data from linked tables).

"What data transformations are you doing?":

Main data transformation is done in MS Access query table (each product with it's invoice number, Customer number, margin, sales and purchase value), in QLIK I only collect data in tables or charts (eg.sales per month and per Client).

"How many records do you have in your fact table?":

Over 800k records (sales data from last 3 years). This number is growing every day as new sold products are invoiced.

I don't really understand "Extraction of the data as it is and storage in .qvd files" - could You please clarify this a little bit?

Additionally there is an error with data profiling that appears while data loading. I guess that it's connected with some mistakes in dates, but even if I switch off data profiling - still data is loaded for a veeery long time (over an hour).

[lanlizgu]

Obtaining 800K records from a MS Access table shouldn't take more than seconds depending on the computer. Did you check that the CPU and memory performance of the computer is OK when doing the update of the data?

Are you obtaining the data from a query and not a table, right? If you execute the query how long does it take to open? I understand that the issue is with the query of MS Access not the performance of data loading of Qlik.

Qlik has a strong potential in ETL,  are you considering doing all the data transformation that is being done in MS Access through Qlik? If so, you could check the how long does it take to load in Qlik just the source tables (without querys).

Thank you.

[Joanna_M]

CPU performance while data uploading to QLIK is c.a. 20%. Memory is not so much occupied. So I don't think this is an issue.

Yes, I'm obtaining data from query - and then connect QLIK with this query.

Executing the query in MS Access takes few seconds to get it open. So my main issue is the time that QLIK needs to load the data from query. After creating link with MS Access database it lasts over an hour to upload the data to QLIK. In the meantime error with data profiling occurs (I guess that's due to some mistakes in dates - I'll find it and correct it, but I don't think that's a big issue here).

[lanlizgu]

Could you check creating a table in MS Access directly through the query and obtain the table from Qlik in case it takes less time?

[Joanna_M]

I'm afraid that this won't work in my case. There is too much data to be connected in specific way (sales / timimg / discounts per part and per given period of time) and query does this in much simplier (for me) way than QLIK.

It'll take too much time for me to rewrite all QLIK report 😞