performance big app huge data set 500 million records

[#1]

Hi everyone

We have a huge app with about 15 tables and 500 million records. Our server configuration is 256gb with 32 cores of cpu power 2.6ghz.

we are running QV11 SR1.

QVD Generation & App update has been optimized. however user interaction with the app extremely slow and few charts which has large amount of indicators is very slow once it is clicked. sometime it takes more then 10 minutes after a click.

It seems like large amount of time is taken once user starts clicking around, hence calculation is slowing it down in terms of interaction. (i fell, but i could be wrong)

1. What can be done to improve performance.
2. What can QV handle in terms of data set.
3. What kind of server cpu & memory is idle to handle this kind of application.

[iktrayanov]

You can try to pre-load the document. Also some pre-agregations might help.

[sundarakumar]

Hi,

You can use conditional expressions on expression tab, like if you have four regions you can use the condition if(getselectedcount(region)=1,1,0) and display the user the data only when he selects one region and we can make the chart to display "Please select one region" if nothing is selected. This will filter out approximately one third of data and so the calculation will speed up. Use such conditions where u use heavy calculations.

-Sundar

[#4 · Accepted solution]

Hi MD,

Performance issues can be for different reasons , because there are several things that affect the final performance of the application .

Basically it's for CPU, memory , and application design , plus server architecture for large deployments.

From what I see CPU and RAM should not have problems because for that size, the eye should work fine.

So it occurs to me that may be design issues to what you commented some good practices you could check on your model :

- Check that you dont have synthetic keys or circular references in the model

- If you have multiple fact tables , and these are linked by linked tables, assesses the ability to concatenate them and go to a star model

- Do not use as key fields that are text fields such concatenations , instead it is better to always use autonumber

- Try to release the objects most of the conditions in the expressions , adding flags in the  script and then facilitating the objetct reload

- Checks are not using key fields as a dimension or in expressions

- Limits the representation of tables and avoid many unnecessary records show , add a condition to limit calculation for example 20,000 rows

- Try not to hold many active objects at once , use minimized objects and displays only a few items at a time

- Eliminates model fields that are not analyzed

- Removes records that are not analyzed

- Evaluate the possibility of cut the document and keep two documents, one with the historical information and other with actual information

- Evaluate the possibility of grouping the fact tables unless necessary access level of detail

- Evaluate the extent of normalization / denormalization tables

- To avoid overloading the first time access to the AccessPoint, you enable the preload on the console to the document

- Lower the level of detail of the logs from the console and the frequency that the logs are created. Setting it eg in 1 hour

These are some practices that will help you win in your application performance. Check this list and let me know if you have more questions.

Regards.

[#5]

Thanks Guzman.

1. I don't have any synthetic key in the data model.
2. There seem to be 4 fact tables with millions of records in each and they are connecting via primary key between the tables. I am inheriting this app and has complex coding, hence I wonder If will be able to merge these 4 tables into one,

I was contemplating of increasing cpu and memory. do you think 256gb memory & 32 cores of cpu is adequate?

I will check mark preload on QMC

I was also thinking of decreasing compression as I read it could improve performance, do you think it is good idea.

[#6]

please have look at Following Thread

*** 6 Weeks in to QV Development, 30 Million Records QV Document and Help Needed!!! ****

[#7]

and also Try use less join

max use of Apply map

[#8]

whats should ideal cpu and memory should be for this kind of data set.

[#9]

Hi,

The sum of rows of all your fact tables is 500 millions of rows?

The decrease the compression level can be a good idea to help improve the performance.

I have some other questions, document at some point worked correctly? From where or from what changes in structure you perceive lower performance? You are doing a binary load from other qvw?

Ever you can change the data model, and concatenate the fact tables, as a last alternative you can do it in the end of the script when all the transformation ended. And  some changes to maintain the correct information if it is needed.

Regards.