Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Reports across multiple databases runs very slow Post Reply Post New Topic
Author Message
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Topic: Reports across multiple databases runs very slow
    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
IP IP Logged
Iago
Groupie
Groupie
Avatar

Joined: 01 Oct 2007
Location: United States
Online Status: Offline
Posts: 52
Quote Iago Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.