topic Reducing the load time of a big table in QlikView

Reducing the load time of a big table

Wed, 25 Nov 2015 15:18:16 GMT

Hi all,

I have a Straight Table containing 600.000+ lines. The effect is loading this takes a while, and the application is unresponsive in the meantime.

Is there some way to partially load this and tell Qlikview to load the entire table upon explicit request only.

For example, load the first 100 lines, then use a button to start loading the remaining 600k+. I am explicitly looking for a solution to this problem in the front end (is possible), not in the load script.

Thanks!

Re: Reducing the load time of a big table

swuehl — Wed, 25 Nov 2015 16:37:08 GMT

I don't think there is a feature like this in QV.

Maybe you can use the show / hide condition and create two charts, first chart visible from the beginning, only showing limited data, second showing all data, but hidden first and then showed after clicking a button.

Re: Reducing the load time of a big table

Colin-Albert — Wed, 25 Nov 2015 16:42:17 GMT

This needs to be done in the load script, its not an option in the front-end.

The best approach is to use an incremental load to just load the transactions that have changed since the last load, and load the unchanged data from QVD files.

Also do you need to load all the columns? Reducing the width of the data being loaded can improve reload performance.

If loading from SQL it is better to just select the columns you require in the SQL script,rather than using "select *" to get all columns and then excluding columns on the QlikView side of the script.

Re: Reducing the load time of a big table

swuehl — Wed, 25 Nov 2015 16:45:04 GMT

You may also want to look into 'direct discovery':

Direct Discovery in Qlikview

Re: Reducing the load time of a big table

settu_periasamy — Wed, 25 Nov 2015 17:20:31 GMT

Hi,

Not sure about the performance. may be if (RowNo(TOTAL)<=100, YourExpression) ?

Create a Button & Variable. -> Assign the value in Button Action.

in Expression if($(v1)=0, if (RowNo(TOTAL)<=100, YourExpression),1)

Re: Reducing the load time of a big table

arthur_dom — Wed, 25 Nov 2015 18:57:46 GMT

Henk, why not limit the dimension, without others, with a variable?

Like the attached?

Re: Reducing the load time of a big table

Anonymous — Wed, 25 Nov 2015 19:15:51 GMT

Have a look at his blog post:

YA(H)Q Blog: Paged charts in Qlikview

Re: Reducing the load time of a big table

MarcoWedel — Wed, 25 Nov 2015 22:29:05 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 08:10:18 GMT

This one I considered already. Thanks!

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 08:12:09 GMT

Thanks for your suggestion. The columns and amount of rows are a given, no flexibility there at the moment. Incremental load wouldn't help, because it takes so long to render the entire table (because of so many rows), not to load the data.

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 08:22:29 GMT

Interesting suggestion. Due to the environment I am working in this is not possible for this Qlikview report though. Thanks!

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 08:29:46 GMT

This one looks good. Similar to what Marco is suggesting below, going to figure out if this works for my table as well. Thanks!

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 08:37:04 GMT

Thanks a lot! Similar suggestion posted by settu, am figuring out what works best.

Re: Reducing the load time of a big table

Colin-Albert — Thu, 26 Nov 2015 09:47:25 GMT

Can you add a "conditional show" expression so users must filter the data to select fewer than N rows before the chart is displayed.

Can you use drill-down groups so the chart is summarised and users can drill down to more detail?

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 12:31:32 GMT

It does not work, because not all values in each dimension are unique. In fact, there are thousands of the same for each dimension...

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 12:32:52 GMT

Wouldn't work because the dimensions have repeating values...

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 12:49:33 GMT

Good suggestions. Drill-down groups wouldn't work (the export of the entire table is important). Conditional show works obviously, but the data is exported so that would take multiple exports.

Re: Reducing the load time of a big table

Colin-Albert — Thu, 26 Nov 2015 13:53:48 GMT

Can you split the app into two.

One app is the data loader that incrementally loads the data in the background, does all the lookups and any manipulation of the data and stores the prepared data to QVDs. This app has no charts and just generates the QVDs. This data loader would be scheduled to run at regular intervals on the server.

The users load the prepared data from the QVDs in their app which has the charts and this load will be very fast, as it does not access the raw data, only QVDs.

Where are the 600,000 rows being loaded from?

Is the data local to the users or remote?

600,000 rows is not a lot of data in QlikView terms.

Re: Reducing the load time of a big table

Mark_Little — Thu, 26 Nov 2015 14:01:46 GMT

Hi,

Well I am not sure you have considered upping the hardware in the server?

Mark

Re: Reducing the load time of a big table

Thu, 26 Nov 2015 14:21:01 GMT

The issue is not on the loadtime on server. The qvd's are already generated. The table I'm generating contains of 600.000 rows, over 5 dimensions and 2 expressions. 'Rendering' the table takes about 2 minutes. Just in the Qlikview client.