Re: Slow Crystal Reports with Oracle

[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?

1. 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
2. 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