How to gradually feed a memory table. Best way for do this

[delagoutte]

i have database query that could be very long in some case.

for example, we can say that the query return a result after 60s (so it is too long for a good user experience on web) the query could be like that :

select * from MY_TABLE WHERE MYDATE BETWEEN '01.01.2017' and '01.01.2018'

the think that i would do : cut main query in sub query.

 

i think i could do a think like this :

use a grid linked to a TFDMemtable.

execute a first query in a TFDQuery  : select * from MY_TABLE WHERE MYDATE BETWEEN '01.01.2017' and '08.01.2017'

-> add result of fdquery to the FDMemTable

execute a second query in a TFDQuery  : select * from MY_TABLE WHERE MYDATE BETWEEN '09.01.2017' and '16.01.2017'

-> add result of fdquery to the FDMemTable

.....

so with this method i gradually feed the FDMemTable

Now my problem is : what is the best way to do this with unigui and show new data in client side when subquery are finished.

what is the best way ?

- unithreadtimer for execute sub query with a unitimer for sync client side ? (like in unithreadtimer-2 and 3 sample)

-unisession.synchronize after add record in fdmemtable ?

- other method ?

 

could you give me your opinion  ?

have you ever done that and what approach did you use to code it ?

 

 

more info, maybe it will be more clear with code :

var curdate : TDate;
begin
  curdate := IncMonth(now,-36);
  UniMainModule.FDQuery1.Close;
  UniMainModule.FDQuery1.SQL.Text :=  'SELECT MyField1, MyField2, MyField3 FROM mytable WHERE Field_DATEPUB BETWEEN :DATE1 AND :DATE2';

  while (curdate <now)  do
  begin
    UniMainModule.FDQuery1.ParamByName('DATE1').AsDate := curdate;
    UniMainModule.FDQuery1.ParamByName('DATE2').AsDate := IncMonth(curdate,1);
    UniMainModule.FDQuery1.Open;
    if UniMainModule.FDMemTable1.FieldDefs.Count=0 then
    begin
      unimainmodule.CloneQueryFieldsToMemTableField(UniMainModule.FDQuery1, UniMainModule.FDMemTable1);
      UniMainModule.FDMemTable1.CreateDataSet;
    end;
    UniMainModule.FDQuery1.First;
    while not UniMainModule.FDQuery1.Eof do
    begin
      UniMainModule.FDMemTable1.Append;
      UniMainModule.CloneQueryDataToMemTableData(UniMainModule.FDQuery1, UniMainModule.FDMemTable1);
      UniMainModule.FDMemTable1.Post;
      UniMainModule.FDQuery1.Next;
    end;
        UniSession.Synchronize;//<<---- here i want that the temp result was sended to the client but synchronize don't work in this case
    sleep(1000);

    curdate:= IncMonth(curdate,1);
  end;

end;

 

[GerhardV]

The following link has some discussion on that...

Paging Through Results

[delagoutte]

it is not really my problem  but i advance

i do a little test case and i use this code :

procedure TMainForm.UniButton1Click(Sender: TObject);
var curdate : TDate;
cnt : Integer;
  FS : TUniGUISession;
  B : TBookmark;
  havenewrec : Boolean;
begin
  curdate := IncMonth(now,-36);
  UniMainModule.FDQuery1.Close;
  UniMainModule.FDQuery1.SQL.Text :=  'SELECT AO_KEYID, AO_REF, AO_DATEPUB FROM TB_AO WHERE AO_DATEPUB BETWEEN :DATE1 AND :DATE2 order by ao_datepub asc';
  cnt := 0;
  while (curdate <now)  do
  begin
    havenewrec := false;

    UniMainModule.FDQuery1.close;
    UniMainModule.FDQuery1.ParamByName('DATE1').AsDate := curdate;
    UniMainModule.FDQuery1.ParamByName('DATE2').AsDate := IncMonth(curdate,1);
    UniMainModule.FDQuery1.Open;
    try
      B := UniMainModule.FDMemTable1.GetBookmark;
      try
        if UniMainModule.FDMemTable1.FieldDefs.Count=0 then
        begin
          unimainmodule.CloneQueryFieldsToMemTableField(UniMainModule.FDQuery1, UniMainModule.FDMemTable1);
          UniMainModule.FDMemTable1.CreateDataSet;
        end;
        UniMainModule.FDQuery1.First;
        while not UniMainModule.FDQuery1.Eof do
        begin
          havenewrec := True;
          UniMainModule.FDMemTable1.append;
          UniMemo1.Lines.Add(UniMainModule.FDQuery1.FieldByName('AO_REF').asstring);
          UniMainModule.CloneQueryDataToMemTableData(UniMainModule.FDQuery1, UniMainModule.FDMemTable1);
          UniMainModule.FDMemTable1.Post;
          UniMainModule.FDQuery1.Next;
          Sleep(5);
          Inc(cnt);
        end;
        if not Assigned(B) then
        UniMainModule.FDMemTable1.First
        else
        UniMainModule.FDMemTable1.GotoBookmark(B);
      finally
        UniMainModule.FDMemTable1.FreeBookmark(B);
      end;
    finally
     if havenewrec then

      UniSession.Synchronize(true);
    end;
    curdate:= IncMonth(curdate,1);
  end;

end;

procedure TMainForm.UniDBGrid1SelectionChange(Sender: TObject);
begin
  Self.Caption := UniMainModule.FDMemTable1.FieldByName('AO_KEYID').AsString;
end;

i add synchronize (true)

on grid , i disabled editing and disabled load mask

 

On this gif, you can see many things like the data arrived gradually but on each click on row i must have the value of first column in form caption and you can see that don't work when the data are loaded, the click have no effect. propably because grid always load data.

how can i do for having a user experience more fluid ?

 

 

[Abaksoft]

Hello Delagoute,

I have a Table with 100 000 records.

My select take : 250 ms  !   (GetTickCount)

Using devart IBDac.

With :

• Fetch All = False

• Search by Query : Where ()

• Displaying only the 100 first :  rows 100 

(Never using Filter in big web application).

Isn't enough ?

Salutations

[Freeman35]

Why you need all record sent to client side?

For MemTable, don't forget added records need to memory and all of them on server and times with session count. This mean can kill server memory.

EndUser can not see all rows in the same time, you can add filter and/or search parameter on form.

You can add "select count...." query and set grid's pagecount value. and close/open grid's dataset. My way is, add refresh button on form. When need refres dataset, increase count and show badge text in button. If EndUser want to refresh click this. This ways, less works on server and data trafic.