[solved] best way to move a query cursor back to *near* where deleted id was

[codifies]

so the query has run its delete query say for example

delete from acme where acmeid=101


say in the table there are id's 99 and 103 (amongst many others!) I want the cursor to end up at either of these id's (not really bothered which just so long as the cursor is *somewhere* near to where is was

[codifies]

assuming the variable id holds the old deleted id....

Code: [Select]

    query:='SELECT peopleid FROM people ORDER BY ABS('+ id + ' - peopleid) limit 1';   
    DataModule1.scratchQuery.close;
    DataModule1.scratchQuery.SQL.Clear;
    DataModule1.scratchQuery.SQL.Text:=query;
    DataModule1.scratchQuery.open;
    DataModule1.QueryToMove.Locate('peopleid' ,  DataModule1.scratchQuery.FieldByName('peopleid').AsVariant,[]);
    DataModule1.scratchQuery.close;   

[ludob]

That looks like a very complex way of doing things.
Can't you just do a QueryToMove.Next or QueryToMove.Previous before deleting the record to fetch peopleid from the data already in memory? Always faster than doing an additional query.

The ORDER BY ABS('+ id + ' - peopleid) is a nice trick to find the one closest record but is a lot of server calculations if the table is large. Furthermore, since id will change, the server won't be able to cache intermediate results. Since you are not interested in which one is selected the following would be much faster, easier to read and cache friendly:

Code: [Select]

SELECT peopleid FROM people WHERE peopleid>'+ id + ' ORDER BY peopleid limit 1 with the caveat that you need to do a QueryToMove.Last if the result is empty (id deleted was last record).

[codifies]

if you do previous or next then delete aren't you deleting the wrong record? or am I misunderstanding you

I take your point about just searching for greater than, but calling ABS in a query over complex is stressing thing a little far.... how fast can people select a record and then click a delete button? performance in this case is somewhat of a moot point... and you still need the extra code to check for the last record.

[BigChimp]

Can't you use

• test for .bof (or similar property); if yes: moveprevious; if no, check .eof and do .movenext
• get the id, store it
• do a movenext (or moveprevious, or nothing) - (or fiddle with bookmarks if that what they're there for; never used them)
• perform the .delete
• do the locate with the id you saved, or nothing if both .bof and .eof where previously true.

Or is that too naive?

[ludob]

if you do previous or next then delete aren't you deleting the wrong record? or am I misunderstanding you

If you do DataModule1.QueryToMove.Delete then you don't need to do anything. The cursor moves to the next record. In the worst case (older fpc perhaps) you could do a GetBookmark and do a GotoBookmark after a delete which is much faster than a locate. So I assumed you where doing a delete with a DELETE sql statement and a refresh afterwards. So you would take the id to delete, do next or previous and take the id to locate after DELETE and refresh.

I take your point about just searching for greater than, but calling ABS in a query over complex is stressing thing a little far.... how fast can people select a record and then click a delete button? performance in this case is somewhat of a moot point... and you still need the extra code to check for the last record.

If you are the only user of the database then there is no problem. I'm only signalling this as something to watch out for when using this in multi-user apps with databases that grow over time. Innocent looking queries like this can cause headaches later on.
BTW the ABS is not over complex it is all the side effects you need to watch out for. If peoplid is an index, the sort is at no cost and the where is very fast. Abs on id- peopleid will run over all records to calculate the result.

[codifies]

@ludob I think you missed the point of what I was doing (and I probably didn't make it too clear) I'm deleting from one query and refreshing a different query which is also using other tables as well as the people table, obviously when you do a refresh you loose the cursors position...

even if you were to next on the other query and record the ID for use after the delete you'd still need a corner case for the last record...

While your advice to keep an eye on random queries is pertinent in situations where you are doing large batches of queries, when you're messing around with cursor positions its almost always for user interface reason, which by definition is low volume, it really is ok to allow SQL logic to take some of the strain.

 Just supposing you had the unlikely situation of two hundred clients all deleting records at (even exactly) the same time do you think there would really be much difference in terms of performance?

@BigChimp you *could* do all that or just run a single query and a single locate...

There is no "right" or "wrong" solution to this and many similar problems like this - but it always is worth discussing the merits of different approaches...

[ludob]

@ludob I think you missed the point of what I was doing (and I probably didn't make it too clear) I'm deleting from one query and refreshing a different query which is also using other tables as well as the people table, obviously when you do a refresh you loose the cursors position...

So my assumption was right from the beginning 

even if you were to next on the other query and record the ID for use after the delete you'd still need a corner case for the last record...

Code: [Select]

if DataModule1.QueryToMove.eof then
  DataModule1.QueryToMove.Previous
else
  DataModule1.QueryToMove.Next;
IDtoLocate:=DataModule1.QueryToMove.FieldByName('peopleid').AsInteger;Looks easier than doing a new query I would say.

While your advice to keep an eye on random queries is pertinent in situations where you are doing large batches of queries, when you're messing around with cursor positions its almost always for user interface reason, which by definition is low volume, it really is ok to allow SQL logic to take some of the strain.

 Just supposing you had the unlikely situation of two hundred clients all deleting records at (even exactly) the same time do you think there would really be much difference in terms of performance?

You are 100% right. This query alone will not bring your app to its knees. But let me put it bluntly: people that are not constantly looking at the performance of all their queries (and other code for that matter) will sooner or later end up with problems in large apps. And it will not be one query that is causing the performance problem but a lot of inefficient or needless queries. Take this example: the location query can be replaced by a few lines on the client with the additional advantage that this scales very well with an increasing number of clients:  20 clients * zero impact on server is still zero impact.

@BigChimp you *could* do all that or just run a single query and a single locate...

There is no "right" or "wrong" solution to this and many similar problems like this - but it always is worth discussing the merits of different approaches...

What BigChimp outlined is above piece of code. His point 3 is because of the confusion you created by writing that the next or previous would delete the wrong record and therefore suggesting that you used the tdataset.delete.

[codifies]

I better see now from your code fragment what you mean now

While I can't do the delete on the querytomove I can do the next/previous and record the ID from it

Code: [Select]

    if DataModule1.f1PeopleQuery.eof then
      DataModule1.f1PeopleQuery.Prior
    else
      DataModule1.f1PeopleQuery.Next;
    // find an ID near the deleted ID
    id:=DataModule1.f1PeopleQuery.FieldByName('peopleid').AsInteger;

    DataModule1.f2peopleQuery.delete;
    DataModule1.f2peopleQuery.ApplyUpdates;

    DataModule1.f1PeopleQuery.refresh;
    // refreshing looses our position
    DataModule1.f1peoplequery.Locate('peopleid', id ,[]); 

this is what I ended up with

thanks for your patience