Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Speeding up report with command Post Reply Post New Topic
Author Message
shabbaranks
Groupie
Groupie


Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Quote shabbaranks Replybullet Topic: Speeding up report with command
    Posted: 22 Dec 2020 at 11:37pm
Hi,
Ive created a command which queries a separate database but when I run the report it takes ages. The command contains a table reference to a separate database so its not doing anything fancy and then I just link the two databases fields within the links.

When I pull the field from the second database I used an IF statement within a formula (but even if I just add the record its still slow).

Any ideas how to speed it up?
Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Dec 2020 at 9:12am
not entirely understanding the description but in general unless you explicitly enforce the join between two 'tables' in crystal it only becomes enforced if you use a field (in any capacity) from both tables (or ends of daisy-chained tables). So if you drag and drop a field onto the canvas you can see your data set wildly change it is because the join is being enforced. If there is no join it will act like a cartesian join/product which can make your data set huge and also make the report run really slow. Also sub-reports and page numbering can also greatly impact performance.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 28 Dec 2020 at 4:32am
So have you run the query in a separate database query program (not sure what 'brand' of database you are using).  If the database is MS-SQL, you can use the SQL Server Management Studio.  We use TOAD ($$) for a lot of our databases.

That way you can determine (roughly) how long it should take to return the necessary data.

IP IP Logged
shabbaranks
Groupie
Groupie


Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Quote shabbaranks Replybullet Posted: 28 Dec 2020 at 7:53am
Thanks guys - the queries work fine independently its the fact that Im querying 2 separate databases but are joined by an inner join and this is causing some slowness effectively joining using the crystal report rather than SQL to create the report.

Unless I am mistaken I cant run the query within SQL Management studio given that only links\queries a single databases\table unless I create a join.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 28 Dec 2020 at 8:33am
It is possible to connect to a separate database in SQL Management Studio (SSMS), but it may require a DBA to give rights to certain functions.  Look up OpenQuery function.
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.015 seconds.