Desktop vs Web view performance

[#1]

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.

[JonnyPoole]

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."

[#3]

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.

[jonathandienst]

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

[#5]

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.

[SergeyMak]

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

[#7]

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.

[SergeyMak]

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

[#9]

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)}>}

[SergeyMak]

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>"}

[SergeyMak]

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