| SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433672] |
Thu, 03 December 2009 11:18  |
thomas.weigel
Messages: 2
Registered: December 2009
|
Junior Member |
|
|
Hi,
we are currently facing a strange problem with Oracle and Crystal Reports.
- Crystal Reports uses a ODBC to connect to oracle
- Monitoring this process we see the temp tablespace growing and
growing and growing
- Oracle writes and reads like crazy from the tmp tablespace
- starting with a fresh created tmp tablespace, it grows up to
- 12 GB
- The Report processing takes very long
Running the SQL out of the report with Toad:
- The SQL is fast
- The temp tablespace isn't growing
Has Anyone here a explanation for this behaviour or anyone
who had the same problem or any tips what we should do now improve the behaviour at this point.
Thanks in advance
Thomas
|
|
|
|
| Re: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433673 is a reply to message #433672] |
Thu, 03 December 2009 11:28  |
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
How are you specifying the query in crystal?
Are you hard coding it with a command object or are you telling crystal what tables to use and what the joins are and letting it sort out the query itself?
I've found through long and bitter experience that if you don't tell crystal exactly what to do it tends to pull all records from the tables specified and then join the data itself instead of letting oracle do it. This causes both oracle and crystal to do far more work than is necessary to get the job done.
|
|
|
|
|
|
| Re: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433676 is a reply to message #433673] |
Thu, 03 December 2009 11:36 |
thomas.weigel
Messages: 2
Registered: December 2009
|
Junior Member |
|
|
cookiemonster wrote on Thu, 03 December 2009 11:28How are you specifying the query in crystal?
Are you hard coding it with a command object or are you telling crystal what tables to use and what the joins are and letting it sort out the query itself?
I've found through long and bitter experience that if you don't tell crystal exactly what to do it tends to pull all records from the tables specified and then join the data itself instead of letting oracle do it. This causes both oracle and crystal to do far more work than is necessary to get the job done.
The query is a command object, because of: we made the same bitter experience like you.
BlackSwan wrote on Thu, 03 December 2009 11:30>Crystal Reports uses a ODBC to connect to oracle
>Running the SQL out of the report with Toad:
What are Operating System (OS) names & versions involed?
CR, Toad an Oracle are on Windows Server 2003
BlackSwan wrote on Thu, 03 December 2009 11:30>
Is TOAD running on the same system as Crystal Reports?
How many physical systems are involved?
Is this difference occuring with all reports or only a subset?
Have you tired different ODBC drivers?
We tried different ODBC drivers (Oracle ones and CR ones): still this problem appears.
Not all Reports produce this "problem", at least the ones with complex SQLs.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
