Slow crystal report 2

[turtlemaster]

There are many variables to this question that I may have left out so feel free to pump me for more information. I am currently creating financial statement reports and all was well until I wrote our balance sheet. This report takes 27min + to run. I think the main problem is the sheer number of records and complexity of the report. I am pulling all of the accounting information from our ERP system which runs cetnura SQLBase and fill in the missing gaps of info I used an Access database. I am connecting to both of these databases via ODBC and I am running many, many subreports to get this data (I am unable to use SQL Expressions due to my multiple datasets, and no I cannot create this other database on the centura server instead). I am wondering if there is something i can do to generate these reports faster. Is it the way I've connected to the server (ODBC)? I montiored the CPU usage on both my machine and on the server when I run this report and the server is minimal while I'm 80-90%. Any suggestions? We will be moving to Oracle in the future do you think this will make a difference even though the problem doesn't seem to be on the server?

 

[Zaboo]

Have you selected the option to do the processing on the server?

Server-side processing is a feature that allows you to set up reports that perform the majority of their processing on the server. These reports push only relevant details to your computer, thus saving you time and memory.

If you're reporting in real-time off live data over the Web, reduce the amount of data transferred from the database server by using the Perform Grouping on Server option. With this option, much data processing is off-loaded to the database server and only a subset of data is read initially. Detail data is returned from the database only when you drill down in a report.

Note: Server-side processing works only for sorted and grouped reports that are based on SQL data sources.

Here is how to check:

first check:
File->options->database - have you selected "perform grouping on server" in the advanced section?

then check:
File->report options - have you selected "perform grouping on server"

Hope this helps - this will ensure the processing is done on the server instead of your workstation.

 

[turtlemaster]

I made the change to the File>options>database preform grouping on server but the File>report options>preform grouping on server was greyed out. This change does not seem to have effected preformance -- but thanks anyway for the input.

 

[kenhamady]

One problem is the subreports, which force the work to the client side and generating many extra queries to the database.

Also, if you link tables across platforms you give up being able to process on the server.

What is in the Access database? How linked?
How are you using the subreports? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[turtlemaster]

The access database stores a listing of our GL accounts and financial groupings so I can create our financial reports such as Income statement, related schedules and a balance sheet out of our ERP system which is based upon centura SQLBase. All of these statements must be created following GAP formatting rules so there is additional information as to which groupings are assests vs liabilities, current or non-current, etc. This allows me to create these reports as dynamically as possible. The reports do work now they just take far longer than acceptable. Right now these reports are generated mannually and take a couple of days work but at 30min for one of the reports it does not save any time. As for the subreports -- because I am using two data sources I am not allowed to use SQL expressions to get the data I need I am not sure what you know about financial statements but there are many sums pulled from different date ranges on the same report. Current Month figures next to prior month next to year to date. The way I got around this problem is to use subreports.

 

[kenhamady]

I have done several financial reports and they are quite challenging. I found that the subtotals were the most challenging, carrying forward certain categories in unique combinations.

You don't need subreports to do separate date ranges in one report. If you can get all of the records that you need in one report, you can create a condition for each date range and then sum numbers when they meet that condition. If you can use this approach, it may take more work to create the report, but it will perform better. See my FAQ in the general forum on common formulas, and look at the options for conditional totals. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[RL624]

I experienced the same problem with the use of stored procedure embedded in the report. I tried enable the server side grouping function, but it doesn't seem to have much effect on the performance. Is there any other way to go around it?

 

[turtlemaster]

I'm still working on this problem but I'm migrating this database to the server in SQLBase instead of access. Hopefully this forces the report to be grouped on the server and improves run time.

 

[RL624]

I am using MS SQL 2000 and it doesn't seem to have much effects upon the preformance. I tried enabling the server grouping function and creating parameter fields in stored procedure, but they don't seem to have much effect either.

 

[turtlemaster]

Success!!!

By creating the tables in SQLBase and migrating the data from access the grouping is preformed on the server and the processing time is reduced from 27min to just over 7sec. Only problem was working with SQLBase but I found a great tool on the internet XTGDM. It allows you to model you database tables and generate SQL Scripts to create them. VERY USEFUL and supports many dbmss.

Thank-you everyone for your suggestions.

Nick