Crystal Reports versus MS-Access (Jet Engine) concerning performance issues
Posted on 2007-07-30
I am currently working in an environment where there is quite a bit of SQL writing going on for both on the fly reports (special requests) and daily/weekly/monthly reports. Our database is Oracle, and is stored off-site.
Here is the current state...
1. We are working with a group of reports that have been developed using Crystal Reports by off-site personnel. These reports cover approximately 40% of our reporting needs. The were implemented several months ago and new report requests have not been published via Crystal Reports (as they are developed off-site). These reports run at a very satisfactory level of speed and quality. There is a constant use of different Crystal Reports throughout a typical day.
2. We have other reports that are created in multiple MS-Access databases based on the type of reports needed. This covers approximately 30% of our reporting needs. We have approximately 5-7 MS-Access DB's which use the microsoft ODBC driver to link to our Oracle DB. People all access these databases, stored on a Network Drive, the databases are not split, and there are about 8-12 persons using MS-Access at any given time.
3. We also have reports that are special needs which are run through SQL Developer for Oracle. This covers 30% of our reporting needs. Runs brilliantly quick compared to MS-Access, and I use this for research purposes. There are approximately three people running random SQL's at any given time using this program.
With option two we experience significant query delays. There are also many complaints with network speed, which I added in case it is related. I would like to know what some of the causes are for the extreme lag in speed via the MS-Access Jet Engine versus options 1 and 3 where the speed is a world of difference. I suspect that many of the network slow downs are attributed to the use of the MS-Access DB for queriying/reporting, but would like to have a professional opinion on the current state of reporting affairs.
The option that I am looking to go towards is requiring our reports to be sent over to Crystal Reports to be published for use, rather than the multiple MS-Access DB's, and use of SQL Developer on multiple machines. However, there is a cost associated with this approach, but with some sound justification it will be approved.