22 Replies Latest reply: Aug 19, 2014 10:52 AM by Gabriela Morales RSS

    Qlik Sense takes almost three times longer to reload than QlikView

    Gabriela Morales

      Hello,

       

      I have a quite complex script I'm running on QlikSense and I noticed that it takes longer to reload (almost three times more) than in QlikView, I'm reloading both on the same machine.

      In QlikView I don't generate a log file since it is a long script It took a long time to save the file. I tried to find a way to disable the option to generate the log file in QlikSense but I couldn't find any, is this even possible?

       

      Thank you in advance

        • Re: Qliksense takes almost tree times more to reload than Qlikview
          Sean Donovan

          Hi Gabriela,

           

          Sounds like a unique case.  Typically the scripts that I run in Qlik Sense take half the time for me when compared to QlikView.  Which commands in the edit script are you using?

            • Re: Qliksense takes almost tree times more to reload than Qlikview
              Bill Markham

              For me QlikSense reloads take about 50% longer than QlikView, both reload scripts identical and run on the same machine against the same data.

               

              Most of the reload is loading from QVD's and I did notice a message about the QVD's being Qlikview 11.2 ones.  Do the QVD's need converting maybe ?

                • Re: Qliksense takes almost tree times more to reload than Qlikview
                  Michael Tarallo

                  Hi Bill - have these times been calculated a few times after conversion?

                   

                  I know that the first time a QlikView QVD is brought into (read in) Sense - it converts it to the new optimized format that Sense uses for the app, but this only happens once. Subsequential reloads of the new format (if you created a new QVD with Sense - coming from source)  not QlkView - should then be much quicker. Can you verify this for us?

                   

                  Thanks

                   

                  Mike

                    • Re: Qliksense takes almost tree times more to reload than Qlikview
                      Bill Markham

                      Michael

                       

                      The QVD's are being generated using QlikView 11.2 Publisher every hour, so even if they are converted to QlikSense format they will be regularly overwritten by QlikView 11.2 format QVD's.

                       

                      I was checking out the potential viability of QV 11.2 Publisher distributing QVD's to people to then use for QlikSense Self Service BI,when I noticed the 50% performance hit.

                       

                      I'll double check next Monday the timings of the first & subsequent QlikSense Loads.  The 50% performance hit is annoying but not a showstopper.

                       

                      Out of interest, can QV 11.2 read QVD's that have been converted to QlikSense format ?

                       

                      It could also be useful if QV 11.2 had an option to create QVD's in QlikSense optimised format.  Or an exe or something that could be called to convert QVD's.

                       

                      If we do decide to exploit QlikSense it shall certainly be in parallel use with QlikView for at least few years.

                       

                       

                      Best Regards,     Bill

                        • Re: Qliksense takes almost tree times more to reload than Qlikview
                          Michael Tarallo

                          Hi Bill -

                           

                          No - QlikView cannot read QVDs created by Sense or once converted by Sense. Your suggestion about allowing QlikView to create an optimized format for Sense is a strong possibility for future releases of QlikView. I will run this by PM to see what they say and let you know. In regards to Sense and QlikView together, I agree. I have witnessed these conversations where it makes sense to have both products for different organization and knowledge worker needs. One is not designed to replace the other, they can compliment each other and provide choices to organizations on how they wish to deploy BI solutions.

                           

                          If I hear something next week, I will update this post.

                           

                          Take Care,

                           

                          Mike

                            • Re: Re: Qliksense takes almost tree times more to reload than Qlikview
                              Bill Markham

                              Mike

                               

                              QlikView           reload takes consistently    10 secs          Tested 4 times

                               

                              QlikSense         reload takes consistently    16 secs          Tested 4 times

                               

                              Every time I run QlikSense reloads it issues messages like this :

                               

                              Flight.Facts << RSMS.RSMSLIVE.Flight.Facts (old QVD (row based) optimized) QVD file generated by version 11.2 or lower was detected. Use STORE INTO to optimize and columnize this file.

                              Lines fetched: 64,703

                               

                              QlikView reads the QVD's ok after QlikSense has loaded them, so I reckon that the first time a QlikView QVD is brought into (read in) QlikSense does not convert it to the new optimized format that QlikSense uses.


                              It appears that one needs to explicitly convert them with a QlikSense STORE INTO.  [I have not tested this].



                              What do you reckon ?




                              Best Regards,     Bill


                                • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                  Michael Tarallo

                                  Hi Bill - that very may be the case, I know "some things" change from time to time, from when they initially brief us, to when the product comes out. Would you mind testing that STORE command to optimize the QVD for Sense and then perform the reload to note the timings?

                                   

                                  Thanks

                                   

                                  Mike

                                  • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                    Alexander Karlsson

                                    Hey Bill,

                                     

                                    Yeah, the internal format has changed for QVDs between QlikView and Qlik Sense.

                                    However to support portability between the two products Sense can read QVD-files that has been created by QlikView using the store command or through external tools.

                                     

                                    If you load data from a QVD in Sense that has been created with QlikView you will notice a slightly longer reload time as the engine will have to convert that data into the internal format for Sense. Executing a STORE command on a table in Sense will create a qvd with the new internal format.

                                     

                                    If you would create the same QVD with the two different products you would see the same reload times if not faster in Sense.

                                      • Re: Re: Qlik Sense takes almost three times longer to reload than QlikView
                                        Bill Markham

                                        Mike & Alexander

                                         

                                        I converted the QV 11.2 QVD's to QlikSense format with  LOAD *... STORE INTO,,,,, DROP TABLE.....

                                         

                                        Then ran the script that I had bench marked loading from QV 11.2 QVD's as :

                                        • QlikView                reload takes consistently    10 secs          Tested 4 times
                                        • QlikSense              reload takes consistently    16 secs          Tested 4 times

                                         

                                        I then test ran the QlikSense load script loading from QlikSense QVD's 5 times and WTF !

                                        • 59 secs
                                        • 51 secs
                                        • 50 secs
                                        • 49 secs
                                        • 50 secs

                                         

                                        So this bench marks QlikSense loading from QlikSense QVD's as :

                                        • 5 times slower than QV 11.2 loading from QV 11.2 QVD's
                                        • 3 times slower than QlikSense loading from QV 11.2 QVD's

                                         

                                        I do hope it is me doing something stupid, but somehow I do not think so.

                                         

                                        Any suggestions ?

                                         

                                         

                                        This is a bit of the script that really seems sluggish with QlikSense, especially the RESIDENT LOAD :

                                         

                                        ///////////////////////////////////////////////////////////////////////////

                                        Temp.EZY.OTP:

                                        load

                                          1 as [EZY OTP Flag]

                                        autogenerate(1);

                                        /////////////////////////////////////////////////////////////////////////

                                         

                                         

                                        //////////////////////////////////////////////////////////////////////////////////////////

                                        Temp01Flights :

                                        ////////////////////////////////////////////////////////////////////////////////////////

                                        LOAD

                                          [Data Loaded Date],

                                          [Data Loaded Time],

                                          [Count] ,

                                          [Flight ID] ,

                                          [Airport IATA Code],

                                          [Airport ICAO Code],

                                          [Airport] ,

                                          [Airport Airline Date Key] ,

                                          [Arrival Flight] ,

                                          [Departure Flight] ,

                                          [Actual Turn Flag],

                                          [MA Ground Time Minutes],

                                          [Scheduled Flag],

                                          [Cancelled Flag],

                                          [Ferry Diversion Flag],

                                          [Arrival Flag],

                                          [Departed Flag],

                                          [Origin Airport IATA Code],

                                          [Origin Airport],

                                          [Destination Airport IATA Code],

                                          [Destination Airport],

                                          [Departure Staff TCO],

                                          [Departure Gate],

                                          //////////////////// EZY specific ////////////////////////////

                                          [Delay over 15],

                                          [Delay over 15 within MGT Flag],

                                          [Delay over 15 out of MGT Flag],

                                          [Door Closed STD-3 Flag],

                                          [Turn Performace ETD+3 Flag],

                                          [Turn Performace ETD+3 Count],     

                                          [EZY OTP Target],

                                          [EZY TP Target],

                                          [EZY DCT Target],  

                                          [Scheduled Arrival Date LT],

                                          [Scheduled Arrival Time LT],

                                          [Scheduled Departure Date LT],

                                          [Scheduled Departure Time LT], 

                                          [Actual Arrival Date LT],

                                          [Actual Arrival Time LT],

                                          [Actual Departure Date LT],

                                          [Actual Departure Time LT],

                                          [Estimated Arrival Date LT],

                                          [Estimated Arrival Time LT],

                                          [Actual Ground Time Minutes],

                                          [Delayed Departure Flag],

                                          [EZY First Wave Flag],

                                          [Flight Delay Minutes],

                                          [EZY Territory] as [Territory],

                                          [Business Unit Code] as BU,

                                          [EZY OTP Flag]

                                        FROM $(vRSMSFolderQVD)RSMS.RSMSLIVE.Flights.qvd (qvd)

                                        where exists ( [EZY OTP Flag] )

                                        ;

                                        ///////////////////////////////////////////////////////////////////////////

                                        drop table Temp.EZY.OTP ;

                                        //drop field [EZY OTP Flag];

                                        ///////////////////////////////////////////////////////////////////////////

                                        NoConcatenate Flights :

                                        ///////////////////////////////////////////////////////////////////////////

                                        load

                                          *

                                        resident Temp01Flights

                                        where

                                          MonthName (  [Scheduled Departure Date LT] ) = MonthName(Today())

                                          or

                                          MonthName (  [Scheduled Departure Date LT] ) = MonthName ( AddMonths ( Today() , -1 ) )

                                        ;

                                        ///////////////////////////////////////////////////////////////////////////

                                        drop table Temp01Flights ;

                                        ///////////////////////////////////////////////////////////////////////////

                                          • Re: Re: Re: Qlik Sense takes almost three times longer to reload than QlikView
                                            Bill Markham

                                            Yup, that RESIDENT LOAD now takes 30 secs :

                                             

                                            ///////////////////////////////////////////////////////////////////////////

                                            let vNow = now() ;

                                            trace $(vNow) ;

                                            ///////////////////////////////////////////////////////////////////////////

                                            NoConcatenate Flights :

                                            ///////////////////////////////////////////////////////////////////////////

                                            load

                                              *

                                            resident Temp01Flights

                                            where

                                              MonthName (  [Scheduled Departure Date LT] ) = MonthName(Today())

                                              or

                                              MonthName (  [Scheduled Departure Date LT] ) = MonthName ( AddMonths ( Today() , -1 ) )

                                            ;

                                            ///////////////////////////////////////////////////////////////////////////

                                            drop table Temp01Flights ;

                                            ///////////////////////////////////////////////////////////////////////////

                                            ///////////////////////////////////////////////////////////////////////////

                                            let vNow = now() ;

                                            trace $(vNow) ;

                                            ///////////////////////////////////////////////////////////////////////////

                                            exit script ;

                                            ///////////////////////////////////////////////////////////////////////////

                                             

                                             

                                            Started loading data

                                            Variables << Variables Lines fetched: 22 Calendar << Generic.Calendar Lines fetched: 62 18 Aug 2014 18:39:59 Temp.EZY.OTP << AUTOGENERATE(1) Lines fetched: 1 18 Aug 2014 18:39:59 Temp01Flights << RSMS.RSMSLIVE.Flights (QVD columnar optimized)

                                            Lines fetched: 4,526,720

                                            18 Aug 2014 18:40:11

                                            18 Aug 2014 18:40:14 Flights << Temp01Flights

                                            Lines fetched: 229,274

                                            18 Aug 2014 18:40:44

                                            App saved

                                            Finished successfully

                                            0 forced error(s)

                                            0 synthetic key(s)

                                              • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                                Torben Seebach

                                                Bill,

                                                 

                                                My test shows the same results. Loaded 20 mio rows on the same computer with Sense and QlikView.

                                                QlikView: 6,00000014529 seconds

                                                Sense: 14,00000012945 seconds

                                                In both loads, I have a variance of about a 1 plus/minus.

                                                 

                                                Since Sense autosaves, I've done my timing using this script, which is accurate beyond nanoseconds:

                                                 

                                                Let _StartTime = Now();

                                                Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
                                                Trace $(_CalendarName) created in: $(_TotalTime) seconds;

                                                Loading the same data, just generated by Sense took just 6,99999975041 seconds in Sense.

                                                 

                                                QlikView however cannot read the QVD that Sense has generated!

                                                 

                                                This is really bad for hybrid (QlikView+Sense) deployments. It will basically force us to load every table twice.

                                                  • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                                    Alexander Karlsson

                                                    Torben Seebach wrote:

                                                     

                                                    QlikView however cannot read the QVD that Sense has generated!

                                                     

                                                    This is really bad for hybrid (QlikView+Sense) deployments. It will basically force us to load every table twice.

                                                    Yeah for now the recommendation, once the Sense server goes GA that is, for customers running both products side by side is to generate their QVD-layer through QlikView. However down the line it would be preferable to have both products share the same internal format that is columnar instead of row based.

                                                • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                                  Gabriela Morales

                                                  I made the same test, I converted the QVD's using the 'store table into..' in Sense, then I ran the script and it was almost 10 times slower than QlikView

                                                   

                                                  Sense (using Sense QVD's): 5hr 53min

                                                  Sense (using QlikView QVD's): 1hr 32min

                                                  QlikView: 38min

                                                   

                                                  Are we doing something wrong?

                                                • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                                  Torben Seebach

                                                  Alexander and Michael, maybe should tell PM to stop calling the Sense QVDs for QVDs, because if they cannot be read by a program that reads QVDs (QlikView)  then they are not QVDs. That is like calling XLSX as XLS.

                                                   

                                                  It would make sense that Sense could write a new optimized format called QVDX or something, but also write out standard QVDs so they could be loaded by QlikView. Hybrid deployments are going to be a huge mess if this is not sorted - maybe we should start using txt files again.

                                    • Re: Qlik Sense takes almost three times longer to reload than QlikView
                                      Rob Wunderlich

                                      " I don't generate a log file since it is a long script It took a long time to save the file"

                                       

                                      I'm curious about that observation. Are you saying that using a document log added a measurable increase to load time? How much?

                                       

                                      -Rob