Subject UPDATE SQL in Treeview table suddenly very slow, with errors
Author Chuck Belanger

Having a series of problems with a UPDATE SQL for a table used in a

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

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 335544345
Lock Conflict on no wait transaction
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?

I suspect that I should just go ahead and do the recursive routine to
update the ml_Path field anyway. 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?)

Thank you for any suggestions,

Chuck Belanger