Re: Performance Tuning
Date: Thu, 10 Nov 1994 15:05:41 GMT
Message-ID: <Cz24LI.Cws_at_lanier.com>
In article <39p707$r83_at_use.usit.net>, Jerry Kirk <jlk_at_use.usit.net> wrote:
>We are having some preceived performance problems under Oracle 7.0 on a
>Sparc 10. The database was designed with many many foreign keys though.
>On a table with only 1 or 2 foreign keys we are able to add about 2000
>records per minute but on our worst table we can only had about 300.
>
>Our worst table has 22 foreign date keys that need to look in something
>like 8+ other tables. The big question is.....
>
22 foreign keys sounds like a lot of overhead on inserts and deletes,
but if your application really needs it, using the foreign keys is
probably better than writing application code to do the same job.
An order of magnitude difference in insert rate isn't really surprising, but you may be able to do better. Make sure you have appropriate indexes defined. Each insert will look up each foreign key value in the parent table; large parent tables may need indexes on their primary keys to support this. Likewise, each delete of a parent record will check for child records, so indexes may be needed on the child tables as well. Of course, this must be decided separately for each table, depending on the size etc.
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Thu Nov 10 1994 - 16:05:41 CET