topic Re: Optimization? in QlikView

Optimization?

datanibbler — Thu, 14 Sep 2017 09:15:20 GMT

Hi,

I am just looking through an app that is supposed to do some Quality-checking on the Background of another app to help in looking for the cause of a potential reload_failure ...

At the same time I am thinking about ways to maybe optimize this.

There is one IF_THEN_clause pretty much at the beginning using >> qvdcreatetime << of a rather large qvd - that takes quite a while - is there any way to get the same effect quicker?

Maybe I can store only the timestamp (>>NOW()) at the time that qvd is saved pretty much at the end of the daily script_run - would that make it faster, if I only load a very small file to check that?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Moreover, this function is, afaIk, only used to decide whether there is such a qvd at all, the actual creation_time is not relevant - this IF_THEN is merely wrapped around a concat_load. That seems like a case of "if I were going there, I wouldn't be starting from here" to me, no?

Re: Optimization?

datanibbler — Thu, 14 Sep 2017 10:59:31 GMT

I guess some Information - like the creation_time - are stored in the Header of a qvd, no? That is the question - I cannot answer that based on the help_file - if I just want to know whether a qvd with a given Name exists, what would be the most efficient method to find out?

P.S.: I just found another thread with a proposal from swuehl‌ using the >> Filelist << function. Might that be faster?

Well, I guess there's no better way to find out than trying. So let's see ...

Re: Optimization?

tresB — Thu, 14 Sep 2017 11:08:46 GMT

QvdCreateTime() - returns a timestamp of the file creation if the file exists, otherwise it returns a NULL. So you can check if the returned value is not-null, then the file exists, else - no such file. I am not sure - what exactly you are asking for.

Re: Optimization?

datanibbler — Thu, 14 Sep 2017 11:13:28 GMT

Hi Tresesco,

well, like the title of this thread is supposed to suggest, my question is about optimization - I know that this function can tell me whether or not the file exists, that's what it's supposed to do in this context - I just wonder whether this is the best possible way to get this Information? It seemed to take quite Long when I executed this in debug_mode - is there a way to do that quicker?

Best regards,

DataNibbler

P.S.: That solution by swuehl using >> FileList << seems to be a lot quicker indeed. That's just what we Need.

Re: Optimization?

tresB — Thu, 14 Sep 2017 11:19:05 GMT

Could you please share your script snippet that shows how exactly you are using that? I have just tested using the same function - with no significant time delay. A wild guess, are you using a network path for qvd?

Edit: Are you using a loop to check through folder hierarchy? If so, the loop itself could be the reason rather than the function, I guess.

Re: Optimization?

datanibbler — Thu, 14 Sep 2017 11:26:45 GMT

Nope - it's on a Server, the path to the qvd is in a variable - I'll paste it all here, the qvd is in a sub-Folder in the same Directory as the qvw - that is one of our development_Standards here.

The qvd is huge - about 1.3 GB - that might be why it's taking longer than usual ...

That script block is as follows

>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

// That path_variable has the value >> set vQVDPfadSchreiben = '.\QS_qvd\'; <<

// That table has been initiated with an >> autogenerate(0) << beforehand

if qvdcreatetime('$(vQVDPfadSchreiben)QS_S4_Fehler.qvd')<>null() then

Concatenate (QS_S4_Fehler) load
*
from
$(vQVDPfadSchreiben)QS_S4_Fehler.qvd (qvd);

endif

Re: Optimization?

prieper — Thu, 14 Sep 2017 11:27:49 GMT

why do you think, it takes long?

Tested with some 30M rows, and returned resutl in less than 1 second ...

Re: Optimization?

datanibbler — Thu, 14 Sep 2017 11:37:40 GMT

Ah, well - it might just be me - but tell me, how do you quickly create a 1.3 GB qvd_file to test with?

That would be really helpful going Forward - that way we could test the "futurability" of our Scripting right away ...

P.S.: Funny, even after restarting, it now does run quite quickly - it did take Long when I did it for the first time - but that might have been for some other reason. Well, there's no Need for optimization then.

Thanks a lot!

Best regards,

DataNibbler

Re: Optimization?

prieper — Thu, 14 Sep 2017 11:41:28 GMT

Not really sure, but think that these kind of functions read only the XML-header of the files, for which you do not need to open the entire file.

Re: Optimization?

tresB — Thu, 14 Sep 2017 12:12:05 GMT

If that could be a possibility, using filesize() could be a better choice (hope, to calculate file size, the file has not be opened. But possible, qlik might use same XML header logic), Try it.

Re: Optimization?

datanibbler — Thu, 14 Sep 2017 12:20:44 GMT

Thanks!

There are enough Points where I can have a critical look at potential for optimization - I am just looking at another script where, such as I understand it

- a list of all qvd_files in a Directory is generated

- this list is sorted asc. by filetime

- The ID of the one with the newest filetime (which happens to be the very last one) is extracted

- A statistics_table drawing from that identifies whether there is an up-to-date file in that table (well, there is only one file in the table 😉

- If that is the case, the path to that file is extracted

=> and then the file is loaded.

Oopsie - ah, well, yes, definitely possible and seemingly correct ...

Re: Optimization?

datanibbler — Fri, 15 Sep 2017 08:03:17 GMT

Hi,

These "funny" apps - those are Monitoring_apps (we have a herd of those, one for every extract_Job there is). All they do is collect some meta_data on the qvd_files that have been generated, for whatever that is worth ...

Up to now, the qvd's were loaded (optimized) just to Count the nr. of records there is. I replaced that with loading just one field, a >>rowNo()<< - but that does not seem to have shortened their runtime dramatically ... is there a faster way to do that?

Thanks a lot!

Best regards,

DataNibbler

Re: Optimization?

prieper — Fri, 15 Sep 2017 08:36:08 GMT

There are several functions to read meta-data from QVD,

below copied from OL-Help:

This may help, as they do not open the QVDs.

Re: Optimization?

datanibbler — Fri, 15 Sep 2017 09:15:18 GMT

Yep - the >> QVDNoOfRecords << might help 😉 I'll see what can be done.

The meaning and importance of all These apps is a bit unclear to me anyway - Monitoring all of the apps seems like a good idea in case several Tasks overlap on the Server and one Fails, that could help with Debugging ... but one app for every Extractor seems a bit over the top ...

Thanks a lot!