Hi,

Here are a simplified set of table definitions to the query I posted
earlier as requested by Alexander.

The only indexes on these tables are the ones that were created for the
primary and foreign keys. There are also triggers which fire on updates
and inserts etc, but they shouldn't be relevant as we are just trying to
speed up a select query.

I will also just reiterate the problem/question below.

Every couple of hours we want to know the status of all active ObjectAs.
This is done by looking at the status of all the related Object D's. For
each Object A this takes 1-2 seconds which is too slow. So we need to
either speed up the query or think of another solution, one that came to
mind was creating a new field on ObjectC or ObjectB that records the
number of ObjectDs that are not yet ready. As records are added and
updated in table ObjectD, triggers fire and update ObjectC or B's
counter field.

Question: Would this be a bad way of doing it multi user wise?

Alternative Question: Should we completely de-normalize and put the
ObjectA Id into ObjectD?

Thanks in advance for any advice/suggestions.
Colin
*******************Table definitions follow.

CREATE TABLE ObjectA (
ID INT_ID NOT NULL,
STATUS SMALLINT NOT NULL,
etc
TIME_STAMP TIMESTAMP);

/* Primary keys definition */
ALTER TABLE ObjectA ADD CONSTRAINT PK_ObjectA PRIMARY KEY (ID);


CREATE TABLE ObjectB (
ID INT_ID NOT NULL,
ObjectA_ID INT_ID NOT NULL,
etc
TIME_STAMP TIMESTAMP);

/* Primary keys definition */
ALTER TABLE ObjectB ADD CONSTRAINT PK_ObjectB PRIMARY KEY (ID);

/* Foreign keys definition */
ALTER TABLE ObjectB ADD CONSTRAINT FK2_ObjectB FOREIGN KEY (ObjectA_ID)
REFERENCES ObjectA (Id);


CREATE TABLE ObjectC (
ID INT_ID NOT NULL,
ObjectB_ID INT_ID NOT NULL,
etc
TIME_STAMP TIMESTAMP);

/* Primary keys definition */
ALTER TABLE ObjectC ADD CONSTRAINT PK_ObjectC PRIMARY KEY (ID);

/* Foreign keys definition */
ALTER TABLE ObjectC ADD CONSTRAINT FK2_ObjectC FOREIGN KEY (ObjectB_ID)
REFERENCES ObjectB (ID);
ALTER TABLE ObjectC ADD CONSTRAINT FK3_ObjectC FOREIGN KEY
(CREATOR_ObjectD_ID) REFERENCES ObjectD (ID);
ALTER TABLE ObjectC ADD CONSTRAINT FK4_ObjectC FOREIGN KEY (ID)
REFERENCES ObjectE (ID);


CREATE TABLE ObjectD (
ID INT_ID NOT NULL,
ObjectC_ID INT_ID NOT NULL,
STATUS SMALLINT default 0 NOT NULL,
etc
TIME_STAMP TIMESTAMP);

/* Primary keys definition */
ALTER TABLE ObjectD ADD CONSTRAINT PK_ObjectD PRIMARY KEY (ID);

/* Foreign keys definition */
ALTER TABLE ObjectD ADD CONSTRAINT FK1_ObjectD FOREIGN KEY (ObjectC_ID)
REFERENCES ObjectC (ID);

-----Original Message-----
From: alex_vnru [mailto:ded@...]
Sent: Tuesday, 22 January 2002 12:11 a.m.
To: ib-support@yahoogroups.com
Subject: [ib-support] Re: Optimising an SQL Query Help


Colin, if you show us tables definitions without attributes out of
question but with PK/FK constraints and all indexes, we can help you
faster.

Best regards, Alexander V.Nevsky.



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/



######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################