[solved] best way to move a query cursor back to *near* where deleted
id was
lazarus / 16-Databases

----------------------------------------------------------------------
codifies · 2012-09-15 01:58

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 · 2012-09-15 10:53

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 · 2012-09-15 12:27

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 · 2012-09-15 13:07

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 · 2012-09-15 13:16

Can't you use
test for .bof (or similar property); if yes: moveprevious; if no,
check .eof and do .movenextget the id, store itdo a movenext (or
moveprevious, or nothing) - (or fiddle with bookmarks if that what
they're there for; never used them)perform the .deletedo the locate
with the id you saved, or nothing if both .bof and .eof where
previously true.
Or is that too naive?

----------------------------------------------------------------------
ludob · 2012-09-15 13:42

Quote from: chris_c on September 15, 2012, 03:07:39 pmif 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.

Quote from: chris_c on September 15, 2012, 03:07:39 pmI 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 · 2012-09-15 14:36

@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 · 2012-09-15 14:58

Quote from: chris_c on September 15, 2012, 04:36:00 pm@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 

Quote from: chris_c on September 15, 2012, 04:36:00 pmeven 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.

Quote from: chris_c on September 15, 2012, 04:36:00 pmWhile 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.

Quote from: chris_c on September 15, 2012, 04:36:00 pm@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 · 2012-09-15 15:29

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