Subject Re: [IBO] IB_QUERY, POST is taking long time, what am Idoingwrong?
Author Chuck Belanger
Hi, Helen:

Thanks for the reply.

First, this application I'm working on is single user, desktop, local (probably
Firebird embedded) application, not multiuser at all. The edit routines are
primarily for me and will not be visible to the end user. For all practical
purposes the end user will not be making any edits or if they do they will be
limited in quantitiy and frequency. I know of no other design besides a treeview
which can give the type of hierarchal information structure that I'm trying to

> >Also, I completely rewrote the routine I was using to do the ml_path
> >update. This
> >is really a completely different problem (or is it?)
> No, it's the same problem.

In the previous case it was all about using the ml_path field to restrict the
update of the ml_path field. Now, I'm just Selecting a single record using its PK,
Edit, doing several FieldByName('field1').asWhatever := Something, then POSTing and
its very slow posting this single record. I don't get it.

> >Maybe its all about my transaction set up?
> Perhaps, but there's no information about it.

Here's my IB_Transaction1 set up:

Autocommit : true
Isolation : tiCommitted
Readonly : false
ServerAutocommit : false

> Clarify what you mean by "posting the changes of that first record."

I mean saving the changes of the actual first item in a potential branch that I'm
drag & dropping to another location in the treeview. The only changes will be
Parent_ID, possibly Root_Id, and ml_path, which is just a string of record ids from
the rootID down through the branches to the actual item itself. The record IDs are
separated by periods '.' . I do record a date/time for doing any of these changes.

The rest of the routine is only called if the item being moved has children. Right
now, even posting changes to this first record is very slow and is causing the
problem. I'm not even focusing on the potential children now. In my tests I'm only
dragging & dropping a single item that has no children, just to keep it simple and
that is slow.

> Also clarify what you mean by "move a node". It looks as if you are using
> some kind of independent system of dotted codes for defining the
> hierarchical structure, which is making this thread very confusing.

I'm really sorry about making it confusing. Not my intention to do so. The ML_PATH
field is just a string and maps the location of each item in the tree. The main
tree has all items in MASTERLIBRARY in the tree, right now that's about 50,000
items (and growing).

> In
> your design, what's the difference (logically) between moving a bunch of
> children from one FK parent to another and moving the same bunch of
> children from one "path node" to another?

Parent is not a FK. Ml_id (PK) and the ParentID are fields within the table. Its
self referencing, similar to other tables used for treeviews. From what I
understand the use of the word "node" refers to the item in the tree. Once added to
the tree it is separate from the table. In memory it has its own record with
limited data (mainly Name, ID). So when I "move" a node I'm actually doing two
operations: 1. cutting the node and all its children from the visual treeview then
adding the same to a new visual node and then 2. doing the necessary changes to the
table to reflect the visual changes. As far as the tree is concerned only the node
I'm moving has a change and that would be ParentID in the table, but because I use
the ml_Path field a lot, I need to update this field, too. (Oh yeah, the root_id,

But all this is really extraneous to this Posting issue, which with this latest
change has nothing to do with ml_path.

> If this "path node" (ml_path) is
> meaningful in some way (from the POV of data structure) then how is this
> "meaning" implemented in the table structure? (My thoughts on that - "with
> great difficulty".)

The ml_path is not so much a table structure as a tree structure mapping of the
particular record's description field in the tree. Like I said it is a string of
the record ids from the root to the item, each record id is the parent to the next
lower, or to the right in the string, item. (eg '.456.7890.34.123.' where 456 =
ml_id of the root item in the tree branch, and 123 = ml_id of the actual item). Its
very helpful information to have without the need to create it for say visually
displaying specific nodes in the tree. There is no easy way to find a particular
item in the tree without knowing the path to that item. One could either go through
the tree or move through the table, going from ml_id then searching for the ml_id
that = the parent_id and so on up the tree. Having everything in a string is faster
and easier to deal with in reports and searches, that's all. Albeit, there is this
overhead to maintain it. Procedures are still new to me and I would not know how to
do this there, so I did what I do know. I'm not sure if a procedure to get this
path would be slower than just having the information in the table record.

> Structure aside - transaction settings are going to make differences to
> what one user can achieve in a multi-user setup, but treeviews are not a
> suitable vehicle as an interactive editing interface in a multi-user
> environment.. If User A has an update pending on a certain record, then
> User B is going to encounter a conflict trying to update any part of that
> "family". Any single row involved is itself also involved in other
> dependencies. Hence, while tree structures are great for reads, for an
> editing interface their beauty is far outweighed by their capacity to up
> tie up large networks of interrelated rows with locks and waits.

See above. The treeview is essentially a read structure in my app. In any case this
is not a multiuser app.

> Do you see yet that the slowness isn't a separate issue?

No, I don't see that. I just don't understand why posting to a single record should
be so slow, unless I really messed up in some basic setup.

> You'll have to explain what it is that you think is "the slow post issue".

Does 5-10 seconds to Post seem normal to you? By this I mean, when I trace through
the code when I get to the line Post;, it sits there for that long before moving to
the next line. That seems really slow to me.

Thanks again,