Recursive/Recalculating/Interdependant Query

[InstructionWhich7142]

I have a table of data which creates a tree structure, with "rererence numbers" and "parent reference numbers"

I have an update query which looks for a value against a parent and multiplies it by a "per parent" field on the child and writes this value against the child

This goes on for up to 6 tiers down,

What has thrown me is that it runs reasionably swiftly for 165000 records but then makes 1.3 million updates!

I was assuming i would have to run multiple passes for every level (i've done something similar in the past and had to do this)

Have i some how stumbled upon a recursive query? or is it seeing changes in records and rerunning previous updates again?

 

[Guus2005]

Looks to me you have stumbled upon a cartesian product. But i can't be sure since you don't show as the sql statement.

It is possible that a single record is updated more than once. In your case about eight times.

Example1:

update table10recs set field1=1

Each of the 10 records are updated once.

Example2:

update table10recs, othertable10recs set table10recs.field1=1

Each of the 10 records are updated 10 times.

Example3, Solution:

update table10recs, othertable10recs set table10recs.field1=1 where table10recs.field1 <> 1

Each of the 10 records are updated not more than one time, maybe less.

HTH

 

[InstructionWhich7142]

I saw a tip suggesting that you make your update query as a select to see what it actually finds to update and I worked out why there were a lot more records than first expected: every time a parent occurs it pulls through all its children again (not unexpected in hindsight)

That aside i'm still unsure how the sub levels (eg 3rd and 4th) seem to use the updated values from the levels above (1, 2, [3]) to calculate their requirements in a single pass when i was expecting to have to go through once for every level to cascade the values down.

 

[Guus2005]

A recursive function works it next level based on the parameters it is provided with.
A query can have parameters that can change while running.

If you think you have a recursive query, please let me see it.

 

[InstructionWhich7142]

UPDATE sndbasis

LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.ijn = sndbasis_1.ijn) AND (sndbasis.pa_irn = sndbasis_1.irn)

SET sndbasis.req = IIf([sndbasis].[level]<>0,[sndbasis].[multiple]*[sndbasis_1].[req],[sndbasis].[req]);



basically if its not the very root of a tree update to be the parents requirement * the childs multiple to give the childs requirement

(IRN is internal reference) - PA_IRN is the link field between a child and its PArents reference

 

[Guus2005]

So your sndbasis table looks like this:

field ijn
field pa_irn
field irn
field req
field level
field multiple

whats ijn, req and multiple?

 

[InstructionWhich7142]

ijn is ----- basically which tree its part of if that makes sense

ie there are a few thousand trees all with the same format of IRN

the IJN determines which tree you are looking at



Multiple is the multiple of which the child is required for each of the parents eg:
if you need 10 child for 1 parent you have a multiple of 10, so if you require 20 parents you need 200 childs

thats what i'm trying to work based upon - calculating down the tree based on the requirement of the top value and the multiples of every lower level

 

[InstructionWhich7142]

oh and req is the value to update by multiplying the multiple by the req of the level above