Re: Extents, Index, Optimization Q's

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:amuln7$32b$1_at_ctb-nnrp2.saix.net...
> 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

Bingo (as Nuno said so eloquently).
Give them package calls (including ref_cursors), and that's it. Then you've a fighting chance of supplying a system which actually works...

Cheers,
Paul Received on Sun Sep 29 2002 - 16:38:12 CDT