Home » RDBMS Server » Performance Tuning » SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast (Oracle Database 10g Enterprise Edition 10.2.0.1.0)
SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433672] Thu, 03 December 2009 11:18 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12404
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 #433675 is a reply to message #433672] Thu, 03 December 2009 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>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?

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?
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 Go to previous messageGo to next message
thomas.weigel
Messages: 2
Registered: December 2009
Junior Member
cookiemonster wrote on Thu, 03 December 2009 11:28
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.


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.



Re: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433678 is a reply to message #433672] Thu, 03 December 2009 11:39 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the query.
Post an explain plan from sqlplus.
Post an oracle trace of the crystal reports session when it's running this report.
Re: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433713 is a reply to message #433672] Thu, 03 December 2009 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
thomas.weigel wrote on Thu, 03 December 2009 18:18
Running the SQL out of the report with Toad:
- The SQL is fast
- The temp tablespace isn't growing

Did TOAD, perhaps, fetch only a few (hundreds of) records of the whole, possibly huge result set? If so, your remark about the difference doesn't have to be significant.
Re: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast [message #433734 is a reply to message #433713] Thu, 03 December 2009 21:31 Go to previous message
wvu1999
Messages: 38
Registered: April 2007
Member

Watch out for Cartesian joins when running via ODBC - Access is notorious for this.

Here's a whitepaper on the subject.

http://f1.grp.yahoofs.com/v1/MHsYS3_v9xvThFQ_FzMGYiz_jGJxj_GKR33lF0InSqIUc_VZOrD3Ho08POinxHDLiYWqbxwMjxo-OJLebwqi/AccessODBCOracle.pdf
Previous Topic: How to identify table need resequencing.
Next Topic: How to calculate ideal size of log_buffer parameter?
Goto Forum:
  


Current Time: Mon Dec 05 18:56:04 CST 2016

Total time taken to generate the page: 0.08667 seconds