Memory usage problem.

turrican · 2016-10-11 16:29:07

Hi,

I have a SQLite database with a 2 millions of rows in a Table. I can load this database with mORMot without problems and can insert and update rows.

But now, I need to iterate the table and I have memory problems.

The way I am iterating table is the next one :

begin
  try
    Log := TMailLog.CreateAndFillPrepare(CurrentContext.ORMServer, 'MailResume is null');
    while log.FillOne do
    begin
      // do something 
    end;
  finally
    log.free;
  end;
end;

Memory increasing until crash.

I don't know why FillOne method is wasting memory and make the process die.

ab · 2016-10-11 17:53:14

Yes, as documented, all data is first retrieved as JSON from the SQlite3 table, then the ORM processes this JSON data.
So what you do won't work.

You should either:
- directly use SynSQLite3 (not recommended)
- modify your code to use paging

Paging may be implemented as such:
- you add a LIMIT 100000 to retrieve up to 100000 for each loop
- you preform the 100000 rows in the loop
- you retrieve the latest ID of the latest row in the loop
- then you use "ID > ? limit 100000" with ? replaced with the latest row in the loop.

So your where condition becomes:
'MailResume is null and ID > ? limit 100000'
with the parameter being 0, then the latest returned ID of each loop.

turrican · 2016-10-13 08:56:22

Thanks AB, I am going to implement your solution.

JD · 2016-10-29 11:01:17

turrican wrote:

Thanks AB, I am going to implement your solution.

How did you eventually implement your paging solution while respecting ORM rules? Is the pagination on the client side or on the server side?

Thanks,

JD

turrican · 2016-11-02 09:03:59

Hi,

Quite easy to achieve with mORMot. I did from client side but also can be implemented on the server side.

This is my implementation :

  //Prepare paging
  current_id := 0;
  while continue do
  begin
    try
      //Not continue if no rows fetched
      continue := False;
      //Try to fetch no more than 5000 rows     
      person := Person.CreateAndFillPrepare(CurrentContext.ORMServer, 'ID > ? limit 5000', [current_id]);
      while person.FillOne do
      begin
        try
	   //Do something with person			
        finally
           //Store the row id	
          current_id := person.ID;
          continue := True;
        end;
      end;
    finally
	person.free;
    end;
  end;
end;

Last edited by turrican (2016-11-02 09:09:19)

mORMot Open Source

#1 2016-10-11 16:29:07

Memory usage problem.

#2 2016-10-11 17:53:14

Re: Memory usage problem.

#3 2016-10-13 08:56:22

Re: Memory usage problem.

#4 2016-10-29 11:01:17

Re: Memory usage problem.

#5 2016-11-02 09:03:59

Re: Memory usage problem.

Board footer