Hi,

I was wondering if anyone could give me any help in optimising a
query...

We have the following situation... We have a parent Object (say
ObjectA) that has a completion status... When all of its parts are
complete, it's status changes...

It has the following parts, One or more ObjectBs, which have one or more
ObjectCs which have one or more ObjectDs.

The ObjectDs are the things that really has the status values that are
changing on a day to day (or Hour to Hour) bases... The other objects
serve as (among other things) a grouping of the child objects.

We were planning on running a query that would update the ObjectA status
by looking through all the child objects and checking all the child
ObjectDs status values... This took ages and the user had no idea on
what was going on, se we came up with the following pseudo code which we
run in Delphi so we can show the user that something is happening...
**************
For all the ObjectAs that Status < Ready do
Tell the user that we are doing something on ObjectA.Id

Select Count(ObjectD.ID)
FROM ObjectD
INNER JOIN ObjectC ON (ObjectD.ID = ObjectC.ID)
INNER JOIN ObjectB ON (ObjectC.ID = ObjectB.ID)
WHERE (
(ObjectB.Id = :ObjectA_Id) AND (ObjectD.Status < 8) /* 8 = Ready
*/
)

If the Count of ObjectD.Id = 0 then
Tell the user that ObjectA is ready
Update the status of ObjectA to ready
else
Tell the user that Object A is not ready
*****************
Note that Telling the user doesn't hold up the progress (it is just a
status label).

On a daily bases we might have 1000 odd ObjectAs that are < Ready and we
need to check their status 3 or 4 times a day.

The query above takes about 1-2 seconds per ObjectA which when you have
1000 ObjectAs takes about 15-30 minutes. The number of active ObjectAs
is only going to increase.

We thought about running the query every so often behind the scenes on
another PC but while the server is running these queries, everything
else on the server (almost) grinds to a holt... That is, other users
suffer badly...

Se finally my question :-) We need some alternatives

A couple I can think of are:
1: A faster query (I have heard putting indexes on the system tables
helps for some things, is this a worth while thing to look at??)
2: A new field on table 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.
Would this be a bad way of doing it multi user wise. The query in the
trigger on ObjectD would be something like
*******
Update ObjectC(or B) set Count_Not_Ready = Count_Not_Reay +(-) 1 Where
Id = New.ObjectC(orB).ID
*******
Sorry for the long winded explanation, but now you know must of the
details :-)

Thanks in advance for any advice.

Regards
Colin


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