Kbase P115336: Slow performance with a Crystal report that connects to more than one database and/or uses Commands
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/01/2010 |
|
Status: Verified
SYMPTOM(s):
Slow performance with a Crystal report that connects to more than one database and/or uses Commands
Slow performance when displaying a Crystal report
During report development, the following message is displayed by Crystal Reports:
More than one datasource or a stored procedure has been used in this report.
Please make sure that no SQL Expression is added and no server-side group-by is performed.
Checking "Show SQL Query..." in Crystal Reports displays more than 1 SQL query
Sample content of "Show SQL Query...":
SELECT "Customer1"."CustNum", "Customer1"."Name", "Customer1"."SalesRep"
FROM "PUB"."Customer" "Customer1"
ORDER BY "Customer1"."CustNum"
SELECT "Order1"."Ordernum", "Order1"."OrderDate", "Order1"."ShipDate", "Order1"."PromiseDate", "Order1"."SalesRep", "Order1"."CustNum"
FROM "PUB"."Order" "Order1"
ODBC trace log file shows multiple queries being sent to the database
crw32 968-924 ENTER SQLExecDirectW
HSTMT 05C225E0
WCHAR * 0x7FCDC59C [ -3] "SELECT * FROM pub.customer\ 0"
SDWORD -3
...
crw32 968-924 ENTER SQLExecDirectW
HSTMT 05C23990
WCHAR * 0x7FE1C304 [ -3] " SELECT "Order1"."Ordernum", "Order1"."OrderDate", "Order1"."ShipDate", "Order1"."PromiseDate", "Order1"."SalesRep", "Order1"."CustNum" FROM "PUB"."Order" "Order1" WHERE "Order1"."CustNum"=1 AND "Order1"."SalesRep"='HXM'\ 0"
SDWORD -3
...
crw32 968-924 ENTER SQLExecDirectW
HSTMT 05C23990
WCHAR * 0x7FE1CA0C [ -3] " SELECT "Order1"."Ordernum", "Order1"."OrderDate", "Order1"."ShipDate", "Order1"."PromiseDate", "Order1"."SalesRep", "Order1"."CustNum" FROM "PUB"."Order" "Order1" WHERE "Order1"."CustNum"=2 AND "Order1"."SalesRep"='DKP'\ 0"
SDWORD -3
FACT(s) (Environment):
Report has been designed using Commands (free-form SQL queries)
Report has been designed against 1+ database connections
Progress 9.1D
Progress 9.1E
OpenEdge 10.x
All Supported Operating Systems
Crystal Reports
CAUSE:
According to Business Objects, no links exists between the report objects (Command and database table, 2 databases etc.) even if links are configured in the Database Expert tool in Crystal Reports. Therefore, Crystal must sent multiple queries to the database in order to build up a result set.
FIX:
Business Objects Technical Support provides the following suggestions to avoid this issue:
Option #1
Modify the command object to use the table within its custom SQL Statement.
Option #2
Generate the complete result-set in another manner and base the report upon this result-set. For example,
- Create a stored procedure that creates a link between multiple SQL Statements on the server and then returns a single result set to Crystal Reports.
- Generate the result-set as an XML file using ABL and use the resulting XML file as the datasource of the report.
Option #3
Use subreports to avoid the issue. Move one of the queries (for the Command or other database connection) into a subreport and linked it the main report. Please refer to the Crystal Reports User's Guide for the version of Crystal used for more information on subreports.