QlikView Hangs After Cancelling Large Script

[#1]

Does anyone else have this problem? My copy of QlikView (v10, Win x64) consistently hangs (locks up) if I cancel a long-running LOAD SQL script. The script executes a stored procedure which typically takes several minutes to run.

I've tried leaving it in this state for several minutes, and it hasn't resumed. Maybe it will resume when the stored procedure finishes executing, but if I had time to wait around I wouldn't need to cancel it.

This is a problem, as the source server is out of town, and large (millions of records) data sets can take over an hour to load. I really don't want to wait 90 minutes after I've spotted some small mistake before I can correct the error and start loading good data.

The bigger problem, of course, is that script execution occurs in the UI thread, a serious design mistake. A workaround would be to install a copy of QlikView on the data server and execute SQL locally then deploy the finished file, basically a jury-rigged SBS.

[Anonymous]

Have you tried using the debugger and limit the # of rows which is returned? This will only help if WAN is the only problem and stored procedure has started returning the rows. If it takes a lot of time for the stored procedure to start returning the rows, you may want to optimize the SQL within the SP. Maybe you have a real need to reuse the existing stored procedure, however, if possible, I would recommend that you bring the raw data (select * from single_table) and do all the joins & filters within QlikView.

Regards,

Dinesh.

[#3]

I have not tried debugging. Since the script is very simple (connect, load, sql), I can't imagine what it would tell me. I validated that the row count was small by running the exact SQL command outside of QlikView.

Oddly, this file has stopped hanging. I haven't changed the report itself, or the stored procedure, and I made just one small change to the script: I added an explicit value for the sproc's second parameter:

SQL EXEC ProcName @StartDate = '2011-01-01';

...became:

SQL EXEC ProcName @StartDate = '2011-01-01', @EndDate = '2011-01-31';

Bizarrely, it's now working fine, even if I take the @EndDate parameter back out and run the exact same script which failed a dozen times earlier this week.

Regarding your last point: what virtues do you see in performing joins and other logic in QlikView rather than SQL? The underlying tables have millions of rows, so paring this down sooner rather than later saves time. Of course, my real reason for working in SQL is that I already know it forward and backward!

[Anonymous]

You can have a single qvw file which would contain all the millions of rows, which you can then let the users do the filtering. The other advantage is that you access your database once as you dont have to go back to database for different filters.

Dinesh.

[andriesb]

I'm also suffering from freezing/ hang ups when cancelling a large load job. Nothing seems solve this freezing except to kill the executable.

When selectin the "End Here" , I'm presented with a lot of other Script errors due to this interrupting of the loading process.

I have to 'target'  between the 'OK' and the "End Here" button. I haven't found a better working solution for this freezing that mainly occurs during the testing phase.

Hopefully v11 would be better?

[Miguel_Angel_Baeyens]

Hi,

The "End Here" button does not terminate the execution right away, but before ends the instruction that was being executed, and creates links between tables and so. Depending on the script, the End Here button might return a working document (say for example that the rest of the script were variable assignments), but it may cause some unwanted synthetic keys that, once the script is normally run, there are DROPs, JOINs and so, but forcing the termination may take some time creating this synthetic tables.

If the data set is huge, the time to finish all the pending operations may take really long.

Hope that makes sense.

Miguel

[#7]

Yep I just started getting same error running locally. Even if I do a inline statement with one line (and no other statements) I get QV hung at the end of the script. Looking at memory, QV is using up all memory & it does not come back. Tried with sr3 so I updated with sr4 with same result. Opened old scripts created new ones, nothing fixed it.

Uninstalled QV and downloaded sr3, no change, memory did not release, but ratcheted up ever higher to my max RAM limit.

Uninstalled & tried with sr4 which did not help either. If I use task manager to kill it I get memory released right away. If I can't figure out support will be called.

Bruce

[#8]

Good luck with that, please post back if support helps you.  My eventual workaround was to stop using sprocs for actual reports, and instead I created a layer of QVDs which load from views.

Has anyone tried this with QV11?  Does it use proper multithreading now?  There's no good reason the system can't just drop the reload process when the user aborts it; it should roll back to the last good state in the reload, or just revert to the saved file if necessary.

[#9]

My problem is even bigger - QV hangs after doing things like:  clicking on minimize an object if the save is not complete.  This is just an example. 

I end up having to hard reboot my PC (at least I can safely close all other windows first).

Nothing works - not even Task Manager force application end.

Is there any trick you know of to shut down QV without having to power down the PC?

Thanks,

Susan

[#10]

Ouch.  Does this happen with all QlikView files, or only the bigger ones?  I've found that files up to around 50 MB are usually OK, but one which is 90 MB is frequently a problem.  With that in mind, I've worked around some issues by paring down the data.  I have 3 GB of memory on my dev machine, you may be able to do more or less depending on how much memory you have.

If Applications | End Task doesn't do it in Task Manager, you can try killing the process itself (qv.exe on the Processes tab).  It probably won't do anything different, but sometimes it works better.