Re: Performance of constraints

Pavel, Could you please send me a copy of your tools. Thanks Cheryl Kendricks E-mail: cherylk@spmais.jcdc.doleta.gov On 18 Jan 1996, Pavel Kazenin wrote: } > From: Uwe Droste <ud@mds-hb.de> } > Organization: Messerknecht Datensysteme } > Subject: [NEWS] Performance of constraints } > } > Hello world, } > } > we have a problem with INFORMIX CONSTRAINTs. } > We use SE 5.x or higher and ERwin/ERX (Windows program) } > to document the datamodel. We want to document the } > references between the tables too, but we don't want } > to define this references in ERwin. We have to } > create CONSTRAINTs in INFORMIX SE and make a } > reengineering in ERwin and here we only have to } > documentthe tables and references. } > } > So we create CONSTRAINTS in INFORMIX with } > SQL-statements. For example: } > } > ALTER TABLE orders_t ADD CONSTRAINT } > FOREIGN KEY (terms_of_delivery) } > REFERENCES terms_of_del_t (mc) } > CONSTRAINT orders_constr } > } > INFORMIX automaticially create indices for foreign keys, } > which don't have an index (here terms_of_delivery). So } > we have in this example one table (orders_t) with one } > primary an many foreign keys. We only want one Index on } > the primary key to have a very good performance on INSERT } > and UPDATE. Now we have many Indices on this table and a } > very bad performance. } > } > So, have anyone an idea to create CONSTRAINTs without } > creating an index? } > } > Thank you } > Uwe } > } } Unfortunately, there is no way to create a Referential Constraint } without creating a pair of indexes ( below is extraction from "Guide to SQL, } Reference", page 7-71 ): } } " } [...] } Primary key, unique, and referential constraints are implemented either as } an ascending index that allows only unique entries or an ascending index } that allows duplicates. When one of these constraints is placed on a column, } the database server performs the following functions: } } * Creates a unique index for a unique or primary key constraint } } * Creates a non-unique index for the columns specified in the referential } constraint } [...] } " } } Below is my humble opinion about Ref. Constraints: } } Reasons to create them: } 1. Now Informix DB Server takes care of the database Referential Integrity } and rejects all attempts to violate it ( but not for free ). } 2. Your code (at least in 4GL) becomes shorter, since you have to write } only one powerful error handling routine instead of needing to propagate } many similar pieces of code. } 3. SELECT performance should not change for the worse. } 4. Your Project Documentation ( DFDs, ERDs, etc ) is in line with real } Development/Production Environment and the database structure. } } Reasons to NOT create them: } 1. They slows down INSERT and especially UPDATE performance - see "Guide to } SQL, Tutorial", p.10-10. } 2. You get yet another head-ache: managing Development/Test Environment for } the database with RCs is complicated a lot. } } I wrote some tools that simplify dealing with Ref. Constraints, } so feel free to mail me directly at pavel@cso.spb.su and I'll send you them. } } Good Luck, } Pavel. } }