topic Re: Data loading into QLIK Sense Desktop is very slow in Connectivity & Data Prep

Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 23 Dec 2021 19:50:22 GMT

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...

Re: Data loading into QLIK Sense Desktop is very slow

Stoyan_Terziev — Thu, 28 Nov 2019 10:04:14 GMT

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

Re: Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 28 Nov 2019 10:15:05 GMT

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.

Re: Data loading into QLIK Sense Desktop is very slow

Stoyan_Terziev — Thu, 28 Nov 2019 10:27:13 GMT

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.

Re: Data loading into QLIK Sense Desktop is very slow

lanlizgu — Thu, 28 Nov 2019 11:08:03 GMT

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:

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

Re: Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 28 Nov 2019 11:34:45 GMT

"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).

Re: Data loading into QLIK Sense Desktop is very slow

lanlizgu — Thu, 28 Nov 2019 11:34:37 GMT

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.

Re: Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 28 Nov 2019 11:49:58 GMT

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).

Re: Data loading into QLIK Sense Desktop is very slow

lanlizgu — Thu, 28 Nov 2019 11:56:22 GMT

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?

Re: Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 28 Nov 2019 12:03:47 GMT

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 😞

Re: Data loading into QLIK Sense Desktop is very slow

lanlizgu — Thu, 28 Nov 2019 12:11:23 GMT

I mean, in order of loading the query from MS Access, copy the query and let this query create a MS Access table and check if loading the MS Access table takes less time that loading the query

Re: Data loading into QLIK Sense Desktop is very slow

Joanna_M — Thu, 28 Nov 2019 12:40:03 GMT

Oh now I know what You mean 🙂

I've done this - query created a MS Access table (with sales data 2017-2019) - and it loads and opens in MS Access very fast.

Then I connected table mentioned above to QLIK (via OLE DB) ... and QLIK loads the data for over an hour.

But I found a way how to cope with this. I divided data from one big table into 3 smaller (one for 2017, another for 2018 and one more for 2019) - and load them one by one to QLIK. And it works! Loading the data step by step takes c.a. 10 minutes per table, so half an hour for all 3.

I know that's not a fine solution, but works for now.

Nevertheless - thank You all for time and help 🙂