11 Replies Latest reply: Oct 3, 2013 4:28 AM by Stephen Matthews RSS

    Hierarchy load causes QlikView to crash

      Hello,

       

      I have an issue with creating a large hierarchy in QlikView.

      My script loads data from Oracle db into a temporary table and then using Hierarchy function to create a tree.

      The data has grown to about 6 000 000 rows and now QV stops responding when I try to load the data.

      Via debug I found out it's the Hierarchy function that causes QV to crash.

       

      Any tips how can I actually load all the data without leaving anything out?

      Is there a limitation somewhere inside QV that causes the application to crash?

       

      Thanks,

       

      Miro

        • Re: Hierarchy load causes QlikView to crash

          Hi Miro

           

          It's difficult to say without seeing the model.  Can you share?  Or at least list the code?

           

          I would suggest you first try loading only a 1000 records into each table (or some other way of limiting your dataset) and keeping an eye on the Performance via Task Manager).  Have you got log files switched on?

           

          If it loads, check your table connections and specifically what the hierarchy table is generating.

           

          I hope that helps.  Let me know how you get on.

           

           

          Thanks


          Steve

            • Re: Re: Hierarchy load causes QlikView to crash

              Hi Steve,

               

              I have added the "where rownum <= 10000;" limit into the SQL Select and the load worked correctly and took about 15 minutes.

              The Qv.exe process in Windows used around 2GB of RAM.

               

              When there is no limit it goes up to 5 GB of RAM. The reason of the crash might be using too much memory.

               

              I cannot share the full model, but the code that loads the data looks like this:

               

              TABLEtmp:

                LOAD

                if(isnull(PARENTNODEID),-1,PARENTNODEID) as PARENTNODEID,

                   AGE_,

                   AUID,

                   CLOSED,

                   CODE,

                   CREATIONDATE,

                   CREATOR,

                   FULLQUALIFIEDCODE,

                   ID,

                   DESCRIPTION;

              SQL SELECT

                   PARENTNODEID,

                   AGE_,

                   AUID,

                   CLOSED,

                   CODE,

                   CREATIONDATE,

                   CREATOR,

                   FULLQUALIFIEDCODE,

                   ID,

                   DESCRIPTION

                FROM table1

              where rownum <= 10000;

               

               

               

              TABLE:

                Hierarchy(NODEID, PARENTNODEID, DESCRIPTION, NAME, DESCRIPTION, PATH, '#', LEVEL) LOAD

                   PARENTNODEID,

                   AGE_,

                   AUID,

                   CLOSED,

                   CODE,

                   CREATIONDATE,

                   CREATOR,

                   FULLQUALIFIEDCODE,

                   ID as NODEID,

                   DESCRIPTION

                Resident TABLEtmp;

               

               

              Thanks

               

              Miro

                • Re: Re: Hierarchy load causes QlikView to crash

                  Hi Miro

                   

                  Do you drop the TABLEtmp after the resident load?

                   

                  Steve

                    • Re: Re: Hierarchy load causes QlikView to crash

                      Yes,  the TABLEtmp is dropped after the resident load.

                        • Re: Hierarchy load causes QlikView to crash

                          So, there is no synthetic key?  The table joins on NODEID?

                           

                          How long does the load take (with the 10000 limit) without loading the hierarchy?  How long does the hierarchy load on it's own take?

                           

                          For hierarchy, is there any duplication in data?  A 'Load Distinct' would help here. Are there redundant, date delimited nodes which can be removed?

                           

                          The code  if(isnull(PARENTNODEID),-1,PARENTNODEID) as PARENTNODEID,  interests me.  You may be loading a considerable amount of useless data where the PARENTNODEID is null.  On your hierarchy load try putting the qualifier;

                           

                          Resident TABLEtmp where NOT PARENTNODEID = -1;

                           

                           

                          Regards


                          Steve

                            • Re: Hierarchy load causes QlikView to crash

                              Yes, the table joins on NODEID.

                               

                              With the 10000 limit the select takes about 20 seconds and the hierarchy load about 40 seconds.

                               

                              There should be no duplication in the data. However I need to confirm this with someone who understands the DB/application structure.

                               

                              I have tried "Resident TABLEtmp where NOT PARENTNODEID = -1;", but there is no difference.

                              It looks like there only about 60 rows where the PARENTNODEID is null.

                              All the other rows have a valid ID.

                               

                              Regards

                               

                              Miro

                                • Re: Hierarchy load causes QlikView to crash

                                  Hi

                                   

                                  How many records are there in TABLE in total and how many of them are PARENTNODEID -1?  How long does it take to load the data and hierarchy without the 10000 limit?

                                   

                                  One thing is certain, 6000000 records with a hierarchy should be feasible.  Of course, this depends upon how many fields, uniqueness of fields, hardware, etc.  But we've got some heavy tables, lots of fields with 10,000,000 records attached to a hierarchy and we don't have any issues (at least none that bring the model down).

                                   

                                   

                                  Steve

                                    • Re: Hierarchy load causes QlikView to crash

                                      Hi Steve,

                                       

                                      it is exactly 6 247 804 lines.

                                      There are no rows with PARENTNODE = -1 in the db.

                                      There are 69 rows where PARENTNODE is null.

                                      The null rows are converted to -1 in the TABLEtmp load.

                                       

                                      I have accomplished a successful hierarchy load without crash, but it took more than 24 hours.

                                      The hardware I'm using:

                                      CPU: Intel Core i7-3770 @ 3.40 GHz

                                      RAM: 16 GB of RAM (15,8 GB usable)

                                      OS: Windows 7 Professional 64-bit


                                      I need to somehow optimize the load because 24 hours of processing is just too much.


                                      Miro



                                       


                                        • Re: Hierarchy load causes QlikView to crash

                                          Wow, this is a tough one.

                                           

                                          If you have a load model, this is your opportunity to look at the data and see how it is hanging together.

                                           

                                          A couple of further questions;  how many records are in the data table that you are connecting to?  And how are you connecting to the other table (a join or simply allowing QV to perform the association)? 

                                           

                                          I would strip the null values out of your load, they could cause issues (although I'm not sure 24hr worth of issues but it would be good to eliiminate this issue).

                                           

                                          I would question the 6million+ records for the hierarchy; what is this a hierarchy for?  I find it difficult to imagine your company's structure has 6million+ org units.  Also, try using a LOAD DISTINCT on your TABLE load.

                                           

                                          One last question, how long does the load take if you use a QUALIFY *; at the beginning of your code?

                                           

                                          In terms of hardware, it looks like you got enough memory for a reasonable model.

                                           

                                          Keep me updated - hopefully we're getting closer to the issue.


                                          Steve

                                  • Re: Hierarchy load causes QlikView to crash
                                    Juan Pedro Hidalgo

                                    Hey Miro,

                                    I had the same problem you are experiencing now. The problem was strictly related to the hardware that was not sufficent for my hierarchy to complete .

                                    If anyone has some other tips, and i already tried all you suggested steveriano, would be much appreciated since i still got the loadscript for that hierarchy. 


                                    Cheers

                                    Juan Pedro