Re: VB.net front end / slow queries

answers embedded

On Thu, 24 Feb 2005 17:16:07 GMT, "Ann via OracleMonster.com" <forum_at_OracleMonster.com> wrote:

>I am a VB.net developer and not a dba... I'm developing a VB.net application with an Oracle database backend
>
>Is it better to use a view in the database for a query that will be repeatedly accessed, or a stored procedure?
>

it depends
>Does a view have an execution plan in Oracle?
>

no. sql statements have execution plans
>Does a stored procedure have an execution plan in Oracle?

no
>
>I am accessing the Oracle database via a VB.net application and have set up views to make the database do some of the work.
A view is nothing more than a canned select statement. There is no difference between processing a view and a normal select Will that help speed up the query?
>

it depends
>Right now, there are 1.8 million header records joined on 4.2 million detail records. I created a the equivalent of a crosstab query using case statements to make one transaction reconrd via a view. And, the query takes about 15 minutes to run???

Crystall balls have sold out here.
>
>There are indexes on the primary keys in both tables.
>

So?

>Any help in speeding up these queries?
>

Not if you don't post them and don't include their execution plans.

>To complicate things, I have a dataset linked to a crystal report to display the data, and so the results don't display until the query results are totally completed. This will be very frustrating to the end user. And the user inputs a date range as parameters. Obviously, the larger the date range, the longer the query takes to run...
>
>All Oracle Gurus please Help! And, thanks in advance.

--
Sybrand Bakker, Senior Oracle DBA