Print Page | Close Window

Speeding up report with command

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22916
Printed Date: 06 May 2024 at 3:03pm


Topic: Speeding up report with command
Posted By: shabbaranks
Subject: Speeding up report with command
Date 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


Replies:
Posted By: DBlank
Date 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.

Posted By: kevlray
Date 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.

Posted By: shabbaranks
Date 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.

Posted By: kevlray
Date 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.


Print Page | Close Window