25 Replies Latest reply: Oct 19, 2014 10:20 PM by Sergey Makushinsky RSS

    Desktop vs Web view performance

    Dennis Mornad

      Hi,

       

      I have an application with 10M rows. While, QV's performance is "almost" acceptable when I am in desktop view (running on an hp laptop with 8Gig of Ram), it really slows down when I switch to Web view. Worst yet, when I deploy the app to the production server which has 32Gig of Ram, hitting the application on the server from a web browser, performance just goes south - and one of my tables actually throws "memory exceeded" error message.

       

      So, Can someone explain what could be going on here, and what do I need to know in order to maximize the performance for the end users hitting the app on the server with their browsers? Why on my laptop the desktop view is so much faster than the web view- where the data is being hit locally on my hard disk and there is no line latency variable in the picture.

        • Re: Desktop vs Web view performance
          Jonathan Poole

          Hi Dennis - what version and browser are you using ?   IE8 is very slow with the AJAX / Zero Footprint/ full browser version.  IE10 is ok and then Chrome and firefox are ok too.   IE8 is better with the plugin which you can install/download.

           

          If we can resolve any browser issues we can move onto the data model, server specs etc...

            • Re: Desktop vs Web view performance
              Dennis Mornad

              I am running it with chrome version 37.0.2062.124 m

               

              IE9 built 1642 feels the same as chrome (with the same "exceeded memory" msg for one of my  tables).

                • Re: Desktop vs Web view performance
                  Jonathan Poole

                  You can try the IE plugin for better performance online .. it doesn't use the 'web view'. 

                   

                  The 'Web View' or AJAX can really slow down if you have wide and deep straight tables or pivot tables (many columns and many rows) ... do you have that ? 

                    • Re: Desktop vs Web view performance
                      Dennis Mornad

                      Yes, my application has many long pivot and straight tables, in fact I don't have any charts. The "exceeded memory" message appear on a pivot table that has a rather complicated calculated dimension like below:

                       

                      =Valuelist('$(=field1)', '$(=field2)','$(=field3)','$(=field4)')


                      and each row of the pivot table calculates expressions such as following:


                      =pick(

                      match(

                      Valuelist(

                      '$(=field1)', '$(=field2)', '$(=field3)','$(=field4))'

                      )

                      , '$(=field1)', '$(=field2)', '$(=field3)','$(=field4)'

                      )

                      , count({1<field1 = {'$(=field1)'}>} distinct fieldx)

                      , count({1<field2 = {'$(=field2)'}>} distinct fieldx)

                      , count({1<field3= {'$(=field3)'}>} distinct fieldx)

                      , count({1<field4= {'$(=field4)'}>} distinct fieldx)



                      What exactly is the IE plug in you're referring to please? It might be against  policy to install plug-ins , but I need to know what exactly is it.

                       

                      thanks,

                        • Re: Desktop vs Web view performance
                          Jonathan Poole

                          Its a thin client web viewer. You can enable the download on access point through a setting in the qlikview management console in the web server area.

                          • Re: Desktop vs Web view performance

                            Calculated Dimension are generally degrade the performance, consider moving all these complex expression at the scripting. May be you can consider it for long term solution.

                              • Re: Desktop vs Web view performance
                                Dennis Mornad

                                Upgraded to latest SR8. The pivot table with calculated dimensions as stated above still fails in  ajax mode and throws: "Allocated Memory Exceeded." This error message appears even if I load only a few handful of records and limit the rows of the pivot table to only 1 row. Again, desktop mode has no problem with 10M rows. Ajax falls flat on its face - IE plug-in makes no difference.

                                 

                                Clearly, I have hit a deeply hidden bug in QV and experiencing the shortfalls of the QV ajax mode.

                                  • Re: Desktop vs Web view performance
                                    Jonathan Poole

                                    When you say the following, does that mean that using the IE-Plugin results in the same error ?  same performance issue ?  .   Also when you upgraded to SR8 was that the desktop and server version ?

                                     

                                    " IE plug-in makes no difference."

                                      • Re: Desktop vs Web view performance
                                        Dennis Mornad

                                        Hi Jonathan,

                                         

                                        Thanks for sticking with my problem. Yes, I upgraded both my desktop and server to SR8 level. I then downloaded and installed the plug-in in IE9. For the most part, I do see some performance improvement with most of my tables (I also implemented the approach in the demo app you suggested above). But, one of my pivot tables just does not want to work and throws that "Allocated Memory Exceeded." error message. I am not sure if this error is data volume related- as I explained, it fails with even limited number of pivot rows and very light imported data.

                                          • Re: Desktop vs Web view performance
                                            Jonathan Dienst

                                            Dennis

                                             

                                            As a previous poster pointed out, calculated dimensions can be performance killers. When combined with the valuelist and a pick/match structure like you have, I expect that performance hit to be magnified.

                                             

                                            But it sounds to me like you may have a problem in your data structure as 10m rows is not a very large model and your hardware should cope with that. Examples would be large tables with low or zero cardinality association, which results in very large temporary tables when performing calculations, or expressions that span too many tables, as well as multiple page jumps (eg an field in table A and another field in table B with the association indirectly through table C).

                                             

                                            Perhaps you could post some more detail about your data structure and the actual expression used.

                                             

                                            HTH

                                            Jonathan

                                              • Re: Re: Desktop vs Web view performance
                                                Dennis Mornad

                                                Hi,

                                                 

                                                Attached please find a qvw application that has the data model that I use in my real app. I have used dummy data here to make it simple to follow. As you can see the amount of data loaded is very minimum). This application runs in the desktop version of QV, but if you upload it to the server and run in ajax mode, the 'Customer Pairing Report' table fails to render.

                                                 

                                                Here is brief description of the model:

                                                 

                                                Customer table: the fact table

                                                All other tables: lookup tables

                                                 

                                                Please let me know your thoughts.

                                                  • Re: Desktop vs Web view performance
                                                    Sergey Makushinsky

                                                    Hi Dennis,

                                                    It looks over complicated. I think it's possible to make it easier.

                                                    Could you describe what are you trying to calculate there? What are the requirements?

                                                     

                                                    Firs of all, I think, you can join all INFILED tables to Customer Lookup and Category lookup to subcategory lookup.

                                                    You don't need third normal form in QlikVIew

                                                    Regards,

                                                    Sergey

                                                      • Re: Desktop vs Web view performance
                                                        Dennis Mornad

                                                        Here is what the table suppose to calculate.

                                                         

                                                        If user select a customer from the Customer list box, the pivot table calculates the expressions for that customer as one of the dimensions (highlighted in green in the table). Let's say he selects Customer E. This customer has a 'Size' field value of 1M. Then, the table should calculate the expressions across all customers who have the same 'Size' field value (1M) as the second dimension (1M highlighted in yellow in the table).


                                                        The listbox titled 'Customer Pairing View' simply replaces the second dimension (Size in this case), with its current selection. So if user selects 'Ownership' from this list box, the pivot table's second dimension would be 'Public' - which is the Ownership field value of Customer E. This is to allow benchmarking a selected customer (Customer E) against all other customers who have the same Size, or the same Ownership, or the same Geography dimensions.

                                                         

                                                        The Pairs listbox determines which PAIR values to show in the pivot table - this is just to extend/limit the depth of the pivot by the user.

                                                         

                                                        Hope this is clear.

                                                          • Re: Re: Desktop vs Web view performance
                                                            Sergey Makushinsky

                                                            So, I simplified your calculations.

                                                            Check if it's correct, than delete all your calculated list boxes and table and try it on the server.

                                                             

                                                            PFA

                                                             

                                                            P.S. you have an issue with Geography because keys are not 1 and 2, bur west and east.

                                                            Nad as I told it's better to join dimension tables. It's better do not have normalised model

                                                              • Re: Desktop vs Web view performance
                                                                Dennis Mornad

                                                                Hi,

                                                                Thanks for your effort.  Looks like a working straight table is better than a not working pivot table. I still wished I could tell what is going on with the pivot table here.

                                                                 

                                                                I also went ahead and took your advise in regards to denormalization.

                                                                 

                                                                One question:

                                                                 

                                                                In your expressions you have this:

                                                                count({$<customer_name,size={$(='"' & Only(size) & '"')}>} distinct customer_name)

                                                                 

                                                                but the following expression seems to work as well:

                                                                count({$<customer_name,size={$(=size)}>} customer_name)

                                                                 

                                                                what does this syntax do exactly: {$(='"' & Only(size) & '"')} vs this one: {$(=size)}>}

                                                                  • Re: Desktop vs Web view performance
                                                                    Sergey Makushinsky

                                                                    if your size is numeric you can use  {$(=size)}, but just to avoid misunderstanding it's better to use Only(size), because size in this case is translating automatically to Only(size)

                                                                    if the value is string, like your geofield, you can't just put $(=size), you have to use quotes. So in case $(='"' & Only(size) & '"') it's just string concatenation to get {"<size_value>"}

                                                                    • Re: Desktop vs Web view performance
                                                                      Sergey Makushinsky

                                                                      I have to add that I got rid of your aggregated dimensions as well and totally rewrote your expressions. So you can try to use it like Pivot Table.

                                                                      With your pivot table it's just overcomplicated. I don't think that it's good way to develop applications. I always strive to make the expressions simple as it possible, because complicated expressions always affect the performance.

                                                                       

                                                                      Regards,

                                                                      Sergey

                                          • Re: Desktop vs Web view performance
                                            Sergey Makushinsky

                                            I can offer just a small trick, if it's acceptable

                                              • Re: Desktop vs Web view performance
                                                Dennis Mornad

                                                Small trick. Awesome. Thank you. May I ask you another question? Is it possible to sort the pivot table by one of the expressions such as #Disch. So, what I want is for each PAIR row  to sort the customer X column #Disch expression in Descending order. For example for Customer F, PAIR 1, it should display the 98 first followed by 34. And PAIR 2 should show 97 followed by 56 and so on. I tried using sorting by expression on different dims but nothing seems to work.

                                                  • Re: Desktop vs Web view performance
                                                    Sergey Makushinsky

                                                    Hi Dennis,

                                                    Go to Chart Properties\Sort

                                                    For Pair tick Numeric Value - Acs

                                                    For subcategory and subcategory_desc leave only Expression - sum(quantity) - Desc

                                                      • Re: Desktop vs Web view performance
                                                        Dennis Mornad

                                                        Hi,

                                                        I wanted to implement your "trick" in my table, but I can't figure it out. What do I need to do when I define my expressions. Where is the trick?

                                                         

                                                        Never Mind. Figured it out. That little 'space' character in the Label field of the Props Expression tab...

                                                         

                                                        Again, really appreciate your help.

                                                         

                                                        Your suggestion for sorting the pivot worked fine in the sample file, but my real application table seems to have a mind of its own when it sorts- it has a couple of more dims than the sample and the sum() expression does not do it.

                                                          • Re: Desktop vs Web view performance
                                                            Sergey Makushinsky

                                                            Hi,

                                                            It's difficult to say how to sort table if I don't see the table , but I can say that sometimes I created additional column in the dataset with concatenated fields and used it to order my tables.

                                                             

                                                            To explain trick: yes - small space to hide the name of column and to set the name I used if clause with dimensionality()=0 which means Total row.

                                                             

                                                            P.S.: if your question is answered, please point out a correct and helpful answers to help other users to see the solution