#1 2015-07-10 07:58:39

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

free objects on the server side

Hello,

I have a service on the server:

  IMessartService = interface(IInvokable)
    procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  end;
  
  ...
  ...
  
  procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  begin
    aMessarts := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;  

Now, I want to retrieve the data per http-request: (data amount is about 300 000 rows from external database)

http://server:1234/root/messartservice/SelectByManufacturer?aManufacturer=qwerty

If I do so, the size of the mORMot-Server-Exe is getting bigger and has about 500 MB. And each request increases the EXE-size.

How should I do the memory management in this case?

My service is defined as ClientDriven.

I coudn't find any information about that in the documentation and in froum :-(

Last edited by cypriotcalm (2015-07-10 08:00:14)

Offline

#2 2015-07-10 08:39:32

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 105

Re: free objects on the server side

Simple call aMessarts.Free after use?

Offline

#3 2015-07-10 08:45:16

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

zed wrote:

Simple call aMessarts.Free after use?

If it would be so simple ;-) after the call .free I get an "EInvalidPointer"-error :-(

Offline

#4 2015-07-10 10:56:17

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

On the server side, the aMessarts: TObjectList is released automatically, after each call:

function TServiceMethod.InternalExecute(Instances: array of pointer;
  ....
      for i := 0 to ArgsUsedCount[smvvObject]-1 do
        Objects[i].Free;
  ...

So the memory would not increase on the server side.

On the client side, you have to manage memory as usual:

var service: IMessartService;
     list: TObjectList;
begin
  ... fill service
  list := TObjectList.Create;
  try
    service.SelectByManufacturer('smith',list);
    ... use list[]
  finally
    list.Free;
  end;
end;

Offline

#5 2015-07-13 06:56:21

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

Thx for the answer, but I want to call my data per http-request, NOT in delphi. I want to retrieve the data for example in browser with uri

http://server:1234/root/messartservice/SelectByManufacturer?aManufacturer=qwerty

and I do this then I get the situation like stated in my first post. Please read it again!

Offline

#6 2015-07-13 10:48:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

There is no reason why the direct HTTP request may consume server memory.
The TObjectList is serialized in to a JSON response, this this JSON string is passed to the client, and released as soon as it has been sent.

Your SelectByManufacturer() method sounds fine.
I guess there is a leak somewhere else in your code.

Offline

#7 2015-07-13 11:51:54

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

ab wrote:

There is no reason why the direct HTTP request may consume server memory.
The TObjectList is serialized in to a JSON response, this this JSON string is passed to the client, and released as soon as it has been sent.

I don't understand then why the server-exe is getting bigger....

ab wrote:

Your SelectByManufacturer() method sounds fine.
I guess there is a leak somewhere else in your code.

I have these methods :-)


I initialize like follows:

  // SERVER
  TMessartService = class(TInterfacedObject, IMessartService)
  private
    function fRest: TSQLRest;
  published
    procedure SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  end;  

  function TMessartService.fRest: TSQLRest;
  begin
    Result := ServiceContext.Factory.Rest;
  end;  

  procedure TMessartService.SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  begin
    aMessarts := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;

  ...
  procedure TFrmMainServer.InitHTTPService;
  begin
    InitializeModel(Model, DBConnectionProperties);

    ServerDB := TSQLRestServerDB.Create(Model, ChangeFileExt(ExeVersion.ProgramFileName, '.db3'), False);
    ServerDB.CreateMissingTables;

    ServerDB.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

    // Messart service
    ServerDB.ServiceDefine(TMessartService, [IMessartService], sicClientDriven);

    ServerHTTP := TSQLHttpServer.Create('7979', [ServerDB], '+', useHttpApiRegisteringURI);
    ServerHTTP.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
  end;


  // CLIENT
  FModel := TSQLModel.Create([]);

  FClient := TSQLHttpClient.Create('serverhost','7979', FModel);

  FClient.ServiceDefine([IMessartService], sicClientDriven);

  if not FClient.Services['MessartService'].Get(FMessartService) then
    raise Exception.Create('Can''t get the service'); 


 procedure TFrmMainClient.Button1Click(Sender: TObject);
 var
   aMessartList: TObjectList;
 begin
   aMessartList := TObjectList.Create;
   try
     FMessartService.SelectByManufacturer('smith', aMessartList);
     ShowMessage('Done! '+IntToStr(aMessartList));
   finally
     aMessartList.Free;
   end;
 end;

This is my whole application! There is no more code!

Now, I have another problem. If I call on the client FMessartService.SelectByManufacturer('smith', aMessartList) I get an error:

   EInterfaceFactoryException: TInterfacedObjectFakeClient failed: returned object

If I change the code on the server like follows:

  procedure TMessartService.SelectByManufacturer(const aManufacturer: TName; out aMessarts: TObjectList);
  var
    LocalMessartList: TObjectList;
  begin
    LocalMessartList := fRest.RetrieveList(TMessart, 'ManufacturerID = ?', [aManufacturer]);
  end;

Everything is ok, the data is retrieved from the database. But I can't pass it back to the client :-(

What am I doing wrong?! It's really frustrating to have such bugs and the code is very simple! :-(

Thank you very much for your help!!!

Offline

#8 2015-07-13 12:35:33

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

How is an "exe" growing?
Please explain.
Did you try with the internal SQlite3 engine?

Register a T*ObjArray and use it instead of this TObjectList.

type
  TMessartObjArray = array of TMessart;

....
  TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(TMessartObjArray,TMessart);
...

procedure TMessartService.SelectByManufacturer(const aManufacturer: TName): TMessartObjArray;
begin
  fRest.RetrieveListObjArray(result,TMessart, 'ManufacturerID = ?', [aManufacturer]);
end;

Offline

#9 2015-07-14 07:56:36

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

"exe" memory is growing...
I call the service-function (http-request) which retrieves a big amount of data from the external database (MySQL/FireDAC). And on the server in the task manager the "exe" consumes more memory, in my case it has the size of 500MB after the first request. But in browser I get the data as JSON.


I have also rewritten the code as you have said, but now I get an access violation in TSQLRest.RetrieveListArray:

function TSQLRest.RetrieveListObjArray(var ObjArray; Table: TSQLRecordClass;
  const FormatSQLWhere: RawUTF8; const BoundsSQLWhere: array of const;
  const aCustomFieldsCSV: RawUTF8): boolean;
var T: TSQLTable;
begin
  result := false;
  if (self=nil) or (Table=nil) then
    exit;
  T := MultiFieldValues(Table,aCustomFieldsCSV,FormatSQLWhere,BoundsSQLWhere);
  if T<>nil then
  try
    result := T.ToObjArray(result,Table); <------- HERE access violation
  finally
    T.Free;
  end;
end;


function TSQLTable.ToObjArray(var ObjArray; RecordType: TSQLRecordClass=nil): boolean;
var R: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
    arr: array of TSQLRecord absolute ObjArray;
begin
  result := false;
  ObjArrayClear(ObjArray); <------- HERE access violation
  if self=nil then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  result := true;
  if fRowCount=0 then
    exit;
  R := RecordType.Create;
  try
    R.FillPrepare(self);
    SetLength(arr,fRowCount);        // faster than manual Add()
    Row := @fResults[FieldCount];   // Row^ points to first row of data
    for i := 0 to fRowCount-1 do begin
      arr[i] := RecordType.Create;
      R.fFill.Fill(pointer(Row),arr[i]);
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;


procedure ObjArrayClear(var aObjArray);
var i: integer;
    a: TObjectDynArray absolute aObjArray;
begin
  if a<>nil then begin
    for i := 0 to length(a)-1 do <------- HERE access violation
      a[i].Free;
    a := nil;
  end;
end;

Offline

#10 2015-07-14 12:23:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

How did you write TMessartService.SelectByManufacturer ?

Offline

#11 2015-07-14 12:49:51

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

ab wrote:

How did you write TMessartService.SelectByManufacturer ?

TMessartObjArray = array of TMessart;

function TMessartService.SelectByManufacturer(const aManufacturer: TName): TMessartObjArray;
begin
  ServiceContext.Factory.Rest.RetrieveListObjArray(Result, TMessart, 'ManufacturerID = ?', [aManufacturer]);
end;

Last edited by cypriotcalm (2015-07-14 12:55:43)

Offline

#12 2015-07-14 14:47:24

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: free objects on the server side

I can't get it.

function TSQLRest.RetrieveListObjArray() is Boolean, but you are using Result as a [absolute] TObjectDynArray in ObjArrayClear()

Offline

#13 2015-07-14 15:20:48

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

Junior/RO wrote:

I can't get it.

function TSQLRest.RetrieveListObjArray() is Boolean, but you are using Result as a [absolute] TObjectDynArray in ObjArrayClear()

I don't want to use the boolean result of the function RetrieveListObjArray, the result of SelectByManufacturer() should be filled by RetrieveListObjArray(Result,...)

Offline

#14 2015-07-14 17:23:47

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: free objects on the server side

What happens if you change this

  try
    result := T.ToObjArray(result,Table); <------- HERE access violation
  finally
    T.Free;
  end;

to this?

  try
    result := T.ToObjArray(ObjArray,Table);
  finally
    T.Free;
  end;

Offline

#15 2015-07-14 17:48:58

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

Yes, there was an issue here.

Fixed by http://synopse.info/fossil/info/f83aed8e10

Thanks for the feedback.

Offline

#16 2015-07-15 09:06:31

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

ab wrote:

Yes, there was an issue here.

Fixed by http://synopse.info/fossil/info/f83aed8e10

Thanks for the feedback.

Thx for the fixing!

But the problem with the growing size of the server-"exe" remains... after the http-request, e.g. http://serverhost:7979/root/messartservice/SelectByManufacturer?aManufacturer=SMITH, I get about 294912 records a database. It seems that the "exe" does free not everything.

Offline

#17 2015-07-15 09:12:46

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

JSON content should be cached somewhere.

Please try to disable SQlite3 cache.

Offline

#18 2015-07-15 09:28:48

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

Re: free objects on the server side

ab wrote:

JSON content should be cached somewhere.

Please try to disable SQlite3 cache.

I've tried it as follows, but the problem remains...

...
  ServerDB := TSQLRestServerDB.Create(Model, ChangeFileExt(ExeVersion.ProgramFileName, '.db3'), False);
  ServerDB.DB.UseCache := False;
  ServerDB.CreateMissingTables;
...

Offline

#19 2015-07-15 14:07:06

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

Do you have the same behavior with a native SQlite3 DB?

Do you have the same behavior when connecting to MySQL with ZDBC instead of FireDAC?

Do you have the same behavior when you are NOT using services, but a plain MySQL/FireDAC request, using only a SynDB SELECT statement, not the ORM?

Offline

#20 2015-07-18 13:10:39

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

I have done some test with this problem ( with the mORMot version 1.18.1597, 1.18.1632 and 1.18.1655). I did not retrieve data rows instead of inserting massive rows to the database.

On the Server side:
  Props: TSQLDBConnectionProperties;

  // get the Props like in sample 12 - SynDB Explorer
  .....
  if (Props.DBMS <> dSQLite) then
      VirtualTableExternalRegisterAll(Model, Props);  // Register all tables of the model to be external if we don't use SQLite3
   //Props.UseCache := False;  // It's READONLY! How can I disable the Cache??

  fDbFileName := ChangeFileExt(paramstr(0),'.db3');
  try
    RestServer := TSQLRestServerDB.Create(Model, fDbFileName, False);
    with RestServer do
    begin
      CreateMissingTables;
      Cache.Clear;
      DB.UseCache := False;
      AcquireWriteMode := amBackgroundThread;
      AcquireWriteTimeOut := 30;
    end;

    // Create the HTTP Server for Client Connection
    HttpServer := TCustomHttpServer.Create(AppServerPort, [RestServer], '+', useHttpApiRegisteringURI);
    HttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
    ....

On the Client Side:

    Model := CreateSampleDataModel(ConstHttpRoot);
    AppServer := TSQLHttpClient.Create(AppServerIP, AppServerPort, Model);

    .......
    // Use BatchStart, BachAdd, BatchSend to Insert data rows to mORMot Server.
    .......

Test Results: (Watched the memory allocated by the Server side by the TaskManager of Win7-64bit when programs were running)
(1) If the Props is connected with the Internal SQLite3 Engine,  after insert more than 2 millions row into 2 tables, the total memory allocated by the server is about 75000KB (Maxium);
    The mORMot server works absolutely OK,   and with the FASTMM4 memory manager running in the FullDebuging mode, there is no memory leakage reported.

(2) If the selected Props is connected with the external MSQL Engine, the memory consumed by the server, seems proportional to the rows added to the external MSSQL database. Here are some test data listed below:
          Rows Inserted            Memory Consumed(in KB)
                   0                                  6180
            25200                                57160
          118620                              186256
          253576                              346664
          421526                              609227
          563164                              817312
          657664                              948559
          711214                            1017168
          .... Eventually, the Server goes with no response with the Client Request, all the remaining data will be lost.

      And with the FASTMM4 memory manager running in the FullDebuging mode, there is still no memory leakage reported.

      (It's not bleeding. It seems the problem is related with the TOleDBMSSQLConnectionProperties, and I tried to replace it with TOleDBMSSQL2008ConnectionProperties to connect with MSSQL2008, but nothing changed.)

      By the way, It seems there is a bug in 1.18.1655. When  exiting the Server program, there is an AV in SynDB:TSQLDBConnectionPropertiesThreadSafe.EndCurrentThread, ant it will not happen in previous versions of mORMot.

Offline

#21 2015-07-19 08:38:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

Did you try with ODBC?

Offline

#22 2015-07-19 11:40:25

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

ab wrote:

Did you try with ODBC?

   Yes, I tried to connect the external database with ODBC, but only partially succeeded.

   I Connect the MSSQL Database with ODBC successfully, as I can get all the table names in the external database. But when I try to get some rows from one table (from the server side) like this:

   PeopleList := RestServer.RetrieveList(TSQLPeople,  '',  [],  '');

   I got an exception:  TODBCStatement - TODBCLib error: [07009] [Microsoft][ODBC SQL Server Driver] Invalid descriptor index (0)

   All the RetrieveList calls from the Client side, fails as well.

   In the server side, I tried to Add some rows to the table named People, but the following call  allways returns aID with 0 and without any error message. Nothing is added to the database.

       aPeople := TSQLPeople.Create;
       try
          aID := RestServer.Add(aPeople, True);
       finally
          aPeople.Free;
       end;

Last edited by houdw2006 (2015-07-19 13:17:48)

Offline

#23 2015-07-19 17:04:50

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

Hi, ab,

    After try the next few lines of code, I found the secrete is in TOleDBConnection.

    Props: TSQLDBConnectionProperties;

    ......

    After connect to the external database by this Props, I just do the simplest thing, call RestServer.Add to add massive rows to the external database (the memory consumed increasely), then I intentially free this Props

procedure TFormRestServer.btnFreeConnectionPoolClick(Sender: TObject);
begin
    Props.ClearConnectionPool;  // line 1
    Props.CleanupInstance;       // line 2
    FreeAndNil(Props);              // line 3
end;

    After the first two lines are executed, the memory consumed by the server has no change. But when line 3 is executed, all the lost memory are released!

    So I traced into here: (line 5987 of unit SynDB).

destructor TSQLDBConnectionPropertiesThreadSafe.Destroy;
begin
  inherited Destroy;                                      // line 1
  fConnectionPool.Free;                                //  line 2
  DeleteCriticalSection(fConnectionCS);         //  line 3
end;

   After call the second line: fConnectionPool.Free; All the comsumed memory are released. (in the Task Manager, you can see it clearly).

   So it's very clear that Props stealed all the lost memory and kept track of them very well.

   Some hints: after call Props.NewThreadSafeStatementPrepared, do we need to do some thing like unprepare?
     or Query.ExecutePrepared then unprepared?

Offline

#24 2015-07-19 18:20:27

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

I'm afraid it is not "Props" which "steal" all memory...

It is the OleDB provider which does not release its memory as expected...

Offline

#25 2015-07-19 22:48:52

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

Thanks for your reply, ab.

I am sorry for the joke "Props stealed all the lost memory...", so don't feel unhappy about what I said.  I am not familiar with OleDB programming by using the OleDB interface.  But I think perhaps we need to Release the ICommandText somewhere after we use it, as the RefCount is not zero, the OleDB Provider will not Release the memory associated with this ICommandText reference.  The Props is the Owner of OleDB Provider, it has the responsibility to do that.

If there is no solution for the "memory growing" problem in a short time,  can we release the Props periodically as a workaround?

Offline

#26 2015-07-20 03:13:59

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

Hi, ab.

I have discovered some interesting thing, but I can not understand what happen to the fCache.  Here is the tracing story. This time, I payed attention to the  fCache member in class TSQLDBConnection.

TSQLDBConnection = class    // SynDB, line 1604
    fCache: TRawUTF8ListHashed;
end;

(1) when connecting to the external database server, the RestServer try to get the datetime from the database Engine:
function TSQLDBConnection.GetServerDateTime: TDateTime; // SynDB.pas 
Added 'select GETDATE()' into fCache;

(2) the first time call RestServer.Add(aPeople, True),  the RestServer is trying to get the max ID in the related table:
function TSQLRestStorageExternal.EngineLockedNextID: TID; // mORMot.pas, line 980
Added 'select max(ID) from dbo.People' into fCache;

(3) still the first time call RestServer.Add(aPeople, True);
function TSQLRest.Add(aTable: TSQLRecordClass; const aSimpleFields: array of const; // mORMot, line 30358
function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8; ....); // SynDB, line 4029
Added 'insert into dbo.People (...) values (...)' into fCache.

after this time, no matter how many times I call RestServer.Add, no more objects are added to the fCache.

Conclusion: there are only 3 prepared statements are stored in the fCache in my cases.

Offline

#27 2015-07-20 03:29:45

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

// I met an post error, says something like: "i" was opened with itself, this is not allowed!
// Even I Comment all the code, I can't pass the error check. :-(   I changed the [ into #


//----------------------- Memory Release ---------------------------
//When I call FreeAndNil(Props);
//the process goes to:
//procedure TSQLDBConnection.Disconnect; // SynDB, line 3941
//var i: integer;
//    Obj: PPointerArray;
//begin
//  InternalProcess(speDisconnected);
//  if fCache<>nil then begin
//    InternalProcess(speActive);
//    try
//      Obj := fCache.ObjectPtr;
//      if Obj<>nil then
//        for i := 0 to fCache.Count-1 do
//          TSQLDBStatement(Obj#i#).FRefCount := 0; // force clean release
//      FreeAndNil(fCache); // release all cached statements
//    finally
//      InternalProcess(speNonActive);
//    end;
//end;

Last edited by houdw2006 (2015-07-20 03:32:15)

Offline

#28 2015-07-20 03:30:57

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

// I met an post error, says something like: "i" was opened with itself, this is not allowed!
// Even I Comment all the code, I can't pass the error check. :-(   I changed the [ into #

//and when FreeAndNil(fCache); is executed, the process goes here:
//
//destructor TRawUTF8List.Destroy;   // SynCommons, line 43571
//begin
//  Capacity := 0;
//  inherited;
//end;
//
//when Capacity := 0; is called, magic things happened here:
//
//procedure TRawUTF8List.SetCapacity(const Value: PtrInt);  // SynCommons, line 43832
//var i: integer;
//begin
//  if self<>nil then begin
//    if Value<=0 then begin
//      fList := nil;
//      if fObjects<>nil then begin
//        if fObjectsOwned then
//          for i := 0 to fCount-1 do
//            fObjects#i#.Free;    // when i = fcount - 1, magic happens!
//        fObjects := nil;
//      end;
//      fCount := 0;
//      ......
//    end;
//  end;
//end;
//
//// when i = fCount - 1, fObjects#i#.Free will release all the lost memory!
//
//I just do not know how fObjects[fCount-1] becomes a big monster!  I hope this can give you some help to catch the "memory growing" bug.  :-)

Last edited by houdw2006 (2015-07-20 03:39:54)

Offline

#29 2015-07-20 07:32:39

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

Still for the "memory growing" problem, this time, I want to see where the memory is allocated.
From the Task Manager, It's very clear that I call RestServer.Add(aPeople, True) 4 times, the working memory set will increase 4KB.

I traced into
procedure TOleDBStatement.ExecutePrepared;  // SynOleDB, line 1560
begin
  .......
      SetLength(ParamsStatus,fParamCount);  // line 1626
      OleDBConnection.OleDBCheck(self,         // line 1627, after execute this, the number of  working memory set will be increased by 4KB.
        (fCommand as IAccessor).CreateAccessor(
          DBACCESSOR_PARAMETERDATA,fParamCount,Pointer(fParamBindings),0,
          fDBParams.HACCESSOR,pointer(ParamsStatus)),ParamsStatus);
      fDBParams.cParamSets := 1;
    ....
      // 3.2 ExpectResults=false (e.g. SQL UPDATE) -> leave fRowSet=nil
      OleDBConnection.OleDBCheck(self,   // line 1648
        fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil));
    .....
end;

Memory is allocated in
     (fCommand as IAccessor).CreateAccessor(   // line 1628
     or
     fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil)  // line 1649

to be continued...

Offline

#30 2015-07-20 08:33:20

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

I've made the TSQLDBConnectionProperties.UseCache property read/write.
See http://synopse.info/fossil/info/6dc6464869

I suspect that if you disable statement cache, it would release all resources ASAP.

Offline

#31 2015-07-21 03:01:43

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

Tank you so much, ab.

There is a good news and a bad news. The good news is that it does work for inserting. I test Inserting from both server and client side, after inserting more than 3 million rows into the table, the memory comsumed by the server just about 35MB. Terrific!

The bad news is that for the retrieving from a table with massive rows,  the memory consumed by the server is still dramatically large. when I want to retrieve the top 200 rows from a table with more than 1 million rows, the server consumed more than 400MB memory. I have monitored from the Profiler of MSSQL and found that, when the retrieve command is send from the client, the LIMIT parameter of RetrieveList does not work(MSSQL receive a query command without the expected "TOP 200" parameter), I think this is another reason why the server consuming so much memory (and the memory will not release automatically after the retrieve completed).

Offline

#32 2015-07-21 09:41:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

How is you query like?

I guess there is a JOIN in it, or some complex expression.

The ORM is able to process most simple requests, but fallback to SQLite3 virtual tables when the request is more complex.
In this context, performance may degrade a lot, and a lot of data is retrieved by the SQlite3 virtual table engine.

Complex queries should better by-pass the ORM, and run directly against the external DB.

Offline

#33 2015-07-21 10:14:39

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

It's a simple query, just like this expected T-SQL, SELECT TOP 200 * FROM People WHERE DepartNo=30 AND WorkingStatus=3

do the query from the Client side, I get the wrong T-SQL:

....
     AppServer: TSQLHttpClient;
     fWorkingPeople: TObjectList;
....

   fWorkingPeople  := AppServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [30, 3, 200]);

   // In the MSSQL Profiler, I get: SELECT * FROM People WHERE DepartNo=30 AND WorkingStatus=3
.......


do the same query from the Server side, I get the correct T-SQL:
....
    TCustomHttpServer = class(TSQLHttpServer)
        ....
     end;

     RestServer: TSQLRestServerDB;
     HttpServer: TCustomHttpServer;
     fWorkingPeople: TObjectList;
.......

   // do the query from the server side, the T-SQL is correct :
   fWorkingPeople  := RestServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [30, 3, 200]);
.......
   // In the MSSQL Profiler, I get: SELECT TOP 200 * FROM People WHERE DepartNo=30 AND WorkingStatus=3

Offline

#34 2015-07-22 21:44:13

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

There are many mistakes in my last post. Sorry for the wrong bug report!  This time, I have done the test both for MSSQL and SQLite3, results are listed below.

Test MSSQL DataSet: Total 15 rows, with 13 rows satisfy the query condition
(I read the note in mORMot ducumentation 9.2.4, Note that parenthesis and mixed AND OR expressions are not handled yet.)

1. Query from Server:

Case 1:
   mORMot Query(Complex Query Syntax with Parenthesis):
      fPeopleList := RestServer.RetrieveList(TSQLPeople,
      '(DepartNo=?) AND (WorkingStatus=?) LIMIT ?',
        [20, 1, 10]);

   MSSQL Profiler: (note: ID is the last column, without TOP Clause in T-SQL)
   exec sp_executesql N'select ID,DepartNo,BirthDate,Name,WorkingStatus from dbo.People where DepartNo=@P1
             and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1
     
  ResultSet: 10 rows (The LIMIT Parameter seems worked, mORMot applied a local filter?)

Case 2:
   mORMot Query (Simple Query Syntax):
      fPeopleList := RestServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [20, 1, 10]);

   MSSQL Profiler: (note: ID is the first column, without TOP Clause in T-SQL)
     exec sp_executesql N'select DepartNo,BirthDate,Name,WorkingStatus,ID from dbo.People where DepartNo=@P1
                and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1

   ResultSet: 13 rows  (The LIMIT Parameter does NOT work).
   
   
2. Query from Client:

Case 3:
   mORMot Query(Complex Query Syntax with Parenthesis):
      fPeopleList := AppServer.RetrieveList(TSQLPeople,
      '(DepartNo=?) AND (WorkingStatus=?) LIMIT ?',
        [20, 1, 10]);

   HTTP Server InContent:
   SELECT ID,DepartNo,BirthDate,BirthDate,Name,WorkingStatus FROM People WHERE (SmsProxyNo=:(20):) AND (SmsStatus=:(1):) LIMIT :(10):

   MSSQL Profiler: (note: NO T-SQL at all)

     
  ResultSet: no ResultSet

Case 4:
   mORMot Query (Simple Query Syntax):
      fPeopleList := AppServer.RetrieveList(TSQLPeople,
      'DepartNo=? AND WorkingStatus=? LIMIT ?',
        [20, 1, 10]);

   HTTP Server InContent:
   SELECT ID,DepartNo,BirthDate,BirthDate,Name,WorkingStatus FROM People WHERE DepartNo=:(20): AND WorkingStatus=:(1): LIMIT :(10):

   MSSQL Profiler: (note: ID is the first column, NO TOP Clause)
     exec sp_executesql N'select ID,DepartNo,BirthDate,Name,WorkingStatus,ID from dbo.People where DepartNo=@P1
                and WorkingStatus=@P2',N'@P1 bigint,@P2 bigint',20,1

   ResultSet: 13 rows  (The LIMIT Parameter does NOT work).
   
   
-------------------------------------------------------------------------------------------------------------------------------------------
Test SQLite3 DataSet: Total 15 rows, with 13 rows satisfy the query condition

Redo the last 4 cases,

ResultSet: 10 rows, no problem with SQLite3.

Offline

#35 2015-07-22 23:58:24

mingda
Member
Registered: 2013-01-04
Posts: 121

Re: free objects on the server side

I think you can make a test case for anyone else to trace the problem, let ab debug it easy.

Offline

#36 2015-07-23 06:04:13

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: free objects on the server side

1. Write the requests WITHOUT parenthesis.

2. LIMIT should not be parametrized, but have a direct number (no ?).

Offline

#37 2015-07-23 14:02:22

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: free objects on the server side

It really works. Thank you! I am really enjoy using mORMot!

Offline

Board footer

Powered by FluxBB