Crystal Reports versus MS-Access (Jet Engine) concerning performance issues

Posted on 2007-07-30
Last Modified: 2010-08-05
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.
Question by:Derek_Indianapolis
    LVL 17

    Accepted Solution

    I would say that your problem is NETWORK traffic. Accessing DB's via network is not the fastest way to get data.

    I would also say ..that you need to centralize your databases and table onto a SINGLE Server if possible...if least consolidate your databases as much as possible.

    ETL the Access DBs into ORACLE and report from ORACLE instead of ACCESS.

    Be sure to double check all of your Crystal Reports to make sure that datatables are linked properly...and the report itself is optimized.

    Do not allow Crystal Reports to be developed using separate DB's and or Servers. If data is scattered , then consolidated it prior to reporting. OR youcan make use of VIEWS that will pull the data together in a SINGLE server.

    hope it helps.

    LVL 92

    Assisted Solution

    by:Patrick Matthews
    Hello Derek_Indianapolis,

    In addition to the network latency, it sounds like you are not using sprocs on the Oracle
    DB server or pass-through queries.  If you use ODBC to link to Oracle and then use
    regular queries, Access has to pull down potentially enormous amounts of data to
    process the SQL; you are probably better off letting the Oracle DB server do that heavy



    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now