topic QlikView Hangs After Cancelling Large Script in QlikView

QlikView Hangs After Cancelling Large Script

Wed, 02 Feb 2011 16:48:19 GMT

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.

QlikView Hangs After Cancelling Large Script

Anonymous — Wed, 02 Feb 2011 18:42:15 GMT

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.

QlikView Hangs After Cancelling Large Script

Wed, 02 Feb 2011 18:58:30 GMT

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!

QlikView Hangs After Cancelling Large Script

Anonymous — Wed, 02 Feb 2011 20:35:39 GMT

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.

QlikView Hangs After Cancelling Large Script

andries_bos — Wed, 04 Jan 2012 11:33:35 GMT

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?

QlikView Hangs After Cancelling Large Script

Miguel_Angel_Baeyens — Wed, 04 Jan 2012 12:09:59 GMT

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

QlikView Hangs After Cancelling Large Script

Tue, 10 Jan 2012 21:32:18 GMT

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

QlikView Hangs After Cancelling Large Script

Thu, 12 Jan 2012 20:11:59 GMT

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.

QlikView Hangs After Cancelling Large Script

Fri, 27 Apr 2012 19:06:18 GMT

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

QlikView Hangs After Cancelling Large Script

Fri, 27 Apr 2012 19:19:17 GMT

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.

QlikView Hangs After Cancelling Large Script

Fri, 27 Apr 2012 19:28:37 GMT

I'm not using this specific file any more, so I can't confirm it, but I think I know what the problem was on the SQL side. This doesn't excuse QlikView locking up while waiting for data, but just in case it helps someone:

Microsoft SQL uses a technique called "parameter sniffing" to create good execution plans for stored procedures. Basically, the first time you run a sproc the database will build and store an execution plan based on the parameters you gave it. If you later run it with different parameters, the stored plan could be drastically inefficient.

For example, say you have a sproc with @StartDate and @EndDate parameters. You create it, and run it with the date 2011/04/27 for both the start and end date, just to see if it works. SQL looks at that narrow date range and decides that an index seek is appropriate. Later you run it with a start date of 2000/01/01 and an end date of 2011/04/27. SQL still uses its index seek execution plan, even though what it really needs for that broad date range is a table scan. This can make a huge difference, leading to run times of hours rather than seconds - I've seen it happen.

The fix is to specify reasonable parameter values when you first run a sproc after creating or re-creating it. This will help SQL to build a well-optimized query plan at the beginning.

More info: http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

QlikView Hangs After Cancelling Large Script

Thu, 31 May 2012 15:59:25 GMT

Thanks Jonathan,

The files are not large at all - only a few meg, maybe 20 TOPS. I've tried killing the process too and it still just hangs....

I was hoping I'd be able to identify which line from the Services tab to kill instead.

QlikView Hangs After Cancelling Large Script

vlad_komarov — Thu, 31 May 2012 17:30:26 GMT

I would suggest to try debugging your script (using Limited Load). You might have a Synthetic key(s) somewhere in the model, and QV can simply hang trying to handle that in large data set.

Re: QlikView Hangs After Cancelling Large Script

etl_tools — Fri, 27 Jul 2012 13:23:18 GMT

I had simular issues when i was loading data from different sql tables into same Qlikviev table

Salesdata:

LOAD * ;

SQL

SELECT F1,f2,...f50

FROM my_table1;

Salesdata:

LOAD * ;

SQL

SELECT F1,f2,...f51

FROM my_table2;

Number of fields is the same.

Note last field has a diifferent name.

Version 11 QlikView hangs.

Re: QlikView Hangs After Cancelling Large Script

Miguel_Angel_Baeyens — Fri, 27 Jul 2012 13:27:02 GMT

Hi,

That's likely because the huge number of synthetic keys that QlikView creates for the F1 to F49 fields. Using Qualify or concatenating fields will make the script work.

Regards.

Miguel

Re: QlikView Hangs After Cancelling Large Script

etl_tools — Fri, 27 Jul 2012 13:32:00 GMT

Yes that was silly mistake but it took a while to figire it out