Tim

>MoveBy is not controlled by us, so there's nothing we can do about the way
>it is implemented. However, a few hundred rows is no problem - you'd have
>to get into the tens of thousands before you'd see any noticeable slowdown,
>unless you're talking a remote session over a very slow connection.

I beg to differ The TMS DBgrid uses MoveBy to detect how many rows and the difference between that and the .RecordCount I replace it with is noticeable.

><< So good try but I don't think bookmarks will work. Storing a bookmark as
>part of the node data would be a great way to return quickly though, but I
>can never remember what invalidates a bookmark. >>
>
>It doesn't matter whether the bookmark is valid or not, it will still put
>you on the closest match, and that's all that matters when you're talking
>about navigation on a table or sensitive result set. Other sessions are
>possibly changing the data underneath, so what you see at any given moment
>is only a snapshot in time.

That's something I didn't know, but if you want to load / edit a specific row the "closest match" isn't what's wanted.

><< The other part of the problem is you can't just use an index because it
>wouldn't be in sequence. >>
>
>Bookmarks work in EDB regardless of whether the table has an index, or what
>order the index is in. And all indexes in EDB are ordered by their
>insertion order if they are not unique. For example, if you had an index on
>State in a table, and you have lots of 'NY' rows, then all of the 'NY' rows
>would be ordered by the State first, and by the insertion order second.

I was referring to the fact that Ulrich couldn't just use an index because of what he's wanting to do rather than how bookmarks work.

Roy Lambert [Team Elevate]

What I'm trying to accomplish is actually quite simple:

I have a table that logs what a user does with a device, so the columns
are (simplified): user_id, device_id, message, and time.

The tree structure is only utilized to determine how to narrow down the
amount of data returned. There are three levels: first is the user,
second the device, and third the actual log messages.

So by expanding the first level, it shows all the devices a user has
interacted with, and expanding a particular device will list all log
messages for that user/device combo.

Level one and two are pretty straightforward, but level three is what I
was asking about.

Ideally, once you expand down to the device level, I would simply apply
a range with the chosen user + device to the log table and then would be
able to use the RecNo to index any one particular row of this dataset.

Since VST inits only the nodes that are visible, I need a method of
positioning the row pointer at the row that VST needs the data for.  For
example, lets say I had 4000 log entries.  If I expand the device node,
VST will init only the first 15 or so nodes (depending on the number of
entries visible on screen).  If I hit the END key, VST moves to the end
of the list and inits the last 15 entries.  This method of
initialization doesn't require me to retrieve the data for all 4000
nodes up front, forcing the user to wait until the database access is
finished.

Ulrich

Ulrich

I think I can see a way to get near to what you want, but you need to start thinking sql.

I'm assuming that each layer is populated with the data from the table in its text property.

To populate the top level

SELECT DISTINCT user_id FROM logtable

To populate the next level when a top level is populated - you'll want a parameterised query

SELECT DISTINCT device_id FROM logtable WHERE user_id = :nodetext

Final layer - again a parameterised query.

SELECT message, time FROM logtable WHERE user_id = rentnodetext AND device_id = :nodetext

I'm assuming here the 4000 log entries so with the divisions it should be fine. If not investigate the RANGE clause - its almost ideal for this situation.

I just tried this select distinct _whofrom from mandn on a 225k record table - 9925 rows were returned in 6.12secs

another table select distinct _fkmarkets from companies 6k records 66 rows returned  .063 secs


Roy Lambert [Team Elevate]

Roy,

<< I beg to differ The TMS DBgrid uses MoveBy to detect how many rows
and the difference between that and the .RecordCount I replace it with is
noticeable. >>

Well, I can't really comment on the internals of TMS's grid.  However, there
are ways to improve it's efficiency that may not be in place in their code,
such as disabling data-aware control updates.

<< That's something I didn't know, but if you want to load / edit a specific
row the "closest match" isn't what's wanted. >>

Yes, but that is what the BookmarkValid method is for.  It positions the row
to the specified bookmark, or the closest one if it doesn't exist, and
returns a Boolean indicating whether it found the exact same row or not.  In
addition, displaying values in a treeview does not tolerate exceptions, etc.
during display, so you either position on the next closest one or have an
unworkable solution.  This is how all grids work - navigation/display is a
much more inaccurate science than editing, and the two are completely
different situations.

<< I was referring to the fact that Ulrich couldn't just use an index
because of what he's wanting to do rather than how bookmarks work. >>

Ulrich can't really do what he wants *without* bookmarks.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ulrich,

<< Since VST inits only the nodes that are visible, I need a method of
positioning the row pointer at the row that VST needs the data for.  For
example, lets say I had 4000 log entries.  If I expand the device node, VST
will init only the first 15 or so nodes (depending on the number of entries
visible on screen).  If I hit the END key, VST moves to the end of the list
and inits the last 15 entries.  This method of initialization doesn't
require me to retrieve the data for all 4000 nodes up front, forcing the
user to wait until the database access is finished. >>

Which is fine, but you need to at least understand that you cannot use
anything but bookmarks in EDB for random row positioning in a dataset, so
you're going to have to come up with a solution that utilizes bookmarks in
order to make it work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Which is fine, but you need to at least understand that you cannot use
>anything but bookmarks in EDB for random row positioning in a dataset, so
>you're going to have to come up with a solution that utilizes bookmarks in
>order to make it work.

How do you get a bookmark BEFORE you visit a record? Unless I'm totally misreading Ulrich's posts the reason for the recno jump is to load the log data onto the node, once its loaded there's no need to go back to the record so unless you can calculate the bookmark before you get to the record it won't work.

Roy Lambert [Team Elevate]

Ulrich
Another thought occurred to me, unless you're stuck with the treeview approach due to space on the form - what about using three grids and three table components linked with master-detail relationships?

Roy Lambert [Team Elevate]

Roy,

<< How do you get a bookmark BEFORE you visit a record? Unless I'm totally
misreading Ulrich's posts the reason for the recno jump is to load the log
data onto the node, once its loaded there's no need to go back to the record
so unless you can calculate the bookmark before you get to the record it
won't work. >>

If you visit the row to load the node data, then you have the bookmark.
IOW, if he wants to load the first 15 rows, then he also loads the first 15
bookmarks.  To get the next rows, he simply navigates to the bookmark for
the last loaded node, and proceeds from there.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>If you visit the row to load the node data, then you have the bookmark.
>IOW, if he wants to load the first 15 rows, then he also loads the first 15
>bookmarks. To get the next rows, he simply navigates to the bookmark for
>the last loaded node, and proceeds from there.

I see where you're coming from now. But in that scenario, unless he's moved off the last record, it would be easier just to go .Next and keep on.

I did have a play around trying to use RUNSUM(1) but it needs a GROUP BY clause for some reason. Shame cos it would have probably worked. The only other thing I can think of now is to create a memory table, add a counter on and index it. But I have a sneaky suspicion that that would be to slow for Ulrich up front.

Roy Lambert [Team Elevate]

Tim


Just trying it again,

select runsum(1),_Name from companies

ElevateDB Error #700 An error was found in the statement at line 1 and column 18 (Invalid expression "_Name" found, the necessary GROUP BY clause is missing)

Reading the manual I can't reconcile what I'm reading with the effect I'm getting. I think <<The selected rows can be grouped into logical sub-sets by using the GROUP BY...>> should read "The selected rows MUST be grouped into logical sub-sets by using the GROUP BY..." together with something about if you only have a runsum column it'll consider the whole select clause as its group and you end up with one number.

I then thought I'd cheat select runsum(1),_Name from companies group by _name - nearly gave myself a heart attack 5244 rows affected - last runsum 5941 - took me a while to realise I had a lot more duplicate company names than I would have guessed

Roy Lambert