Appending one linktable to another

[datanibbler]

Hi,

in the app I'm currently working on, the linktable from one app is loaded with about 8.000 records;

Then another linktable (from a different app) is to be concatenated, it has a bigger nr. of fields and it has about 73mio records

=> As it was, the big table was concatenated to the small. I separated the LOAD (of the big table) to get it optimized and then I do a RESIDENT LOAD to concatenate it to the small. That worked fine, but I ended up losing a good bit of the "optimization_win" by going through those 73mio records again. => So I had the seemingly good idea, instead of appending a very large table to a very small one, to rather do a RESIDENT LOAD of the very small table and append it to the large one

<=> that did not work at all. The Computer kept on freezing every time I tried.

Can any of you think of a reason why that happened? I know that the tables have a differing nr. of fields, but well, it worked before ...

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

the loading itself should normally be working. Is this load the last one within the script and qlik struggles afterwards by creating the datamodel because there are any synthetic keys? Maybe the first qvd-load and/or the concatenate-load is loaded into a different tables or something similar goes wrong ...

Beside them I'm not sure if it's the best approach for a datamodel to have such large link-tables ... I would assume that the performance of the application will be rather slow.

- Marcus

[datanibbler]

Hi Marcus,

yes, I guess it will be so - but well, that is what the app was like when I took it over from a colleague - the colleagues routinely work with link_tables here which tend to be rather big. I always used the calendar as my link_table since most tables to connect usually have a date. But well, I'm not in the Position to question the way things have been done for some time yet ...

That said, that load is not the last one, but well, that's the Point where the app freezes, so synthetic keys might be a good Explanation - but how to test that? I have been thinking about it for a while now.

[marcus_sommer]

Hi DataNibbler,

if it's not the last load the reason are not synthetic keys because creating the datamodel with all the table-associations happens only after the loadings are finished.

It's quite difficult to say whats wrong here - I think I would in this case use the debugger with a reduced number of records to be able to see the final datamodel and if there is no real hint to the issue I would put some trace-statements with the number of records for certain tables, variable-contents and similar informations and also exit the script after certain loadings and if it worked after the next one and so on.

This kind of freezing is a common signal of a lack of RAM probably caused by failings with any joins, aggregation loads and similar heavy transformations.

- Marcus

[datanibbler · Accepted solution]

Thanks Marcus!

I will have a Close look at this issue. I guess there's more to do, it's quite possible that I don't Need all there is in that "linktable" as it is actually the one from another app - a colleague built this without really caring about Performance and such in the first step, which is probably the right way to go.

Now I have taken this over, the colleague has some other work to do, and I will now have a Close look into what can be optimized, what data is really needed etc. - my first Task on this app is breaking it up into three - one EXTRACT, one TRANSFORM and one GUI, according to the Standard here, and then I will look into any potential for optimization.

I'll Close this for now. I'll open a new thread if I have to revisit this.

Best regards,

DataNibbler