Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-07-30
2
Medium Priority
?
902 Views
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.
0
Comment
Question by:Derek_Indianapolis
2 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 1000 total points
ID: 19596052
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 not...at 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.

MIkeV
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 19596109
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
lifting.

Regards,

Patrick
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

580 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