Print Page | Close Window

Reports across multiple databases runs very slow

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2221
Printed Date: 19 Apr 2025 at 10:21pm


Topic: Reports across multiple databases runs very slow
Posted By: rvink
Subject: Reports across multiple databases runs very slow
Date Posted: 04 Feb 2008 at 2:17pm
Hello,
I am developing reports in Crystal XI and have some which draw data from two or three different databases. I find that these reports run extremely slowly.  I often prototype my queries using MS Access, and the same (or equivalent) query on the same databases runs MUCH faster. In one case the query in Access runs in seconds, the same report in Crystal takes over an hour.

Is there any way to make reports which run across more than one database run faster?

Thanks - Roland


Replies:
Posted By: Iago
Date Posted: 06 Mar 2008 at 2:58pm
The problem is that instead of sending one query, you are sending as many queries as there are records in your smallest table.
 
You need to make sure to use one connection.
 
It may be possible to place a view in the database that contains most of the tables you are using to point to a table in the second database.  This is not a crystal solution, and if you are not the DBA, they may think you are nuts.  Notice the Fully qualified name of the table.
 
use HelpDeskDB
Create VIEW [dbo].[vwcorrespondents]
AS
SELECT *
FROM corpdata.dbo.Correspondents
 
 
The other choice is some sort of ETL job to copy the data from one DB to the other or ideally pull all the data to a third.


Print Page | Close Window