From a strictly database viewpoint... 1. If you need the report often, the I would create a view in your database and have your report access that. Do all of your computations in the view.
2. If you only need the data once per month or so, I would create a stored procedure that does all of the calculations and call that from your report. In short, I would not use Crystal or AR to do the calculations. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of patchsk Sent: Thursday, November 01, 2012 4:44 AM To: [email protected] Subject: Re: ARS 7604 Custom App and Reporting Issues ** 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. _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
