August 15, 2009 at 10:18 am
Hi All
I'm looking for opinions on what is the most efficient method to use for pulling data into a Crystal Report template from an MS SQL 2005 database.
In the past I have used the following;
1. Use stored procedures for the data source
2. Create a custom view to use as the datasource and filter in Crystal
3. Create a SQL select statement in Crystral and use it as the data source
My guess is that SQL Server is more efficient at executing the select query so I tend to use Stored Procedures as my method of choice.
I realize some may view this as off topic but I am looking for the SQL Developers view not the Crystal Reports developers view.
Thanks in advance for the opinions.
August 15, 2009 at 11:32 am
Crystal Reports is a pain.
That said, I have struggled a lot in the past to find myself the best way to feed it the data to display.
If you use stored procedures directly you will find that Crystal Reports will execute the procedure twice: once to determine parameters and resultset structure and once to retrieve the data. Obviously this is not acceptable, especially with complex procedures.
If you use tables and views as a source and join them in the "database expert" (wonder who on earth gave it that name... nothing would be further from reality) you will find messy queries around at times. CR could decide to extract some data first and then issue one query for each row during data fetch. A nightmare.
What I still think is the best way to go is this:
1) create a stored procedure that does the real work
2) pipe the results to a dedicated table (I use a separate schema for this, something like "creports"), one for each stored procedure.
3) provide the staging table with an uniqueidentifyer column and push the data in the staging table along with a NEWID you calculated in the procedure.
4) make the stored procedure return the NEWID you calculated
5) pass the NEWID to crystal reports as a parameter and make it the filter for the staging table
6) when the report is closed, empty the staging table. To be sure you don't have garbage data around, create a job, scheduled nightly, to truncate all staging tables.
It's a bit of work to set up, but with this technique I cut down reports execution times a lot.
Hope this helps
Gianluca
-- Gianluca Sartori
August 16, 2009 at 9:50 am
Thanks for responding.
Its an interesting solution. The problem I am working on currently will collect data nightly from several clients and produce and email reports programatically on a daily basis to each client based on their daily and historical data. I may have to give your method a try and check some response times compared to having a stored procedure for each of the 8 sub-reports inthe template that use a common set of parameters.
Thanks again!
August 18, 2009 at 2:33 am
Gianluca
I have used the option u mentioned and it has worked for me when we used crystal reports. One point to note is the management of T-Logs. If the report was called very frequently and had a large set of data the T-logs would increase in size.
"Keep Trying"
August 18, 2009 at 3:19 am
In fact this is one of the weaknesses of this strategy.
There's no technical solution to this issue: when a user wants to display a 1 mln rows report, we have to take off our tech geek clothes and talk to the user. Is it really necessary to work on such big datasets?
Is a 2000 pages report useful in any way? I don't think so. It's impossible to read, impossible to print, ergo useless.
For frequent hits you're right: the log grows. I have found that if the number of rows is reasonable, also tlog growth is reasonable.
I have to admit that I don't have thousands of users running the same 5000 rows report every 2 seconds, but I seem to have found a sort of balance that satisfies both me and the users in the current environment load. Will it scale well in the future with a higher load? Time will tell...
One possible solution could be setting up the reports staging tables in a different database, with simple recovery model. Up to now it has not been an issue, but, who knows what's next?
Thank you for pointing this out, I'll be sure to check it.
-- Gianluca Sartori
August 19, 2009 at 12:44 am
That's a good point about having staging tables in a database with simple recovery model. Thanks
A 20 page report is useless to the customer but what if the user wants to turn to the next page. Implementing some sort of pagination involves bringing all the required data into the staging tables.
"Keep Trying"
November 16, 2009 at 3:05 pm
I having the same behavior of execution sproc twice on Crystal 9 (first time with blank parameters and second time with passed parameters). Crystal 10 calls the sproc once with parameters passed.
Question:
Is there a fix for Crystal 9 to call sproc only once?
November 17, 2009 at 1:10 am
No there isn't. The same applies to Crystal XI, just to be clear. The reporting engine has to know which columns will be returned by the procedure, so it makes a call with empty (or, even worse, fixed) parameters to validate the data source.
My suggestion is still populating staging tables from stored procedures and linking the staging tables from CR. In one of my previous posts I explained the whole technique to use staging tables: you could try that way.
Regards
Gianluca
-- Gianluca Sartori
December 29, 2009 at 12:24 am
Gianluca Sartori (11/17/2009)
No there isn't. The same applies to Crystal XI, just to be clear. The reporting engine has to know which columns will be returned by the procedure, so it makes a call with empty (or, even worse, fixed) parameters to validate the data source.My suggestion is still populating staging tables from stored procedures and linking the staging tables from CR. In one of my previous posts I explained the whole technique to use staging tables: you could try that way.
Regards
Gianluca
Hi Gianluca
Have you tried setting up a main report / subreport, with the parameters collected by the main report then passed to the subreport which calls the stored procedure with them? In this scenario the main report is used solely for collecting the parameters. This works very well against large tables.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 29, 2009 at 12:55 am
lobbymuncher (12/29/2009)
Gianluca Sartori (11/17/2009)
No there isn't. The same applies to Crystal XI, just to be clear. The reporting engine has to know which columns will be returned by the procedure, so it makes a call with empty (or, even worse, fixed) parameters to validate the data source.My suggestion is still populating staging tables from stored procedures and linking the staging tables from CR. In one of my previous posts I explained the whole technique to use staging tables: you could try that way.
Regards
Gianluca
Hi Gianluca
Have you tried setting up a main report / subreport, with the parameters collected by the main report then passed to the subreport which calls the stored procedure with them? In this scenario the main report is used solely for collecting the parameters. This works very well against large tables.
No, I haven't tried yet. I'll check out your suggestion and come back with some news. Thanks for the advice.
-- Gianluca Sartori
December 30, 2009 at 8:36 am
My 2 bits are as follows
1. Use views/tables
2. Join using the So Called database expert
3. Decide what your rows will be filtered by
- Now create Formula Fields which you will be filtering by
- Prefix each field with the line
BeforeReadingRecords;
- Now set your Selection Formulas-Rows to use these formula fields in the filter
eg
{tablename.Field1}={@cFormulaField1} and {tablename.Field2}={@cFormulaField2}
To see why this is faster view the SQL crystal generates under 'Database-Show Sql'. Notice Sql has a where clause and how the Row filter has been embeded into the sql. Usually when in design mode the Formula fields will evaluate to 0 blank etc.
Without the BeforeReadingRecords; the sql passed in ommits these filters and crystal then tries to filter on a record by record basis at run time and is extremely slow.
Hope this helps.
January 20, 2010 at 10:43 am
Gianluca Sartori (8/18/2009)
In fact this is one of the weaknesses of this strategy.There's no technical solution to this issue: when a user wants to display a 1 mln rows report, we have to take off our tech geek clothes and talk to the user. Is it really necessary to work on such big datasets?
Is a 2000 pages report useful in any way? I don't think so. It's impossible to read, impossible to print, ergo useless.
For frequent hits you're right: the log grows. I have found that if the number of rows is reasonable, also tlog growth is reasonable.
I have to admit that I don't have thousands of users running the same 5000 rows report every 2 seconds, but I seem to have found a sort of balance that satisfies both me and the users in the current environment load. Will it scale well in the future with a higher load? Time will tell...
One possible solution could be setting up the reports staging tables in a different database, with simple recovery model. Up to now it has not been an issue, but, who knows what's next?
Thank you for pointing this out, I'll be sure to check it.
I realize that this is not the case for all industries but in the accounting world there is nmo such thing as a useless report that is useless because of it consisting of a very large number of pages, even a 2,000 page report. The best exampe of this is a consolidated GL (General Ledger) that involves a large portfolio of accounting entities that share a single Chart Of Accounts, something common in Proeprty Management. And so don't too quickly dismiss the need to produce a report consisting of this many pages and labeleing it as useless for there are some industries where not only will this not be useless but may be required (for audting & historical purposes).
Kindest Regards,
Just say No to Facebook!January 20, 2010 at 10:45 am
@Gianluca Sartori
I did have a question about this probelm with Crystal Reports; do you know for certain which versions of Crystal (starting with 9 and going up) suffer from this problem?
Do you know if it has been fixed in any recent version of Crystal?
Is there any substitue for Crystal, a reporting tool that still provides the same extensive level of report design options as Crystal but does not handle Stored Procedures in this same screwey manner?
Lastly, are you familiar with SQL Server Reporting Services and if so do you know how it deals with SP's for a data source?
Thanks
Kindest Regards,
Just say No to Facebook!January 20, 2010 at 11:29 am
do you know for certain which versions of Crystal (starting with 9 and going up) suffer from this problem?
I know for sure versions 8.5, 9, 10 and XI behave this way.
Do you know if it has been fixed in any recent version of Crystal?
Nope. I'm working (very little) with XI and it's the last version I've worked with. As far as I know, all newer versions, including those for .NET, are not very different from XI and I would expect them to behave in the same exact way.
Is there any substitue for Crystal, a reporting tool that still provides the same extensive level of report design options as Crystal but does not handle Stored Procedures in this same screwey manner?
Probably yes, but I don't know. I'm not a reporting guy, it's a secondary activity for me.
are you familiar with SQL Server Reporting Services and if so do you know how it deals with SP's for a data source?
Same applies here. Reporting is not my focus.
Hope this helps.
Gianluca
-- Gianluca Sartori
January 20, 2010 at 11:37 am
Just an update to this thread which I started as a general request for opinions on how best to pull info into Crystal.
I've since had to create several complex reports for clients who wanted a "Dashboard" like single page report showing 12 distinct KPIs of their business. Each of their multiple locations required a daily copy with data for that location. To implement this I made each KPI a subreport and since each KPIs data was relativly unrelated to the others I wrote a stored procedure to pull the data for each. The top level report requires 2 parameters (location and date) which are passed on to the subreports.
It's a little crowded on the page but the process works great. I've automated the entire process through a C# app. so that each location (almost 100) has a report produced and emailed to a list of subscribers on a daily basis.
While this may not have been the most efficient way of pulling the data it seems to work reliably and fairly quickly.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply