Re: Referential integrity

In article <6jqff8$7dk@bgtnsc02.worldnet.att.net>, Idiot <rajam@worldnet.att.net> writes >Here is my two cents: > >Referential integrity through declaration is great except most of the >database implements them with a separate index. Consequently, a table may >end up being "over-indexed" and can contribute to poor performance in two >ways: > >While updating or inserting rows in a table, the engine not only has to >update data pages but also update index pages for all indexes. > >In some cases, it is possible that the query optimizer will pick the wrong >or inefficient index. > >This is what I normally follow. Your input will be appreciated. > >Create reference integrity declarations for the development database. It >will help catching programming issues. > Agreed. >All data modifications program will check for referential integrity. In >most application, they constitute less than twenty percent of all SQL. > >Run a weekly batch process to look for referential integrity of the >production database. > Agreed, a ripple(TM) server. Scans the database using dirty read isolation (no locks)..then a cleanup server scans the resultant primary keys using repeatable read isolation (everything locked) at a 'quiet' time.. Guess who just defined a new project.... >Hope it helps.... > >Raja >Peter Tashkoff <TashkoP@kiwi.co.nz> wrote in article ><6jnei2$jb8$1@news.xmission.com>... >> >> According to everything I learned at Database School 101, declarative RI >= >> is *much* more highly performant than using triggers and stored >procedures,= >> which kind've makes sense when you think about it. I don't know of = >> anyone in Informix using any other kind unless they are a subscriber to = >> the multiple databases school of thought and so need to enforce RI = >> *across* databases. >> >> OTOH they also told me as a DBA I would *love* RI, so how come I hate it >= >> ;-) ? >> >> How seldom does the consummation reflect the anticipation n'est-ce pas? >> >> rgds >> >> >> >> Peter Tashkoff <tashkop@iname.com> >> Zespri International Limited Std Disclaimers Apply >> All rights reserved. No party may use this document to vilify another. >> >> Zespri New Zealand Kiwifruit, The World's Finest >> >> >> >>> Philippe Fornaciari <p.fornaciari@summittechnologies.com> 16/05/98 = >> 09:11:02 >>> >> Hi all, >> >> I am using Power designor to build the conceptual and physical model of >> my project. To build my database (Informix OLWS 7.22 ) from my physical >> model, there are two ways to generate referential integrity in the >> database : >> >> * Using triggers and stored procedures >> or >> * Generate declarative statements in a SQL script >> >> >> What is the best method for performance ?=20 >> >> Best regards. >> >> >> Philippe Fornaciari >> Summit Technologies International >> p.fornaciari@summittechnologies.com=20 >> >> >> -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care