Thanks for your, as usual, thoughtful reply.

Your comments on Transaction states makes me realize that I need to study that
more. (I'm hoping to be able to afford getting your book this coming month.) It may
be the problem behind my "Slow Post" posting in IBO groups.

I believe the problem was that I was preparing and running a SQL statement a number
of times in IB_SQL when testing and may have left the system waiting for a commit
or roll back.

The program is a desktop, single user system, Local access.

> On the subject of the actual update:
>
> Update MasterLibrary
> Set ml_path = '.38141.50004.',
> Date_LastEdit = 'NOW' ,
> Root_id = 38141
>

My intent here was to use the Root_id (yes, I agree a low selectivity id, but I had
it in the table because of series of routines that I needed just the top most item
id in a tree branch) to help limit the number of records in the set that f_substr
was looking at. What I found strange was that indeed, initially it worked to make
the whole UPDATE much faster, then later everything slowed down.

After thinking more about it, I just bit the bullet and rewrote the entire routine
to update the ml_path field using a recursive approach to each potential child for
other children in the node that was being moved in the tree. I won't be using
Root_id. (My post in IBO about POST being slow was a result of that rewrite.)

>
> Adding this:
> and F_Substr('.3419.50004.', ml_path) > -1
>
> does one one of two things, depending on the data.
>
> case a) if there are any "3419" rows that don't have that path, they will
> be ignored

Case a) is what I was hoping for and initially it seemed to work just fine.

>
> or
> case b) if all of the "3419" rows have that path, then the search is doing
> a helluva lot of hard work that is completely unnecessary.

Case b) would only occur if I were moving the "root" node.

>
>
> My comment on case a) is that you need to make a structural change. Store
> the path strings in another table with a unique integer index, that you key
> into this table and index. That will solve the gross slowness of using
> f_substr

I agree that f_substr is VERY slow and not appropriate for a time critical update
of multiple records. The PATH field is a way around the problem that I need access
to the Tree structure in some operations and other times I need access to the table
structure. I saw this field structure being used in FIBPLus's demo software for the
dbVirtualTree. It helps solve a number of problems using a DB based tree,
especially the VirtualStringTree that is available from Mike Liskte (SP?).

The tree has minimal data associated with each node, but it does have some node
based routines to access the node's parents as well as children. The Path field is
a structure that is unique for each item, showing where in the tree each item is
located. Its great for reports and search functions. It is not meant to be a key at
all, more a map. But, when I move a node that field needs to be updated, and when I
move a node that has children all the children of that node need their paths
updated, too. Limiting updates to just those items that have the node's record id
somewhere in the path allows for a simple method of updating the node and all its
children for their path fields. All I have to do is insert the new parent id into
the correct position in the Path field. It works great, but slow.

>
> I don't understand what you intend here.

(i.e. Recursive routine to update the Path field) Simply, that I update the moved
node for its path and if it has children then I move through each layer of
potential children of the moved node, re-calling the routine to select the layer of
children and updating the Path field.

>
> The whole things seems structurally woolly to me. If ml_path is really
> needed as a structural key then it should *not* be dependent on a costly
> string function to get found. So let's clarify exactly what the structural
> keys are and get them indexed.

They are! The DB structural keys are ml_id and ml_parentid, but the ml_path field
is just a map of the tree structure. It just needs updating when I move a node. No
doubt about it, F_Substr() is a costly method of doing this and in the end, I spent
a lot of time trying to be lazy :) I still ended up writing the recursive routine
that does not use ml_path to update itself.

Thanks again,

Chuck

p.s. I hope you can look at my question about POST being so slow (IBO groups,
because I use IB_Query). I suspect I'm not doing something correctly. I keep seeing
my FoxPro work and mentality coming backt to haunt me and this may be one of those
times.

>
>
> Low selectivity on the Root_id is a possible performance trap that, with
> the proper structure, you could resolve. The current structure doesn't
> offer anything towards that goal.
>
> ./heLen
>
>
>
> Yahoo! Groups Links
>
>
>
>

[Non-text portions of this message have been removed]