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.
| Year | Attribute1 | Attribute2 | Attribute3 | Attribute4 | ... |
|---|
| 2013 | 88 | 77 | 88 | 23 | ... |
| 2014 | 99 | 24 | 34 | 64 | ... |
| 2015 | 77 | 34 | 23 | 45 | ... |
| 2016 | 55 | 44 | 11 | 34 | ... |
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
