Kbase P56724: Poor performance when running a Crystal report that uses multiple databases and joins
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/3/2009 |
|
Status: Verified
SYMPTOM(s):
Using Crystal Reports
Report uses a three table join between multiple databases
Poor performance when connecting to multiple databases via SQL-92
FACT(s) (Environment):
Progress 9.1D
Progress 9.1E
OpenEdge 10.x
Windows
CAUSE:
Crystal Reports is manually implementing the join required to produce the report as SQL-92 cannot directly join tables that are from different databases. In SQL-92 a SQL query is resolved completely on the server and one server cannot connect to a separate database. This is why Crystal Reports must manually implement the join by sending SELECT statements to each database to implement every part of the join. Depending on the join this could mean thousands upon thousands of queries being sent to each database.
FIX:
There is no direct solution for this problem since it relates to how the report was initially designed. From a Progress perspective, the following actions could be taken to try to improve performance:
1) Ensure that the Transaction Isolation Level for each ODBC DSN used in the report is set to either 'READ UNCOMMITTED' or 'READ COMMITTED' as these levels will cause least locking overhead.
2) Ensure that UPDATE STATISTICS has been run for each table involved in the query so that the query optimizer has the best chance of generating an efficient query execution plan.