topic Re: Simple LOAD - taking too long ... in QlikView

Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 07:51:30 GMT

Hi,

I have an issue:

- In my current app, I have multiple ways of reloading the script (or parts of it) based on user_input on the GUI:

- I have two buttons, one for a full (regular) and one for a partial reload;

- These two are again split in two using conditional execution (a >> IF ... THEN << bracket)

=> Because I cannot simulate this (not without mayor "retooling" to be repeated every time, anyway), I use TRACE statements to tell which part of the code has been executed.

There is not much data in there yet, just a few records.

My issue is such: The LOAD itself is seemingly finished in a second, just like it should - at least the TRACE statement highlighting the end of it is displayed pretty quickly.

<=> But then it takes a while (from a few seconds to a few minutes) until the button "OK" becomes available.

Can you tell me what might be the reason for this? This app is eventually supposed to be like a database where multiple users will enter data in the qvd file.

Thanks a lot!

Best regards,

DataNibbler

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 07:58:07 GMT

Hi DataNibbler,

this will be most often happens if the load generate some synthetic keys or circular references which could need a lot of time to calculate and/or there are some store-statements which could be take some time by large amounts of data.

- Marcus

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 08:03:03 GMT

Hi Marcus,

thanks for the answer!

However, there is just one table, so there cannot be a synthetic key or circular reference.

There is a STORE statement in two of the blocks of code to execute - but the table has 46 records, so that should not take long.Something needs some time to calculate, that's for sure, and I guess it is in the script - the GUI is only calculated after the execution_window has been closed, no?

P.S.: I will experiment a bit with the start and end of that >> IF NOT ISPARTIALRELOAD() << bracket, there might be a code that takes so long after the LOAD has finished.

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 08:18:56 GMT

Hi DataNibbler,

yes, this should be very fast. Are there further statements involved maybe some loops? What said the log? What happens if this runs in the debugger?

- Marcus

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 08:27:06 GMT

Hi,

I am just checking the code. The entire script is subdivided into several parts:

- The first part (standard elements) is executed in all cases

I cannot use

- Then there is the > IF NOT ISPARTIALRELOAD() < bracket

=> This is subdivided by use of the > IF [variable] = X THEN ... ELSE < function into two

- After the ENDIF statement, there are again some statements to be executed in all cases

- Then there is the > IF ISPARTIALRELOAD() < bracket

=> This is again subdivided by the same function (another variable)

The log says nothing about this: It looks completely normal and the entire execution (of one branch) takes just about a second. The log finishes with "Script execution finished" after a second or so. Nothing visible there.

I cannot easily use the debugger because I cannot distinguish between full (regular) and partial reload in the debugger. So I would have to change around my code to fit the debugger and there is usually a chance that somewhere along that line, the actual error goes missing, to turn up again when you stop using the debugger and return to normal execution 😉

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 08:39:33 GMT

I could imagine that the reason is the partial load then these data must be included in the datamodel by rebuilding the symbol-tables. I think if you looked to the taskmanager you will see some activities to this time.

- Marcus

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 08:43:59 GMT

yes, I guess it must be something connected with that.

Too bad - having multiple ways in which to reload my script (or parts of it) is crucial to this app.

Maybe I will deactivate the partial_reload_branch for testing and remove the ISPARTIALRELOAD() function and try with just the conditional execution. If that helps, I would just abandon the approach with the partial reload and use more conditional_execution_branches instead.

P.S.: I noticed something else. I had overlooked those >> IF ... THEN << clauses were not properly closed off - at least that's how it seems. I had one opening IF ... THEN for the full or partial reload and inside that another >> IF THEN << opening (for the variable for the conditional execution) but only one >> ENDIF <<.

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 08:50:25 GMT

Yes, I think partial loads will have only an advantage if you loads huge amounts of data quite often (maybe hourly). If not normal incremental loads will be fast enough and easier to handle.

- Marcus

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 08:55:42 GMT

Hi,

I'm with you there. The issue is, in this case the partial load has nothing to do with the amount of data to load. There are completely different blocks of code to execute.

That does not seem to have been the problem, either. I deactivated everything to do with the partial reload, leaving only the conditional execution, but I still had the same issue - the script finishes in about 1sec, then comes the final TRACE statement, the button OK becomes available - and I see the cursor indicating that something is still calculating and I have to wait another 20sec approximately until I can click the button.

This seems to be a very difficult one - but then, QlikView is not your typical tool to build a database. I didn't expect it to be easy ...

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 09:09:49 GMT

I'm not sure if to use qlikview as database is very practical. It needs many logic to develop and test things which others have already developed and tested. So I think it could be easier to use qlikview as a frontend and control-tool but the backends are real databases, maybe these postings are helpful:

https://community.qlik.com/search.jspa?q=write+database&type=document

- Marcus

Re: Simple LOAD - taking too long ...

patroser — Tue, 05 May 2015 09:19:12 GMT

I once faced a bug with similar symptoms.

Try to switch window after your reload should be finished.

So, if you think the reload just takes 5 seconds, wait a few more before switching the window with Alt+Tab. Switching back could enable the OK-Button. At least that was the solution to my problem.

I sometimes have this problem with really fast scripts. the log window with the OK-Button is somehow inactive and there fore i need to switch the window or click somewhere else to reactivate it to be able to click on OK.

Let me know if this also works with your problem

Patrick

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 09:25:39 GMT

Hi Marcus,

sure. Using QlikView as a frontend and a real database in the background does seem more logical. The issue is, there is nobody here to create the database that we want and spending money is not within my sphere of influence - it's only for a handful of records, never more than 20 I guess, but with various people to work with it. This was supposed to be done as an ACCESS_database - but well, that was supposed to be complete on Jan 01, but as there was no one to actually do it, the task was now given to me.

Anyway - whatever kind of database we have in the background, whether that is an SQL database of some kind or just a flat file - that leaves me with the requirement of including in my frontend_app several ways to execute different blocks of code.

The process is like this:

- We get new records from our customer in their system daily, in their system, so they are exported
<=> there need not be new records every day, but we check daily

- We (various people all over the place) add our information to these records and work on them

- At some point, one record is completely processed, then Feedback to our customer is given in some way
and the record drops out of the database

=> I need to execute various blocks of code:

- I need a way to load the new masterfile (from xls) and insert it into the database;

- I need a way to load the existing database (qvd) - to get any updated records in case someone else
has already worked on it.

- When someone is working on the database and has updated one record, I need a way to refresh the table
such as it is in memory and update that specific record => then another one can be selected for updating;

- Last, I need a way to "close off work_session" and save the table back to qvd.

In general, this does work - I can tell from the TRACE statements I have that I can execute each of the four different variations all right; My remaining issue is just that it takes so long (AFTER the button >> OK << has become available at the end of the script_execution)

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 09:28:27 GMT

Hi Patrick,

no, unfortunately this won't solve my problem - the script takes just about 1sec (regardless which block of code I choose to execute), the log is finished after that time and the button OK does become available after about 1sec - but the cursor keeps on turning (that is the successor of the well-known hourglass indicating that something is being worked on or else) and it takes a while until that ceases and I can actually click the button.

Thanks for answering anyway!

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 10:13:19 GMT

Hi,

I guess I'll have to put some more thinking into this.

I am just considering - there are going to be various people working on this, potentially at the same time (I know of no way to prohibit this like in Excel).

Different people are not going to put differing infos in the same fields, which is already a great relief, but

=> When two people are simultaneously working on the database and one has just saved his work, the other one has the older version in his machine's memory
=> When that second person works on one or more records and that always turns round in his machine's memory before the work is saved back to qvd, he/she is going to overwrite the other person's input.

=> So it seems that it is crucial that I break this down into the smallest possible time_intervals (to minimize the risk of
overlaps)

=> When one person has completed his/her work on one specific record, regardless whether he/she is going to
     process another record or not, the existing qvd should be loaded, with a number of IF() clauses in the LOAD to
     consider any potential edits to fields.
     => The existing table in memory has to be dropped immediately (or potentially even before reloading the qvd) to
           avoid any synthetic keys;
     => The table then has to be immediately stored back to qvd, regardless whether another record is going to be
           processed or not.
           (if it is, that will imply another reload of the qvd, so any data input by another person will become visible).

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 10:59:44 GMT

This is one of the main-reasons to use a real database which have an inbuild lock-feature if someone accessed a record.

Maybe you could build a logic to lock the qvd per windows filesystem by opening the qvd in another task or move it to a temp-folder and the store-routines includes various checks if the qvd is available and/or delay a new store or or .... and inform the user what happens. This will be very difficult and have always a remaining risk.

- Marcus

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 11:37:05 GMT

Hi Marcus,

you're right. But, as I said, never will two persons enter different info into the same field.

Firstly, different persons will have different roles and thus different privileges (to view/ edit different fields);

By refreshing the in-memory table from the qvd and updating it and immediately storing it again - all that should be triggered by the "update" button - I minimize the time window where I have a potential for two peoples' work getting into conflict - only if two persons click on "update" in the same split-second there will be trouble - most probably the qvw will generate some error then. But that is very improbable.

That is my idea 😉 I haven't tried it yet. i am going to now.

=> Then I would need three different ways of reloading:

- loading a new masterfile and appending any new records (if there are any) to the existing database

- reloading the existing database (qvd) without updating

- updating the existing database (qvd) with additions to one record.

I'll keep you posted.

Off goes the Lucy 😉

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 12:24:05 GMT

Hmmm ... I am still having that issue that, after the script has finished and the button OK is already available, it takes a while before I can do anything.

So it wasn't due to the partial reload, I have completely abandoned that approach for now.

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 12:55:39 GMT

Ah .- I have unticked the checkbox to "close (the execution_window) after finishing" - so it might be that, while that window still remains open, the script is already finished and one or other of the objects I have on the GUI takes so long to recalculate.

I can't really imagine which one, they are not so complicated, but it's a new assumption to test ...

Re: Simple LOAD - taking too long ...

datanibbler — Tue, 05 May 2015 13:06:16 GMT

Well,

I have solved the riddle - no, I haven't, it just disappeared ... dropped into the gap between two lines of code, I suppose 😉

Here is a challenge for the QlikView_experts to explain:

I again ticked the checkbox to close the script_execution_window after execution - and my issue is gone. Now the whole process takes about 1sec and I have no turning_cursor_alias_hourglass, but I can immediately do something.

The only drawback is, I now have to actually look into the log to see the TRACE statements and thus tell which code was executed.

Now that the basic function is working, I can focus on adding more fields - and loading the matrix of privileges (who can read and/or edit what field(s).

Thanks for your good will and perseverance to help!

Of course, I'm far from done, but I'll open a new thread for any new issues.

Best regards,

DataNibbler

Re: Simple LOAD - taking too long ...

marcus_sommer — Tue, 05 May 2015 13:20:32 GMT

Hi DataNibbler,

maybe it's an option for you to open the log-file automatically after a reload is (nearly) finished. Have a look here Re: Is that Possible to Specify Path of the "Generate Logfile"? to a routine which moved log-files.

- Marcus