The issue could be at two levels.
a) How much time database takes to complete the execution of SQLs?
It depends on how complex your queries and how much data you are
running these queries on.
You may improve this timing by getting the raw sqls for your report
calculations and do a cost analysis and creating respective indexes.
Sometimes even though you have indexed fields the db may think
differently and may not make use of the indexed fields.
Rerunning the database stats could help here. Also DBAs can do some
settings to force database to use a different plan than the default one.
This all depends on how your cost analysis of the sqls look like.
b) How much data being generated in the results set and the amount of time
it takes to transfer that data into client machine?
Infrastructure on cloud will definitely makes a difference here.
Specifically for reports. Basically the more further you are, the more
network latency and hence the more the time it takes to render the reports
with more data.
Apart from above, I would suggest:
1. Use a private thread to run these reports. That way you would not impact
regular users. Especially when your production system is also the reporting
system. You can specify the private threads on your ODBC settings.
2. If the formulas are really complicated and they are the bottleneck even
after creating indexes, instead of doing all those calculations at run
time, run cronjobs/escalations/etl jobs to pre-calculate the formulas
and populate the results into summarized tables.
And use those tables for your reporting purpose.
3. Check your ARServer, DB Server memory and temp space size to make sure
you are not breaching the limits.
Since you mentioned you saw this happening even with user tool, increasing
the midtier JVM does not help you here.
Working closely with the DBAs would fetch you a lot for these kind of
issues.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
