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.
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';
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!
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.
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?
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.
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.
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.
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?
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.
I had simular issues when i was loading data from different sql tables into same Qlikviev table
LOAD * ;
LOAD * ;
Number of fields is the same.
Note last field has a diifferent name.
Version 11 QlikView hangs.