nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need advice on speeding up queries in ad hoc report

DavidGress

I'm using Actuate 11SP3.

I've created some data objects and published them to the iServer. From Information Console I'm trying to put together some Dashboards for users and to also allow them to use the data objects (both design and data file). I've noticed that for large data files and data set that even when filtered to use the tables index that the queries (using the Table gadget) time out a lot. some eventually return information but often they don't.

Has anyone ran into this issue?

I'm wondering what parameters I can adjust on the iServer to maximize the ad hoc reporting for my users.

I've had good luck putting BIRT report designs on the server and using those with the Report gadget. I was leaning toward having users define what they want and providing BIRT reports instead of them creating their own ad hoc queries.

TIA,
Dave

Find more posts tagged with

Comments

Clement Wong

On the surface, there a few items that would aid increasing performance: 
 
<ul class='bbcol decimal'><li>Indexing Data Set in .datadesign (performance will be seen in .data)</li><li>Use a Data Cube for pre-aggregation</li><li>Allocate more memory to the Java Factory/ies process/es in iServer</li><li>Increase result set buffer size in iServer configuration</li><li>Schedule and use pre-generated Data Object (.data) instead of Data Design (.datadesign)</li></ul>
 
I would also suggest that if you are current on maintenance to contact Actuate Support for additional details and for further assistance to see if there other environmental factors that could affect the performance you are experiencing.

DavidGress

Thanks for the information. I've tried a couple of these already but look into the others.

Do you think that having only 4GB of RAM on this server can be contributing to a performance problem?

Clement Wong

Having 4 GB on your iServer can possibly be a contributing factor. This will vary and depend on what you have running on this machine (Actuate and non-Actuate), the types of operations being performed, the types of reports/data/dashboards being run and viewed.

Look into increasing result set buffer size for starters, but also please if you are current on maintenance to contact Actuate Support for additional details and for further assistance to see if there other environmental factors that could affect the performance you are experiencing.

DavidGress

Under Server Configuration Templates > mdmsactuate : Properties > Viewing Service > BIRT > BIRT Content Caches > Data Set

I increased that from 8 to 16 MB but didn't notice much difference. Should I go to 32 or 64 MB on this?

Is this the correct area? Viewing Service.

Clement Wong

There are actually two locations, one for (background) generation in Factory and the other in Viewing for (async generation and viewing). 
 
Properties > Factory Service > BIRT > BIRT Content Caches > Data Set 
 Maximum result set buffer size for BIRT data object generation query 
 
The settings require an iServer restart. 
 
There are other potential tweaks such as Java Factory startup options for the various processes such as initial Java heap size, garbage collection settings, etc... 
 
Before proceeding any further, it's best to open a support case so that the team can better understand your environment and provide a much more detailed suggestion.

DavidGress

Thanks for the advice.

We've added memory and I made a few adjustments to Data Set and Factory settings. I'm noticing the system is more responsive.

We do have some help desk tickets in and are going to meet with some consultants from Actuate.