A common problem, that possibly can be solved by:

You are trying to aggregate (to my mind) the results of multiple children
into various parents. What can work (depending on numbers of items, layers
and load), is to have a "changed" table, whereby when a status changes in
layer D, you put a marker in LayerDActions table, this allows a worker
process to check upstream from LayerD without generating contention on Layer
C (this wouldbe caused by using straight triggers). This means that instead
of updating every row in Layer C & B & A, you only update those that have
changed. YOu could have the table in such a way as:
Layer => Layer
PK => PK of record that has changed.

When you select this you can do all of the implications of layer D, then C,
then B and finally A - minimal server load, therefore you can do it more
often and keep the data fresher.

My 2c.

JAC.


""C Fraser"" <Colin.Fraser@...> wrote in message
news:003e01c1a209$e81f5f20$8f01a8c0@......

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