Slow Crystal Reports with Oracle

[kristoff plasun]

I have a problem with a C++ DCOM application that
prints Crystal Reports with data from Oracle.

The SQL query is relatively complex but when
the report is printed from the Crystal Reports
designer it shows up very fast. When the report
is printed from my application it takes about
ten times as long to get the report to appear.

When printing straight from the Crystal Reports
designer Oracle uses about 15% of CPU cycles. When
the report is printed from my application Oracle
uses about 95% of CPU cycles, or sometimes
totally uses it, while my application sits basically
idle throughout the entire operation until it shows
the Crystal Reports control to display the report.

Any idea why Oracle would be working so hard when
the report is printed from my application and not
when the report is printed from the Crystal Reports
designer? How does the Crystal Reports DLL communicate
with Oracle?

Thanks in advance for any help
kristoff plasun

[Jan Gelbrich]

"kristoff plasun" <[e-mail]> schrieb im Newsbeitrag
news:Q1%ib.57$iD6.43@edtnps84...

I have a problem with a C++ DCOM application that
prints Crystal Reports with data from Oracle.

The SQL query is relatively complex but when
the report is printed from the Crystal Reports
designer it shows up very fast. When the report
is printed from my application it takes about
ten times as long to get the report to appear.

When printing straight from the Crystal Reports
designer Oracle uses about 15% of CPU cycles. When
the report is printed from my application Oracle
uses about 95% of CPU cycles, or sometimes
totally uses it, while my application sits basically
idle throughout the entire operation until it shows
the Crystal Reports control to display the report.

Any idea why Oracle would be working so hard when
the report is printed from my application and not
when the report is printed from the Crystal Reports
designer? How does the Crystal Reports DLL communicate
with Oracle?

Thanks in advance for any help
kristoff plasun

And what versions do You use (database, OS, app) ?
And what kind of app do You use (C++ DCOM says nothing) ?
And what is the SQL like (most of them are "complex") ?
And so forth ...

better goto comp.databases.oracle.crystalball ...

sorry, I couldnt rethitht ;)

[NorwoodThree]

That is becuase Crystal reports suck. You can only do so much with
and ODBC connection.

[Turkbear]

no**********@my-deja.com (NorwoodThree) wrote:

That is becuase Crystal reports suck. You can only do so much with
and ODBC connection.

Crystal Reports DOES NOT require an ODBC connection to work with Oracle..It can use NativeSqlNet connectivity.
( BTW, we use it exclusively in our Enterprise environment ( both CR PRO for design and Crystal Enterprise for web
distribution) and have found it to be powerful, fast, and to have a fairly easy learning curve)
[Disclaimer: I am not now, nor have I ever been employed by Crystal Decisions ]

Just my 2c

[kris p]

> And what versions do You use (database, OS, app) ?

And what kind of app do You use (C++ DCOM says nothing) ?
And what is the SQL like (most of them are "complex") ?
And so forth ...
I should have written, HOW does Crystal communicate with
Oracle. When a report is being printed, my application sits
idle. It's not really my code, otherwise I would probably know
the answer. The Oracle ODBC driver version is 8.1.77, win2k.

I think I should delve more into the code and then rephrase
the question.
better goto comp.databases.oracle.crystalball ...
sorry, I couldnt rethitht ;)

:-)

kristoff plasun

[Ross McKay]

On Tue, 14 Oct 2003 22:47:12 GMT, kristoff plasun <[e-mail]> wrote:

I have a problem with a C++ DCOM application that
prints Crystal Reports with data from Oracle.

The SQL query is relatively complex but when
the report is printed from the Crystal Reports
designer it shows up very fast. When the report
is printed from my application it takes about
ten times as long to get the report to appear.

When printing straight from the Crystal Reports
designer Oracle uses about 15% of CPU cycles. When
the report is printed from my application Oracle
uses about 95% of CPU cycles, or sometimes
totally uses it, while my application sits basically
idle throughout the entire operation until it shows
the Crystal Reports control to display the report.

Any idea why Oracle would be working so hard when
the report is printed from my application and not
when the report is printed from the Crystal Reports
designer? How does the Crystal Reports DLL communicate
with Oracle?

G'day Kristoff,

Does the report use multiple tables and join them itself to produce the
result? Or does it select from a view on Oracle? Does it call a stored
procedure to do the work?

At a rough guess, it probably has a bunch of tables that it joins and
then groups to produce output. IME, Crystal does a bad job of optimising
the data access, so you are best off sorting that out on the Oracle side
by creating either a view or a stored procedure that Crystal uses.

My preference is for the stored procedure, because you can then effect
changes within Oracle, often without needing to change the Crystal
report.

To see what it is doing, open the report in the Crystal designer and
select Show SQL Query from the Database menu.

regards,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

[kris p]

> G'day Kristoff,

How you doin' :-) Thanks for your reply!

Does the report use multiple tables and join them itself
to produce the result?
Well, the SQL query contains the join statements so I thought
that Oracle would be doing the work and returning the results.
Or does it select from a view on Oracle?
Nope, but I'm converting the report to use a view now, would
it make a difference?
Does it call a stored procedure to do the work?

I can't rely on that as that would involve creating stuff
on the Oracle and management wants to avoid that for reports.

How would a stored procedure work?

Thanks for your help.

kristoff plasun

[Ross McKay]

[followups trimmed to comp.databases.oracle - please don't cross-post]

Kristoff wrote:

Well, the SQL query contains the join statements so I thought
that Oracle would be doing the work and returning the results.

Or does it select from a view on Oracle?
Nope, but I'm converting the report to use a view now, would
it make a difference?

In my experience, yes it will.

Does it call a stored procedure to do the work?

I can't rely on that as that would involve creating stuff
on the Oracle and management wants to avoid that for reports.

But creating a view is little different to creating a stored procedure.
Either way, you have created something on the server - ten lashes!

Which would they prefer?

a) CR brings the server to its knees every time someone runs a report,
so basic reports can only be run out of hours or against a separate,
not-quite-up-to-date data source; or

b) you build some views and stored procs in a separate schema to enable
reporting to run efficiently?
How would a stored procedure work?

At its simplest, pretty much the same as a view. However, a stored
procedure can do more than a view, especially when you use parameters to
control selection criteria, and other server-side processing.

First off though, see how you go with that view.

regards,
Ross
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

[kristoff plasun]

> But creating a view is little different to creating a stored procedure.

Either way, you have created something on the server - ten lashes!
he he good point! :-)
Which would they prefer?

a) CR brings the server to its knees every time someone runs a report,
so basic reports can only be run out of hours or against a separate,
not-quite-up-to-date data source; or

b) you build some views and stored procs in a separate schema to enable
reporting to run efficiently?

I think I'll go the view way.

Thanks for your help!

kristoff plasun

[kris p]

Hi,

Yes, I'm going to try the view solution. We upgraded the server
from a p4 1.8mhz to a 2 cpu xeon, STILL Oracle maximizes
CPU cycles when running the report. How would I do an ODBC
trace? Or is there any way to get Oracle to log or show exactly
what it's doing at any given moment?

Thanks,
Kristoff Plasun

[Howard J. Rogers]

kris p wrote:

Hi,

Yes, I'm going to try the view solution. We upgraded the server
from a p4 1.8mhz to a 2 cpu xeon, STILL Oracle maximizes
CPU cycles when running the report. How would I do an ODBC
trace? Or is there any way to get Oracle to log or show exactly
what it's doing at any given moment?

Thanks,
Kristoff Plasun

This probably tells you something about Crystal Reports rather than about
Oracle.

It also tells you something about ODBC.

Regards
HJR
--
--------------------------------------------
See my brand new website, soon to be full of
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

[Ross McKay]

kp*****@hotmail.com (kris p) replied to a post in comp.databases.oracle
but crossposted to 3 other groups too:

Hi,

Yes, I'm going to try the view solution. We upgraded the server
from a p4 1.8mhz to a 2 cpu xeon, STILL Oracle maximizes
CPU cycles when running the report. How would I do an ODBC
trace? Or is there any way to get Oracle to log or show exactly
what it's doing at any given moment?

Pick a newsgroup, and maybe we can talk. Once again, follow-ups trimmed
to comp.databases.oracle
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

[kris p]

> kp*****@hotmail.com (kris p) replied to a post in comp.databases.oracle

but crossposted to 3 other groups too:
:-| sorry, will stay in this group only!
Pick a newsgroup, and maybe we can talk. Once again, follow-ups trimmed
to comp.databases.oracle

OK comp.databases.oracle then

I'd like to do an ODBC trace, how would I go about doing that? Is the ODBC
trace function that is accessible through the ODBC admin what I'm looking for?

Also, is there a way to trace what the Oracle server is doing? I'd like to
see what it is doing when it's maximizing the CPU.

Thanks,
kristoff plasun

[Ross McKay]

On 20 Oct 2003 11:03:07 -0700, kp*****@hotmail.com (kris p) wrote:

I'd like to do an ODBC trace, how would I go about doing that? Is the ODBC
trace function that is accessible through the ODBC admin what I'm looking for?
Depending on the version of the ODBC Data Source Administrator you have,
you should be able to enable an ODBC trace to a log file. In my version
(3.52 on Windows 2000), there is a Tracing tab that allows you to start
or stop tracing and select the file to log to.
Also, is there a way to trace what the Oracle server is doing? I'd like to
see what it is doing when it's maximizing the CPU.

As others have said elsewhere, see Ask Tom for some articles about this.
Here is a link:

http://asktom.oracle.com/

Also, why are you using ODBC? There are better ways, e.g. the native
Oracle driver.

regards,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander