0

I have a program that must set up a more less complex screen after the user goes to a certain record in a dataset (TUniQuery). The user can jump on many ways: a combobox, search box and finally a DbNavigator. Going from the first to the last record takes forever. After following program execution I discovered that pressing the last button on the DBNavigator causes each record on the dataset to be visited, and so, on each one, the screen is uselessly built. Same from any record going to the first or last. It was my understanding, that such methods (First and Last) will do a direct jump. Maybe is a particular behavior on the Unidac components, but I can´t find any reference nor property to modify it. Currently, I´m planning to set a flag on the BeforeScroll event, but since AfterScroll also happens after each one, I can´t learn when the dataset has ended scrolling. I can´t neither find any reference on Delphi documentation that simply states
Call Last to make the last record in the dataset active

alvaroc
  • 433
  • 5
  • 14
  • Is it a multi-user app? Retrieving too many rows, which it sounds like you may be, is a user-hostile thing to do. – MartynA May 26 '20 at 17:05
  • The query and database often determine if you can jump around like that. Try using a TUniTable or if you have to use a TUniQuery make the query simple and define the key fields etc. – Brian May 26 '20 at 18:31
  • There can't be a *direct jump* if you have more rows in your query resultset than what fits in the row buffer. After the end of the buffer is reached, another set of rows is requested from the server, and then it is processed until the end of the buffer is reached, at which point the process is repeated again and again until the last row is reached. You typically deal with this by limiting the number of columns being retrieved by using a specific SELECT column list instead of `SELECT *` and by limiting the number of rows using a `WHERE` to restrict them. – Ken White May 26 '20 at 20:59
  • You may be able to work around it by disabling your before and after scroll events before calling `First` or `Last`, and then restore them afterward (in a `finally` block). – Ken White May 26 '20 at 21:01
  • Not possible Ken. First and Last are called by a TDBNavigator. In this case there are few rows and the direct jump is possible since all records (four specifically) have been fetched – alvaroc May 27 '20 at 03:10

1 Answers1

0

Following the source code I found this:
Short answer: Yes, a TDataset jumps directly to the first and last records when possible but...

Long answer: Delphi has internal data events (DB unit, TDataEvent) that are broadcasted so data aware controls can update properly. Methods like Next, Prior and many others use function MoveBy, which generates a Before/AfterScroll events and also internal events. MoveBy may fetch some extra rows if needed. Let's say you have twenty rows fetched from a thirty rows dataset. Your current record is fifteen. You call MoveBy(2). As expected, your new record is seventeen. In this case the following occurs:

  1. BeforeScroll
  2. ActiveRecord is incremented twice (in a loop) but no rows are fetched
  3. internal DataEvent: deDataSetScroll
  4. AfterScroll

Note that no program events occur while scrolling. BeforeScroll occurs on record fifteen and AfterScroll on seventeen.

Now, same scenario, but this time MoveBy(10) is called. Five rows are already fetched (20 - 15) and five more need to be retrieved. In this case the sequence is:

  1. BeforeScroll
  2. ActiveRecord is incremented by five (in a loop) but no rows are fetched
  3. Active record is incremented by five, in the same loop. On each cycle, a record is fetched (no program events occur)
  4. internal DataEvent: deDataSetChange
  5. AfterScroll

Every thing runs fine. The problem raises with the Last method that does not use MoveBy and assumes that records were always fetched: 

procedure TDataSet.Last;  
begin  
  CheckBiDirectional;  
  CheckBrowseMode;  
  DoBeforeScroll;  
  ClearBuffers;  
  try
    InternalLast;  
    GetPriorRecord;  
    GetPriorRecords;  
  finally  
    FEOF := True;  
    DataEvent(deDataSetChange, 0);  // Problem! Causes TJvDBSearchComboBox to retrieve all records (again)
    DoAfterScroll;  
  end;  
end;

As you can see, Last method always broadcast a deDataSetChange event even when all records have been fetched. In my particular case of four records, the user presses the last button of a TDBNavigator which in turn calls Last method. The event deDataSetChange is broadcasted, and then a TJvDBSearchComboBox (from JVCL library) reacts to this event by visiting each row with a "while not eof...next" loop to update its contents, and generating Before/AfterScroll events.

I love Delphi, but is one of the things I don´t like about datasets. There is no way for a program to learn if certain data event was triggered due to a user action, or the program itself. I have suggested in Delphi presentations, for a way to travel through a dataset without events been triggered (as MoveBy does) and then, components like TJvDBSearchComboBox could use it, or at least a flag or the like in the dataset that could be turned on before starting to visit each record. Also, Last should check if records were indeed retrieved.

Some will says that's easy to implement by myself, and it is indeed when your code scrolls through the dataset. But in the case of a TDBNavigator and TJvDBSearchComboBox I have no control over them. Some other will say don´t use JVCL, There's no easy answer here. Also, FYI, First method exhibits the same behaviour.

alvaroc
  • 433
  • 5
  • 14