Re: Optimization advice
From: Hans Forbrich <forbrich_at_yahoo.net>
Date: Fri, 17 Sep 2004 14:19:35 GMT
Message-ID: <XnC2d.49723$XP3.18529_at_edtnps84>
>> I am looking for information, books, websites, etc that will help me
>> out the following things in MSSQL and Oracle:
>>
>> 1. Optimizing the database configuration itself.
>> 2. Optimal table and index design.
>> 3. Optimizing SQL statement lookups.
>> 4. Anything else that might help to speed up our database applications.
>>
>> Any advice at all would be useful.
>>
>> Thanks alot.
>>
>> Andy Reynolds
>> ajreynolds_at_san.no.spam.rr.com (remove the no.spam to send email)
>>
>>
Date: Fri, 17 Sep 2004 14:19:35 GMT
Message-ID: <XnC2d.49723$XP3.18529_at_edtnps84>
Jim Kennedy wrote:
> > "Andy Reynolds" <ajreynolds_at_san.removethis.rr.removethis.com> wrote in > message news:Xtt2d.7869$XW.206_at_twister.socal.rr.com...
>> I am looking for information, books, websites, etc that will help me
> figure
>> out the following things in MSSQL and Oracle:
>>
>> 1. Optimizing the database configuration itself.
>> 2. Optimal table and index design.
>> 3. Optimizing SQL statement lookups.
>> 4. Anything else that might help to speed up our database applications.
>>
>> Any advice at all would be useful.
>>
>> Thanks alot.
>>
>> Andy Reynolds
>> ajreynolds_at_san.no.spam.rr.com (remove the no.spam to send email)
>>
>>
> Optimize the application. > Use bind variables > Get data efficiently, use array interface. > Avoid unnecessary parsing. > write efficient queries. > > If you don't do the above then the rest is useless or of little value. > Jim
Your list being in context of Oracle. Supporting your list ...
... as described in Jonathan Lewis' "Practical Oracle8i" (and valuable even now with 10g), Thomas Kyte's "Effective Oracle by Design", other books by OakTable members (http://www.oaktable.org), and in the Oracle documents such as "Application Developer's Guide - Fundamentals" at http://docs.oracle.com
And adding to your list ...
Don't use DDL (creates, drops, alters) at run time
Learn Oracle's Global Temp Tables before diving into the Temp Table mill
Don't commit in loops
Don't use procedures where set operations will do
Do use stored procedures
Don't reinvent the wheel. Consider using Oracle's built-in
- workflow
- message queueing
- geospatial (Locator), Document (Text), multimedia (Intermedia)
- security (Row Level Security & Policy-based statement re-write)
- auditing
- direct http interaction (frequently replaces ASP, Perl)
- direct file, tcp interaction
- job scheduler
- external procedures
- external tables
- and so on
Don't use same development techniques for Oracle and MS SqlServer
- due to different locking internals, different techniques MUST be used!
(BTW - the comp.database.oracle.* heirarchy and the group comp.databases.oracle are rogue - please help bring the lost souls back to comp.databases.oracle.* per the charters copied at http://orafaq.com)
/Hans Received on Fri Sep 17 2004 - 16:19:35 CEST