How to use Delphi to process very large list with limited memory

I just discovered that my programmer actually loaded the entire SQLite database into lists (select * from tablename) (for search and filtering operations) instead of executing sql statements individually. This worked well when there are less than 20,000 - 50,000 records. But once the memory runs low, or there exceeds a certain number of records, the Delph 7 application freezes.

It is too late to change this approach as we're deep into the project and it has been deployed. I don't have the time to redo the solution so I need to find a "quick fix" to reduce memory usage and increase the number of records in the database. I'm wondering if it is possible to split the list into files and then process one by one?

Updated to add OPs answer into question

Thanks for replying. Here's some code snippets on how the sqlite records are loaded into a record which then used throughout the application. There can be up to 200,000 records, which consumes a lot of memory. Is there a way to buffer the record in file?

type
  TMyDatabase = class(TThread)
  private
    Owner: TComponent;
    FSqldb: TSQLiteDatabase;
    FSltb: TSQLIteTable;
.....

type
  PMyMessageRec = ^TMyMessageRec;
  TMyMessageRec = record
    Id: integer;
    RcptId: integer;
    PhoneNumber: ShortString;
    Text: string;
   ......
  end;

procedure TMyDatabase.Execute;
........
begin
...........
         FSltb := FSqldb.GetTable('SELECT * FROM Messages ORDER BY ID LIMIT ' + IntToStr(MaximumMessages));
        try
          Synchronize(Syn开发者_JAVA技巧cLoadAllMessages);
          Synchronize(SyncLoadMessages);
        finally
          FSltb.Free;
        end;


procedure TMyDatabase.SyncLoadAllMessages;
var MessRec: PMyMessageRec;
.......
begin
....
 while not FSltb.EOF do
            Begin
              if TerminateAll then exit;
              New(MessRec);
              MessRec.Id := FSltb.FieldAsInteger(FSltb.FieldIndex['ID']);
              MessRec.RcptId := FSltb.FieldAsInteger(FSltb.FieldIndex['RecipientId']);
              MessRec.PhoneNumber := FSltb.FieldAsString(FSltb.FieldIndex['RecipientPhone']);
              MessRec.Text := FSltb.FieldAsString(FSltb.FieldIndex['Text']);
              MessRec.Charset := FSltb.FieldAsString(FSltb.FieldIndex['Charset']);

"In-memory databases" are not a bad design per se (there are many products on the market). Object-relational mappers and object caches use this strategy for smaller or bigger parts of the database to improve performance.

Try to divide the problem in two parts to have a short-term and a long-term solution:

1. short-term

   • increase available memory (see other answers)
   • apply design patterns which reduce memory usage, for example the flyweight pattern
   • use a object cache algorithm (lazy loading / removing of objects)
   • find and eliminate memory leaks
   • use the FastMM usage tracker application to search for application objects which use a lot of memory

2. long-term

   • introduce a service layer API which hides the implementation details of the underlying object lists, and initially simply uses the existing object lists, then replace the implementation of the service layer step-by-step by SQL queries, or calls to an ORM
   • with a service layer design, the application could also be divided into a GUI client and a server (service) side application later

This might help, but it's very much applying a sticking plaster to a severed head!

1. Switch to a 64 bit machine.
2. Make your program /LARGEADDRESSAWARE.
3. Switch to FastMM (needed to make step 2 work due to bugs in old Borland MM).

Then cross your fingers and hope that 4GB address space, as opposed to 2GB, is enough!

I would add a "WHERE" clause based on some criteria, like a date range, to the initial query so that you or your app can have some control of the size of the initial result set. I use DevExpress QuantumGrids alot which loads the entire query result into memory for great flexibility and speed. (DevExpress stuff is amazing....) I place a couple of date controls in the app that my users can interact with and set a StartDate and EndDate range for the result set. This keeps the performance under some control.

Even the solution you suggest is a "change to this approach". You're going to wind up refactoring somehow and it would be better use the SQL engine to do as much filtering as possible.

You don't describe the actual algorithm used, but I'm going to assume you currently do this:

1. Load entire dataset into some kind of list at startup.

2. Have user specify filter criteria.

3. Call a function which passes in the filter criteria and operates on the complete list, returning another list that is filtered and sorted.

If so, you should be able to refactor at step 3. In step 1 just create an empty list (so you don't throw errors on references to that list). Then, in step 3 either use SQL to do all your filtering or, if that's too much refactoring, figure out how to get a partially filtered list from SQLite into an intermediate in memory list, then apply your existing sorting and filtering to that list.

That should limit your refactoring to a single function (assuming I guessed the program's overall structure correctly).

As others have pointed out its kind of hard to give suggestions without more details. That being said here's my two cents.

My suggestion would be to use a profiler to determine where the "hot spots" are and focus on those. You can get a free one from SmartBear.

You didn't mention what kind of list you are working with. If the list is that entrenched in the design you could write a wrapper that functions like a list from the caller's perspective but internally it relies on parameterized select statements.