Optimization - Pre Calculated Columns

[diwakarnahata]

Hi All,

Background:

I am working on optimizing an application which has 12 GB of uncompressed data.

The Server config is : 122 GB RAM, 2.5 Gz processor hosted on Amazon cloud, with 5-10 concurrent users.

Issue:

The expected performance of the application is 5-10 secs, however, most of the screens take 20-30 secs to load and refresh.

So, we have started optimizing the Data Model and the expressions of the application.

For the expression optimization there is one use case which we are working on.

Assumption - The expressions needs to be optimized and not the data volume for the expected performance of 5-10 secs.

Use Case Description:

Currently we are using a formula similar to below for calculating growth metrics:

=num(

sum({$<WeekId={'>$(=max({1}WeekId)-(vCurPrd))'}>}Sales)

/

sum({$<WeekId={'>$(=max({1}WeekId)-(vPriorPrd))  <=$(=max({1}WeekId)-(vCurPrd))'}>}Sales)

)-1,'#,##0.0%')

Question:

Is it recommended to avoid the use of set analysis and create extra columns for Current and Prior period Sales and use those columns instead? So, the formula will look similar to the below. Also we would require 6 such columns as there are 3 time period grouping required on the reports.

=num(

sum(Sales_Current_Period)

/

sum(Sales_Prior_Period)

)-1,'#,##0.0%')

Consideration:

The application is quite complex with 15 screens and a lot of set analysis already implemented.

Also, we have already considered some of the things which could be implemented from the QlikView Optimization Best Practices.pdf

Let me know your views on the above.

Regards,

Diwakar

[sunny_talwar]

It will definitely show some improvement with all the big calculations in the back end, but what could be equally effective is, if you create flags and use that in your set analysis. If you create numeric flags they won't slow down your system performance and will take less space in the application also. It could be a good middle ground between the two ends.

HTH

Best,

Sunny