🚀 go-pugleaf

Mitch wrote:
>
> I need to enable my users to easily specify (and re-specify) a
> position for each row relative to the others above and below it. I 
> therefore
> can't use the primary key or other columns to sort by. I must use an
> extra integer column where the sequence is defined by the integer.
>
> I am using the Developer Express grid to drag and drop the rows and
> they have given me code which (upon an insert or drag) automatically
> recalculates the sequence in the grid, often for all the rows in the
> dataset.
>
> This is done on a row by row basis, so the process is very very slow
> and gets slower as the number of rows in the dataset increases. Also,
> in a multi-user situation I suspect this will be unworkable.

Multiple users can compete on trying to order these rows? There's no good 
solution for that part.

> Does anyone have any suggestions for accomplishing this in a way which
> numbering is automatic for the user, but which maintains the
> performance and efficiency of a database with > 10,000 rows.

Use a grid that is not directly attached to the dataset. Could be a 
stringgird, drawgrid or listview. When the dataset is opened, load the rows 
into a list of your own objects and use these to display (I strongly 
recommend a drawgrid or virtual mode listview as this avoids copying data 
yet again to display it).

When a user moves an item, you simply need to move that object in the list.

When the user says he's done, you simply loop through the list *once* 
updating the sequence numbers in the database. Using a loop with a 
parameterized update statement within a single transaction should provide 
decent performance.

-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Some see private enterprise as a predatory target to be shot, others
as a cow to be milked, but few are those who see it as a sturdy horse
pulling the wagon." - Winston Churchill