Guideline to solve performance problems.

[rnepla83]

Hi community,

I have some performance problems related to the expression of Count({<As-of Date>} DISTINCT VISITOR_ID) in a table that have 70 Million of record for 40 column (VISITOR_ID is already Autonumbered).

Obvious that is slow to calculate expressions (because is a big table), but 35 second to open the page and to make the calculation i think is not good for the customer.

Question:

     Is possible increase the performance using some cache solutions?

     Any other solution to make faster the expressions with count distinct calculation on big table?

Really thanks,

Paolo

[felipedl]

One solution Paolo would be to aggregate older data, let's say from previous years like 2016,2015, and so on, and only let the whole picture for the year of 2017.

That would increase perfromance and let your table way slimmer too.

But it comes at a cost also, you won't be able to drill down to data, since its summarized for previous years, so I don't know if you can do that in your case.

Just a suggestion anyways.

Felipe.

[Anonymous]

What makes you think that expression is your issue?  I would think that 1 expression with what you describe wouldn't be a problem.

[rnepla83]

Really thank you Felip for your reply.

data are already aggregate as the minimum pictures.

[rnepla83]

is not a single expression,

Its a page that contain 6 Chart that (as the memory size in a sheet objet property show) are 6 second.

there is another table (that contain 8 expression using as of in set analysis of count distinct on the huge table)  that is 16 second. In ajax the total is 30-35 second in total to calculate the page.

Just to explain that is not just an expression

[vvira1316]

HI,

Rob's following utility may help. May be you want to use it to analyze your document.

Document Analyzer V3.0 Update Available

I hope it may help

BR,

Vijay

[rnepla83]

Hi Vijay

we already did that. In fact the dashboard now is 1,5 GB, before was 2,6 GB.

Thanks for the consideration.

Paolo.

[felipedl]

Don't know what type of expression you have, but instead of doing set analysis, try to incorporate what you need in the load script during transformation step, so the final application only loads data and displays it, instead of doing extreme calculations.

Other thing would be increase the "horsepower" on your server (wild guess).