15 Replies Latest reply: Jul 27, 2012 9:32 AM by Mike Rewnick RSS

    QlikView Hangs After Cancelling Large Script

    Jonathan Shaltz

      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

          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
              Jonathan Shaltz

              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! Wink

                • QlikView Hangs After Cancelling Large Script

                  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
                    Jonathan Shaltz

                    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
                  andries Bos

                  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 de Arce

                      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

                      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
                          Jonathan Shaltz

                          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

                          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
                              Jonathan Shaltz

                              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
                              Vladimir Komarov

                              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.