16 Replies Latest reply: Jul 29, 2011 9:32 AM by Daniel Rozental RSS

    Does Resident Table improve performance?

      Hi,

      Could anyone tell me if the use of resident tables would help improve performance?

      Regards

      Raj

        • Does Resident Table improve performance?
          Steve Dark

          Hi Raj,

          It depends entirely on how you use them. They can be very useful in your load script to reduce joins in your eventual data model - if you do this then you can drastically improve performance. However, they can significantly slow your load routine as data needs to be parsed twice. Apparently saving to QVD and loading from file is quicker than a resident load - but I have not personally benchmarked this.

          For general performance of documents it is key to reduce number of links. Use of the ApplyMap statement is the best way of doing this. For performance of load script one key factor is use of QVD's (allowing incremental and concurrent loads) and making sure these QVD's load in superfast mode.

          Information on all of the above can be found in the QlikView manuals and on QlikCommunity.

          Hope that helps.

          Regards,
          Steve

          • Does Resident Table improve performance?
            Deepak Kurup

            hi Raj,

            Yes . Using resident helps in performance. But use only if there are claulation or conditiion required in table.

            eg:

            Test:

            Load a, b, c from table;

             

            Load a, if(b='A', Yes,No ) type, C resident Test;

             

             

             

             

            • Does Resident Table improve performance?
              Colin Hancox

              Hi Raj

              Compared to what? What are you trying to do?

              If you are doing transformation on a very large dataset, I've found that saving to QVD and then doing transformation by loading from the QVD is significantly faster than using a resident load.

              • Does Resident Table improve performance?
                Sushil Kumar

                resident table are something which are stored on local so when you working with those they are fast to access and in this way we can say that they can improve the performance.....

                  • Does Resident Table improve performance?

                    Hi Sushil,

                    I am using QVD file to load data. Both methods are shown below:

                    Method1 -

                    Table1:

                    Load * from abc.qvd where code=101

                    Table2:

                    Load * from abc.qvd where code=102

                    Now instead of loading data from the qvd twice in the above example should i do as shown in the following Method 2 -

                    Table0:

                    Load * from abc.qvd

                    Table1:

                    Load * resident Table0 where code=101

                    Table2:

                    Load * resident Table0 where code=102

                     

                    Could you tell me which of the above 2 methods are better for performance?

                    I hope my question is clear...

                    Raj

                      • Does Resident Table improve performance?
                        Steve Dark

                        Hi there,

                        Looking at your example (loading * from QVD three times) this will not create three tables in the data model as the columns will be identical each time. This will therefore just create a single table with some of the rows duplicated. Is this what you are aiming for?

                        Regardless of that QVD load instead of Resident will definitely be quicker.

                        Regards,
                        Steve

                          • Does Resident Table improve performance?
                            Magnus ÅVITSLAND

                            Hi.

                             

                            If loading QVD OPTIMIZED, it is faster than loading from resident tables.
                            I can't prove it right now, but I see this all the time.
                            I only do resident loads when necessary.

                             

                            QlikTech states that QVD:s are optimized for loading fast from them.

                              • Re: Does Resident Table improve performance?
                                Enrique Herranz

                                Hi,

                                I found a very good article ( a 36 min webex recording plus slides in pdf ) called "Understanding & Best practices with QVD files", by Rob  Wunderlich.

                                 

                                http://qlikviewnotes.blogspot.com/2011/02/qvd-questions-and-answers.html

                                 

                                My understanding is ( maybe wrong ) that due to the QVD files Optimized Load Limitations ( mainly no transformations or new fields allowed ), in case you want to add calculated dimensions or new fields for example when loading a QVD file, a solution could be to load the QVD file as it is ( no manipulations inside, to have an Optimized load ), and then add these new fields in a new table using RESIDENT load ( so the option 2 as commented should be faster ).. Also, should the first loaded table from the QVD file be drop after the resident load ?. Unfortunately I am not an expert developer. Could someone confirm/comment on this ?

                                Thanks

                                Enrique

                                  • Re: Does Resident Table improve performance?
                                    John Witherspoon

                                    I haven't watched the webex, but you can add fields after a QVD load like this:

                                     

                                    MyTable:
                                    LOAD
                                    MyKey
                                    ,SomeField
                                    ,SomeOtherField
                                    FROM MyFile.qvd (QVD)
                                    ;
                                    LEFT JOIN (MyTable)
                                    LOAD
                                    MyKey
                                    ,if(SomeField>5,'Big','Small') as SomeFieldSize
                                    ,SomeField + SomeOtherField as SomeSummaryField
                                    RESIDENT MyTable
                                    ;

                                     

                                    You would not drop MyTable in this case because you're joining to it.  I haven't compared the speed to making a new table and then dropping the original.  I'd expect that to use more memory (untested), and we're a bit short on memory on our publisher server, as well as on our development PCs, so I try to conserve it where possible.

                                      • Re: Does Resident Table improve performance?
                                        Daniel Rozental

                                        I did a couple of tests and didn't found much difference in processing time or memory utilization. Tested with 10 Million records, Core i5, 4GB memory.

                                         

                                        Doing this took 24 secs

                                        R00:
                                        LOAD ID,
                                             ShipperID, 
                                             OrderDate, 
                                             CustomerID, 
                                             Discount, 
                                             ProductID, 
                                             Quantity, 
                                             UnitPrice,
                                             Quantity*UnitPrice AS NetSales,
                                             Quantity*UnitPrice-Discount AS Sales
                                        FROM
                                        C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
                                        (qvd);
                                        
                                        

                                         

                                        Doing this took 27 secs

                                        R00:
                                        LOAD ID,
                                             ShipperID, 
                                             OrderDate, 
                                             CustomerID, 
                                             Discount, 
                                             ProductID, 
                                             Quantity, 
                                             UnitPrice
                                        FROM
                                        C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
                                        (qvd);
                                        
                                        
                                        R00_1:
                                        LOAD ID,
                                             ShipperID, 
                                             OrderDate, 
                                             CustomerID, 
                                             Discount, 
                                             ProductID, 
                                             Quantity, 
                                             UnitPrice,
                                             Quantity*UnitPrice AS NetSales,
                                             Quantity*UnitPrice-Discount AS Sales
                                        RESIDENT R00;
                                        
                                        
                                        DROP TABLE R00;
                                        
                                        

                                         

                                        And this also took 27 secs

                                        R00:
                                        LOAD ID,
                                             ShipperID, 
                                             OrderDate, 
                                             CustomerID, 
                                             Discount, 
                                             ProductID, 
                                             Quantity, 
                                             UnitPrice 
                                        FROM
                                        C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
                                        (qvd);
                                        
                                        
                                        left join(R00)
                                        LOAD
                                             ID,
                                             Quantity*UnitPrice AS NetSales,
                                             Quantity*UnitPrice-Discount AS Sales
                                        Resident R00;
                                        
                                        
                                          • Re: Does Resident Table improve performance?
                                            Enrique Herranz

                                            Well, it seems there is not significant differences then. Maybe it could depend on the type of calculated field we introduce when loading the QVD.

                                            Not sure if adding a new field using an if statement in your first R00 test ( like the one John adds after the left join )..or maybe adding a WHERE statement would make things slower in the first test. Not sure you could do that test and see what happens ...otherwise I would conclude there is no need to use left joins or resident loads here...the simplest way as I see it would be to add the new/calculated fields when loading the QVD.

                                             

                                            What it is not clear to me then is the speed difference between a QVD Optimized Load and a QVD Non Optimized Load ( as if we add fields during the QVD load, it seems as per the QVD best practices slides, we break the rules of the Optimized Load ). Any ideas on this ?

                                            • Re: Does Resident Table improve performance?
                                              Enrique Herranz

                                              Well, it seems there is not significant differences then. Maybe it could depend on the type of calculated field we introduce when loading the QVD.

                                              Not sure if adding a new field using an if statement in your first R00 test ( like the one John adds after the left join )..or maybe adding a WHERE statement would make things slower in the first test. Not sure you could do that test and see what happens ...otherwise I would conclude there is no need to use left joins or resident loads here...the simplest way as I see it would be to add the new/calculated fields when loading the QVD.

                                               

                                              What it is not clear to me then is the speed difference between a QVD Optimized Load and a QVD Non Optimized Load ( as if we add fields during the QVD load, it seems as per the QVD best practices slides, we break the rules of the Optimized Load ). Any ideas on this ?

                                                • Does Resident Table improve performance?
                                                  Daniel Rozental

                                                  I don't believe you can make generelatizations with this, it might be a case where doing a resident load might improve performance, maybe if the table has many fields or if the calculation is more complex, I really don't know.

                                                   

                                                  Loading your QVDs optimized has a huge difference in performance than not loading them optimized. An optimized load is, at least my interpretation, almost like a direct upload to memory where QV only copies information to memory without any processing, while not loading it optimized does cause some processing to be involved.

                                  • Does Resident Table improve performance?

                                    Hi Everyone,

                                    I am using QVD file to load data. Both examples are shown below:

                                    Example1 -

                                    Table1:

                                    Load * from abc.qvd where code=101

                                    Table2:

                                    Load * from abc.qvd where code=102

                                    Now instead of loading data from the qvd twice in the above example should i do as shown in the following example 2 -

                                    Table0:

                                    Load * from abc.qvd

                                    Table1:

                                    Load * resident Table0 where code=101

                                    Table2:

                                    Load * resident Table0 where code=102

                                     

                                    Could you tell me which of the above 2 are better for performance?

                                    I hope my question is clear...

                                    Raj

                                      • Does Resident Table improve performance?
                                        Colin Hancox

                                        I've just done a quick test and method 2 seems to be faster - 12 seconds vs 20 seconds on 1.5 million rows. This is what I would expect, since in method 2 the QVD load is optimised, whereas in method one you lose the optimisation by doing the where clauses on the QVD loads.

                                        The best way to tell though would be to test on your own data. If you go into Document Settings and set the document to generate a logfile, the logfile will contain the exact start times of each step in the load process as well as the start and finish times of the whole reload, which will show you exactly which statements are faster or slower.