mORMot Open Source

ken

Member

Registered: 2016-07-15

Posts: 9

TSynDBDataSet raise error "insufficient memory for this operation"

Code:

procedure TForm1.Button1Click(Sender: TObject);
begin
  with TSynDBDataSet.Create(Self) do
  begin
    Connection := fProps; // connect sql server2008
    CommandText := 'Select * From LogInfo'; // LogInfo has more then 100000 rows.
    IgnoreColumnDataSize := true;
    Open; // raise error: insufficient memory for this operation.
  end;
end;

But i use TADOQuery or TFDQuery. It's OK.For example:

procedure TForm1.Button1Click(Sender: TObject);
begin
  with TADOQuery.Create(Self) do
  begin
    Connection := fADOCon;
    SQL.Text := 'Select * From LogInfo';
    Open; // It's OK
  end;
end;

Last edited by ken (2017-06-22 10:22:50)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,667

Website

Re: TSynDBDataSet raise error "insufficient memory for this operation"

SynDB DataSet works differently than other queries.
TSynDBDataSet does not behave like a cursor over data, with multiple back & forth to the database engine.
TSynDBDataSet will retrieve ALL the data from the server, then give you access to its rows.

Advantages of such a behavior is that the low-level statements are released much quicker, and that no cursor is to be maintained on the server side.
If, in the future, you want to convert your monolithic 2-tier application in to a n-tier SOA design (which is what I strongly advocates, as most software professionals), you will definitively want to reduce the number of opened cursors on your DB server.

So in your case, you should use SQL paging, and retrieve only the needed rows.
Adding a WHERE clause over an indexed field of the table is the way to go.