Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Highlighted
Not applicable

Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

Hello QV community

we are currently programming a QV module which takes static historical data from predefined EXCEL lists. The lists have some attributes (about ~30) and list every attribute for each year:

e.g.

YearAttribute1Attribute2Attribute3Attribute4...
201388778823...
201499243464...
201577342345...
201655441134...

A final line is added dynamically. For this the Script draws the from an ACCESS database. (E.g. Attribute 26 - but this is done for every Attribute)

As an example:

///Kennzahl 26

Attribute_KZ_z_26:

Load

     Attribute_kennzahl,

     Attribute_z as Attribute_z_26

Resident Attribute_KZ WHERE Attribute_kennzahl='26';

Let Attribute_z_26=Peek('Attribute_z_26');

Drop Table Attribute_KZ_z_26;

The data is imported via the OLE DB driver for access.

The data then is concatenated (the years 2013-2016 from the EXCEL sheet + the active year 2017 from the ACCESS DB):

LOAD * INLINE [

attribute.auswertejahr, attribute_kz_z_1, attribute_kz_z_2, attribute_kz_z_3, attribute_kz_z_4, attribute_kz_z_5, attribute_kz_z_7, attribute_kz_z_8, attribute_kz_z_9, attribute_kz_z_12, attribute_kz_z_13, attribute_kz_z_14, attribute_kz_z_15, attribute_kz_z_16, attribute_kz_z_17, attribute_kz_z_18, attribute_kz_z_19, attribute_kz_z_20, attribute_kz_z_21, attribute_kz_z_22, attribute_kz_z_23, attribute_kz_z_24, attribute_kz_z_25, attribute_kz_z_26, attribute_kz_z_27,  attribute_kz_z_28,  attribute_kz_z_29,  attribute_kz_z_30,  attribute_kz_z_31

2016, $(Attribute_z_1), $(Attribute_z_2), $(Attribute_z_3), $(Attribute_z_4), $(Attribute_z_5), $(Attribute_z_7), $(Attribute_z_8), $(Attribute_z_9), $(Attribute_z_12), $(Attribute_z_13), $(Attribute_z_14), $(Attribute_z_15), $(Attribute_z_16), $(Attribute_z_17), $(Attribute_z_18), $(Attribute_z_19), $(Attribute_z_20), $(Attribute_z_21), $(Attribute_z_22), $(Attribute_z_23), $(Attribute_z_24), $(Attribute_z_25), $(Attribute_z_26), $(Attribute_z_27), $(Attribute_z_28), $(Attribute_z_29), $(Attribute_z_30), $(Attribute_z_31)

];

Now the problem: Everything works nicely! BUT only maybe until attribute ~25. Then the loading times start to get horrendous. The script nearly needs ~8 hours to load. Then until everythin is loaded, the script works perfectly. We could pinpoint the problem. The problem is indeed in this concatentation of data. When we remove a couple of attributes it is very fast, if we load the full ~30 attributes, it gets super slow. What are we supposed to do? Any ideas how we can adjust our prgramming, so that is loads faster? We are really kind of out of ideas, since the programming seems to be not wrong, as it works fine after loading. But the loading itself is super time consuming.

Any help highly appreciated

Tags (2)
5 Replies
MVP & Luminary
MVP & Luminary

Re: Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

The reason for the long loading times could be that there is not enough RAM available and the system swapped with the virtual RAM.

Beside them your load-snippets look a bit strange for me and there might be other more performant ways of doing what do you want to do - it's not really clear for me without the relevant parts of the script.

- Marcus

Not applicable

Re: Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

Thanks a lot -

ye possible there is not enough RAM - i thought about that as well. Even though the lists are not very large.

But do you have an alternative suggestion how to combine these lines?

Cheers,

Daniel

MVP & Luminary
MVP & Luminary

Re: Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

Without knowing how your script looks like it's hard to give a suggestion.

- Marcus

prieper
Honored Contributor II

Re: Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

From the description it does not sound to be soooooo many rows ...

Sounds rather that you are creating some huge cartesian tables.

Have you once tried with lesser data and checked the datamodel?

Another idea would be to redo the datamodel and to load convert the first table into a more regularized table with the CROSSTABLE-functionality?

Peter

Not applicable

Re: Very long loading times (8h) when adding just a couple of attributes (ACCESS+EXCEL)

Thanks for the ideas. I will look into it. Did not know about the crosstable functionality so far. Already big thanks!