How can I work to turn my job faster and/or use less memory?

[mlarruda]

Hi,

I have a database with some millions of rows and around 25 columns and, when loading it, QlikView often seems to "freeze". Checking the Windows Task manager, I could conclude that QV is consuming a lot of memory and it is delaying the loading.

Any one knows what I can do to manage it and accelerate the loading?

Thanks

[Tanalex]

How many millions of rows?  Care to share your load script that is "freezing"? 

[mlarruda]

Around 25 millions, loaded from 9 different files (6 qvd and 3 csv). Unfortunately, I can't share the script, but I could detect that it is freezing in a segment like the bold one below:

Tab1:

LOAD

aaa as AAA,

bbb as BBB,

(etc.)

FROM file 1

LOAD

aaa as AAA,

bbb as BBB,

(etc.)

FROM file 2

(etc.)

LOAD

aaa as AAA,

bbb as BBB,

(etc.)

FROM file 9

Tab2:

NoConcatenate LOAD AAA,

BBB,

(etc.)

Resident Tab1;

LOAD AAA,

min(BBB) as BBB.MIN

max(BBB) as BBB.MAX

(etc.)

Resident Tab1

Group by AAA;

DROP Table Tab1;

Any hint about what can I do?

[wdchristensen]

Qlik is as "In Memory" application so you might want to check RAM usage while the script is running. Depending on the amount of memory and how much data you are loading, that could be a bottleneck. I have also noticed imports slow down due to the source of the data which really has nothing to do with Qlik (network / application contention / database server load / backups). Do you notice that loading at different times of the day makes any significant difference (+/- 25%).

[mlarruda]

Well, I do not noticed change in the speed in different times of the day. I left my computer on during all weekend and, when i was back today (monday) morning, everything was frozen (even ctrl-alt-del didn't work) and I needed to restart the computer.

On the other hand, I noticed change in the speed when changed the format of my data: csv files are loading very slower than xlsx ones.

[olivierrobin]

hello

you could try this method to find main and/or max

“Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

[marcus_sommer]

You are doing in the bold-sector an aggregation-load which is probably the most resources-consuming statement within Qlik and applied to a large dataset you might easily exceed the available resources of RAM + CPU on your system.

Therefore try to check if you need this aggregated table really within the script or if you could calculate it more easily in the UI.

Whereby this doesn't mean that such a load couldn't be optimized. Quite useful in such cases is to apply any where possible clauses in one step before and also to order the table, see: Optimize Group By Performance. Further helpful could be to include only those fields in this load which are essential for it and if necessary to merge other needed data afterwards.

Depending on the data which should be aggregated you might als be able to get the min/max values with cummulations per interrecord-functions like previous/peek or like suggested from Olivier whereby it only returned the min/max without any regard to a grouping dimension but by concatenating these fields (dim + measure) it would be possible.

- Marcus

[prieper]

Shouldn't the various tables be concatenated? One file may have additional columns and then QV tries to create some keys ...

[mlarruda]

Yes, these tables are all concatenated.

[prieper]

Did you check, that there is just one table? Let it debug with just some 10 records.

From my experience would always expressively order a concatenation when you wish to concatenate. This makes a script also better readable and prevents for errors in case of changes:
Tab1: LOAD .....FROM File1;

CONCATENATE (Tab1) LOAD ..... FROM File2;

I do not quite understand the step with Tab2 - why do you do so?