Print Page | Close Window

Connecting Database Tables and Database Views

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
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=16323
Printed Date: 07 May 2024 at 2:29am


Topic: Connecting Database Tables and Database Views
Posted By: AlexanderAPWM
Subject: Connecting Database Tables and Database Views
Date Posted: 30 Apr 2012 at 10:09am
Hello, I have an issue with my refresh speed for a report.

In making this report i had to add the certain database tables and also database views. As soon as i dropped a field from the Views into my report the refresh goes 10 records at a time.  Since i have linked two sources i believe it has slowed my refresh speed to a crawl. Is there a way to speed the refresh rate up? Are the linking and joins the issue?


Replies:
Posted By: hilfy
Date Posted: 01 May 2012 at 3:03pm
The issue is probably the view itself.  A view is a SQL statement that can be used like a table.  The database runs the SQL in the view at the time of the refresh.  Generally, views are not indexed so when you link to them from a table, there is no index available to speed up the processing.  I would look at a couple of things:
 
1.  Make sure that the SQL in the view is optimized so that it runs fast.
 
2.  If that doesn't work, think about creating a view or command that includes the tables that your using in your report - so you have a single SQL statement that uses all tables and doesn't connect to the view.
 
Also, make sure that you  have the following options turned on for your report:
 
Use indexes or server for speed
Perform Grouping on server
 
These setting will  push the processing to the database server which is usually much faster than having Crystal download all of the data and to the processing in memory.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: DBlank
Date Posted: 02 May 2012 at 3:57am
This may not be applicable but just in case...
be aware that in crystal when you join tables (or views) the join is not automatically enforced so your data set is not necesarily complete (or what you would expect based on the join).
The join will become enforced (altering the data set) if you:
- manually enforced it inside the join properties
- or as soon as you use at least one field from both tables in the join. (It can be used in any way in the report not just for displayed on the canvas)
 
A symptom/result of the second type of enforcement can be described as, in the preview mode, when a user adds a field to the table the report size either explodes or shrinks and summary values jump all over the place.

Posted By: AlexanderAPWM
Date Posted: 03 May 2012 at 11:20am
Hello,

So after reading your suggestions I moved the Views around in the database expert and linked it to a different Table and the report refreshed in 10 minutes as opposed to 30 minutes. Then i gave one of the Views linked fields a left outer join and now it refreshes in 17 seconds. I am still confused as to why that worked, but I am not complaining. I did go into the options and check-marked "Perform Grouping on Server" but I haven't noticed a difference yet.

Thanks for the help,

Much appreciated!


Print Page | Close Window