Re: Extents, Index, Optimization Q's

Len wrote:

> I have a VB application that opens and accesses an Oracle DB.
> The routine iterates through records at glacial speeds.

You are using data bound controls? Be careful as the controls generate their own SQL. It can get especially bad when you start using data bound control features like filtering.

> I indexed the 22 tables that are opened read and closed, that helped
> marginally.

To treat the symptom (slow performance), you need to diagnose the the problem.

Adding indexes can actually decrease performance.

> After only experiencing marginal improvements I tried reducing the
> extents on the tables, marginal improvement but nothing colossal.

Len, again you are trying to fix the symptoms. You should rather look at the cause.

> Does anyone have recommendations to improve the speed of my
> application by tweaking the way data is stored in the Oracle 8 DB?

The primary criteria is having an application that is correctly designed.

IMO, data bound controls is a bad idea in general. You need to known what you are doing and must ensure that the controls generate the correct SQL statements.

In fact, I would say that there should be no SQL at all at client level because of various issues (programmer skills, lack of understanding of Oracle, inability to perform fine-tuning at client app level without recoding, recompiling and redistribution, etc.)

I'm a staunch supporter of providing an API at db lebel for the font-end programmers. A a well designed API for accessing, retrieving and manipulating data (said API written in PL/SQL, Java, Transact-SQL, etc).

The programmers know how to work with APIs (or they should). Everything in Windows is API based (GDI, mouse, keyboard, networking, etc). They will not be able to screw this up to the same extent as they often butcher SQL and hack indexes and db designs.

Having the API implementation on the database allows you to tune that API for better performance. Allows you to implement new business and validation rules. Make structural changes to the database. All without having to recompile any of the client code. In addition, you also reap benefits of having a sound design for implementing a proper security model. The same API set can be used from Web Server applets/CGIs for reporting and stats purposes.

Performance is not something that can be added as an afterthought to any design. That needs to be part and parcel of the design and development process. Especially knowing Oracle.

You can _not_ treat Oracle like a black box when dealing with it at SQL level. Or any other database for that matter. If it needs to be a black box, then you need that API architecture.. i.e. removing the need to understand RDBMS basic theory and Oracle specifically, from the VB programmer.

Probably, not the answers that you were looking for... but that is how I think the cookie crumbles. :-)

--
Billy