Simple LOAD - taking too long ...

[datanibbler]

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

[patroser]

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

[datanibbler]

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)

[datanibbler]

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!

[datanibbler]

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).

[marcus_sommer]

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

[datanibbler]

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 😉

[datanibbler]

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.

[datanibbler]

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 ...

[datanibbler · Accepted solution]

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

[marcus_sommer]

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