At 09:08 AM 31/08/2004 -0700, you wrote:

>Hello:
>
>Having a series of problems with a UPDATE SQL for a table used in a
>treeview.
>
>The following is a trimmed up version, since I'm using various
>parameters in the app's code:
>
>(I'm trying to update the Path field in a moved node and all its
>children.)
>
>This is a very simple example, since the Root ID is the Parent ID. Also,
>this Parent + Item ID combination can be embedded in a path field that
>is any where from 2 to 10 (potentially even larger) strings of IDs long.
>By looking for the combination of Parent + Item Id in the Path field I
>can easily identify the node and all its children.
>
>Update MasterLibrary
>Set ml_path = '.38141.50004.',
>Date_LastEdit = 'NOW' ,
>Root_id = 38141
>Where Root_id = 3419 and
>F_Substr('.3419.50004.', ml_path) > -1
>
>I'm trying to avoid doing a recursive SQL routine to update all the Path
>fields in a Node and all its children.
>
>1. When I was developing the SQL update statement I started without the
>"WHERE ROOT_ID = " part and it was very slow (20sec)--going through
>50,000 nodes with a string UDF probably, but it worked and got all the
>children properly. To speed it up I added the Root_id part (one of about
>200 main branches) with an index and this speeded it up to the point it
>processed everything in well less than a second, which is acceptable.
>
>What I find really strange is suddenly, its back to being very slow. As
>far as I can tell, I've changed nothing.
>
>2. I used IB_SQL to test the above and started to get the following
>error:
> Error 335544345
>Lock Conflict on no wait transaction
>DeadLock
>Update conflicts with concurrent update
>
>The SQL gives this error even if I eliminate the F_Substr portion or the
>Root_id portion.
>
>So my question is what is going on here? Why did the UPDATE become slow
>then start causing errors?

In a NO WAIT transaction (the default in IB_SQL) a lock conflict excepts as
soon as it occurs. In a WAIT transaction, the update will wait until the
conflicting transaction commits, upon which it will either except (because
the conflict wasn't resolved) or finally commit (if the other transaction's
commit or rollback resolves the conflict).

So it would be worth looking at the transaction settings for the test that
is demonstrating the slowness.

The next thing is to find the transaction that is causing the lock
conflict. Assuming you are alone on the system, if you have IB_SQL (or some
other tool) that has some operation pending on the table in another
transaction, then you've got it.

On the subject of the actual update:

Update MasterLibrary
Set ml_path = '.38141.50004.',
Date_LastEdit = 'NOW' ,
Root_id = 38141

Sans the WHERE clause, this will update EVERY row in MasterLibrary.

Adding this:
where Root_id = 3419

causes the update to target all of the rows that currently have Root_id = 3419

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

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

On b), you should simply omit that expensive path search if the Root_id
unequivocally finds all the child records anyway. It's "just data", not a key.

>I suspect that I should just go ahead and do the recursive routine to
>update the ml_Path field anyway.

I don't understand what you intend here.

>It would give me more flexability in
>designing the treeview (what if I should decide to have only a few root
>nodes, then Root_id would correspond to a large data set and the UPDATE
>would be slow again?)

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.

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