At 03:35 PM 06-08-02 -0500, you wrote:

>
>The tables are :
>CREATE TABLE "TRAF_ORG"
>(
> "PMDATE" VARCHAR(8) NOT NULL,
> "PMHOUR" INTEGER NOT NULL,
> "BSC" INTEGER NOT NULL,
> "BTS" INTEGER NOT NULL,
> "SECTOR" INTEGER NOT NULL,
> "FA" INTEGER NOT NULL,
> "ATT_VOC" INTEGER default 0,
> "ATT_DAT" INTEGER default 0,
>....
> "AVG_DLY" NUMERIC(15, 2) default 0,
>CONSTRAINT "TRAF_ORG_PRIMARY" PRIMARY KEY ("PMDATE", "PMHOUR", "BSC", "BTS",
>"SECTOR", "FA")
>);
>
>
>CREATE TABLE "TRAF_TER"
>(
> "PMDATE" VARCHAR(8) NOT NULL,
> "PMHOUR" INTEGER NOT NULL,
> "BSC" INTEGER NOT NULL,
> "BTS" INTEGER NOT NULL,
> "SECTOR" INTEGER NOT NULL,
> "FA" INTEGER NOT NULL,
> "ATT_VOC" INTEGER default 0,
> "ATT_DAT" INTEGER default 0,
>....
> "AVG_DLY" NUMERIC(15, 2) default 0,
>CONSTRAINT "TRAF_TER_PRIMARY" PRIMARY KEY ("PMDATE", "PMHOUR", "BSC", "BTS",
>"SECTOR", "FA")
>);
>
>
>I was trying to add :
>ALTER TABLE "TRAF_ORG" ADD FOREIGN KEY ("BSC") REFERENCES BSCMAP ("BSCID")
>ON UPDATE CASCADE ON DELETE CASCADE;
>
>where BSCMAP is a config table defined as :
>CREATE TABLE "BSCMAP"
>(
> "BSCID" INTEGER NOT NULL,
> "MSCID" INTEGER NOT NULL,
> "BSMID" INTEGER NOT NULL,
> "BSCNO" INTEGER NOT NULL,
>CONSTRAINT "BSCMAPUNIQUE" UNIQUE ("BSMID", "BSCNO"),
> PRIMARY KEY ("BSCID")
>);
>
>-----Original Message-----
>From: Amrita Chaudhury [mailto:achaudhu@...]
>Sent: Tuesday, August 06, 2002 3:30 PM
>To: 'ib-support@yahoogroups.com'
>Subject: [ib-support] HELP ! Adding a foreign key SLOWS down query
>
>
>HI,
>
>I had a set of stored procedures that were used to join and query certain
>data tables, and
>all was working fine, till I had to add foreign key reference on one of the
>columns on these
>tables to a column in another configuration table.
>
>If I take off the feriegn key reference, everything is ok, as soon as I put
>it back on, the
>query just never makes it through ..
>
>
>PLEASE help .. I do need to reference this to the other table - how can I
>get away with
>this ?

There are at least two potential factors here causing this slow-down:

1. Creating the foreign key on BSC creates a single-column index on that
column which "steps on" your primary key. If you apply an SQL monitor to
your query (one which displays the PLAN on preparing the query, such as the
one in IB_SQL) I think it will show that the optimizer is not using any
indexes for your query.

One way out of this one is to replace your PK with a surrogate (using a
generator). If all of your current PK columns require uniqueness, place a
unique index on them. You are still going to have indexes stepping on one
another but at least it will give the optimizer access to the primary key.

Better still would be to normalise some of the columns out to other tables
to avoid such massive duplication in the top table that requires six
elements in a PK or uniqueness constraint. This seems especially called
for, given that you have two tables both using the same complex PK structure.

2. If there are very few possible values for BSC, or if most values for
BSC in the actual data are only one or two of the possible values, then the
FK index will have very low selectivity. This is a well-documented source
of query-constipation. Don't use single-column lookup keys as FKs in these
conditions - provide the integrity checking yourself using triggers.

3. You don't mention what the query is but, if there is any ordering or
grouping in it, this would compound the problems the optimizer would have
with these index structures.

heLen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________