VB.net front end / slow queries

AYEB Mounir via OracleMon · *nix forums beginner Joined: 17 Feb 2005 Posts: 7

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?

Does a view have an execution plan in Oracle?

Does a stored procedure have an execution plan in Oracle?

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. Will that help speed up the query?

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

There are indexes on the primary keys in both tables.

Any help in speeding up these queries?

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.

--
Message posted via http://www.oraclemonster.com

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

answers embedded

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

AYEB Mounir via OracleMon · *nix forums beginner Joined: 17 Feb 2005 Posts: 7

Sybrand,

Was your reply an attempt at wit, or sarcasm? It's so hard to tell with some technical people, since so many of us think we possess a higher form of intelligence than the rest of humanity... I'll choose to assume you were trying to be witty!

Here is the code for the view:

CREATE VIEW view AS
a.field1,
a.field2,
a.field3,
a.field4,
a.field5,
a.field6,
a.field7,
, sum(case when b.field2 ='A'
then b.field3 else 0 end) as A
, sum(case when b.filed2 ='B'
then b.field3 else 0 end) as B
, sum(case when b.field2 ='C'
then b.field3 else 0 end) as C
, sum(case when b.field2 ='D'
then b.field3 else 0 end) as D
, sum(case when b.field2 ='E'
then b.field3 else 0 end) as E
, sum(case when b.field2 ='F'
then b.field3 else 0 end) as F
, sum(case when b.field2 ='G'
then b.field3 else 0 end) as G
, sum(case when b.field2 ='H'
then b.field3 else 0 end) as H
, sum(case when b.field2 ='I'
then b.field3 else 0 end) as I
, sum(case when b.field2 ='J'
then b.field3 else 0 end) as J
, sum(case when b.field2 ='K'
then b.field3 else 0 end) as K
, sum(case when b.field2 ='L'
then b.field3 else 0 end) as L
, sum(case when b.field2 ='M'
then b.field3 else 0 end) as M
FROM
TABLE1 A LEFT JOIN TABLE2 b ON a.field1 = b.field1
WHERE
a.field5 ='something'
GROUP BY
a.field1,
a.field2,
a.field3,
a.field4,
a.field5,
a.field6,
a.field7

The query in the VB.net code is a simple select statement from the view to display the data to the end user.

I don't have the execution plan.

--
Message posted via http://www.oraclemonster.com

Frank van Bortel · *nix forums Guru Joined: 19 Apr 2005 Posts: 804

Ann via OracleMonster.com wrote:

AYEB Mounir via OracleMon · *nix forums beginner Joined: 17 Feb 2005 Posts: 7

Thanks for your kind reply.

"Filed" in the second select statament is a typo in my post, but it is correct in the code.

Yes, field one is the primary key in both tables. There is an index on field 5. I select all records explicitly naming each field in my select statement in the VB code.

The query is using the index on table a which includes header records, and it is not using the index on table b which includes detail records. It is doing a full table scan on the detail table.

--
Message posted via http://www.oraclemonster.com

Frank van Bortel · *nix forums Guru Joined: 19 Apr 2005 Posts: 804

Ann via OracleMonster.com wrote:

Mark · *nix forums Guru Joined: 24 Feb 2005 Posts: 380

Some thoughts (from a fellow developer and not a DBA), probably best
executed in the order listed.

Have you calculated statistics on the database lately (or at all).

Add indexes to the fields included in the case statements (maybe add
and test, then remove and test others, strange things can sometimes
happen, adding unneccessary indexes can actually decrease performance
in some cases)

Look at the Auto Trace statistics to see if you have the server memory
configured appropriately (I use TOAD, which has a nice little tab to
display them).

Do a google search of this, and the other, oracle new groups for
hints. Or do a general google search.

Read other articles/white papers on optimising queries.

Hire an Oracle guru.

If any of the above help, then feel free to send me an appropriate
consulting fee to my Swiss Bank Account (just joking <grin>), but a
couple of beers never go amiss!

Good luck.

Mark

"Ann via OracleMonster.com" <forum@OracleMonster.com> wrote in message news:<d7f9a4b03ff74756a0b9a0991e3e73d2@OracleMonster.com>...

Google