Hi,

I've inherited some software that I'm trying to improve the efficiency of.

In this particular case, there is a master table, and a child/detailed
table.

The detailed table has a field for 'Line No' which is unique to each
entry, and should not exceed the number of records in the table.

ie:

Parent Index; LineNo; Value

Parent1, LineNo 1, A
Parent1, LineNo 2, B
Parent1, LineNo 3, C
Parent1, LineNo 4, D
Parent2, LineNo 1, A
Parent2, LineNo 2, B
Parent2, LineNo 3, C
Parent2, LineNo 4, D

.... etc.

The LineNo field is not a unique index - but they shouldn't double up
for a particular record.

The code is written that if a line is deleted, that it goes through the
records and resequences them for that particular Parent.

ie, if I delete LineNo 2 in Parent 1 - it would reindex to show:

Parent1, LineNo 1, A
Parent1, LineNo 2, C
Parent1, LineNo 3, D

It's not overly important how it's resequenced, provided that the line
numbers go from 1 to the total number of records. (This is a requirement
with another software that the data communicates between).

I was just wondering, is there a efficient solution in which to
accomplish this.

At present, the software is coded similar to:

DetailTable.first;
recno := 1;
while not DetailTable.eof do
 begin
 DetailTable.Edit;
 DetailTableLineNo.value := recno;
 DetailTable.Post;
 Inc(RecNo);
 DetailTable.next;
 end;

When you're talking significant amounts of records, this uses up some
bandwidth (especially since the software is running on a network, and
doesn't use Client/Server mode).

I was hoping that there would be a simple SQL statement that could be
executed to update in one hit?

Best Regards

Adam.

"Adam H." <ahairsub5@removeme.jvxp.com> wrote in message
news:33D6D46F-8D27-4639-96CE-642F05079222@news.elevatesoft.com...
>
> At present, the software is coded similar to:
>
> DetailTable.first;
> recno := 1;
> while not DetailTable.eof do
>  begin
>  DetailTable.Edit;
>  DetailTableLineNo.value := recno;
>  DetailTable.Post;
>  Inc(RecNo);
>  DetailTable.next;
>  end;
>
> When you're talking significant amounts of records, this uses up some
> bandwidth (especially since the software is running on a network, and
> doesn't use Client/Server mode).
>
> I was hoping that there would be a simple SQL statement that could be
> executed to update in one hit?
>

Hi Adam

I don't see how doing with a simple SQL will work any faster than what you
already have.

If you were C/S mode, yes, you could offload onto the server and reduce
network traffic, but in file server mode you will always have to get each
whole record over the network, edit it and save the whole record back.

Just my opinion.

Cheers

Jeff

On 1/26/2014 7:41 PM, Adam H. wrote:
> The code is written that if a line is deleted, that it goes through the
> records and resequences them for that particular Parent.
>
> I was hoping that there would be a simple SQL statement that could be
> executed to update in one hit?
>

If maintaining the order is not important then i would just update the
line with the highest LineNo (for given parent id) and set it to the
deleted LineNo (logically "moving" it from last position to fill-in the
deleted position).

This would result in 1 delete and 1 update total. SQL would be very
straightforward or you can do it with navigational code - just edit the
last line only.

As to whether it's any faster - you'd have to test.

Raul

Hi Jeff,

> Hi Adam
>
> I don't see how doing with a simple SQL will work any faster than what you
> already have.

You could be right... I wonder if I wrap the whole process in a
transaction if that would make it any faster.

I just figured that this sort of resequencing might have been a common
thing, and there may have been a standard way of doing it that I've
missed, but I guess not.

Thanks for your help!

Adam.

Hi Raul,

> If maintaining the order is not important then i would just update the
> line with the highest LineNo (for given parent id) and set it to the
> deleted LineNo (logically "moving" it from last position to fill-in the
> deleted position).
>
> This would result in 1 delete and 1 update total. SQL would be very
> straightforward or you can do it with navigational code - just edit the
> last line only.
>
> As to whether it's any faster - you'd have to test.

That's a good suggestion, but unfortunately I believe that the order is
important to the client.

I may have to think more laterally to try and get around the issue at hand.

Thanks again for your suggestion

Adam.

"Adam H." <ahairsub5@removeme.jvxp.com> wrote in message
news:449EA82F-94FF-4597-9B02-C2B62F54E6D8@news.elevatesoft.com...
>
> You could be right... I wonder if I wrap the whole process in a
> transaction if that would make it any faster.
>

Hi Adam

Never used them myself, but perhaps Cached Updates is what you want.  In
http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&page=1&msg=11980#11980
(which is posting relating to speeding stuff up), Tim suggests

"Using cached updates helps in situations where you want to bring over a set
of rows, edit them, and then send back the edits, such as in a master-detail
situation."

Having read that, I'll be looking out for a place to use it in any new code
as I currently usually load into temporary tables, modify and post back.

Cheers

Jeff



--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Hi Jeff,

> Never used them myself, but perhaps Cached Updates is what you want.  In
> http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_general&page=1&msg=11980#11980
> (which is posting relating to speeding stuff up), Tim suggests

Cached updates rings a bell. Not sure if I played with them in the BDE -
but that is definitely worth a try. Thank you!

Cheers

Adam.