Re: Performance of constraints

> 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.