Huge memory problem

[limelect 47]

I need your advice returning to my problem -out of memory.

I have this free program  https://limelect.com/downloads/document-projects/

that came out of my need. After 40+years of pascal/Delphi, I have a large

number of projects/programs I do not even remember their content categories and more.

The record includes text category pictures and more describing the source and program.

 

Just started and I have 730 records with a 1.56 GB database (FireDac) size.

So fixing the software does not seem to have an option?

My DB file will grow to be very large.

 

1. I suspect I will have thousands of records

2 What is the theoretical size FireDac can handle?

3. Is it possible to fix my program with software?

4. I was thinking of splitting the DB.

5. Can the DB be manipulated on disk and not on RAM?

6. I am looking for a solution that will NOT cost money as it is a free program.

7. Installing other software like MS SQLserver is not an option.

8. I need a local/or any solution.

 

9. On a commercial program I have in a company I use MS SQLserver 2008 without any problems.

     Years of work and no problems.

 

What are my options? Or what is my thinking wrong?

 

Sorry if my need is too long.

[David Heffernan 2300]

Would you like to trisect the angle with ruler and compass while you are it too? 

 

Seriously though, I wonder if it's realistic to look for a solution without actually changing anything. 

[limelect 47]

@David HeffernanBy no means I am willing to redesign the DB concept

if I knew there is a good solution like MS server for example.

The only problem I will not give my user to install the MS server.

Why I wrote my problem because someone might come up with a good

dB that i do not know

[Lars Fosdal 1732]

FireDac is not a database, but Delphi's DB Driver framework. 

There is no practical size limit in FireDac itself.

The limits would be the limits of the underlying database.

 

What is your actual underlying database?

 

A good allround DB is designed to be on disk and to be manipulated on disk and use memory mostly for indexes and caching.

Using a billon row DB that fills terabytes of diskspace, does not necessarily imply a performance problem. 

There are free DBs you can use - even commercially: Postgresql, MariaDB (Open source MySQL), Firebird, etc. 

 

Ideally, you would want to limit what you keep in memory, and instead pull in stuff on demand - but that means a redesign of your application.

 

However, if you need/want to load and keep everything in memory, switching from 32-bit to 64-bit will offer a much larger memory space (but then you have to face the sorry state of the 64-bit debugger).

[limelect 47]

@Lars Fosdal you are correct my DB is SQLite.

When I SQL or add records all gives me memory problems.

What I think I will do I will write an investigation program with

my actual database to analyze my use so

I will decrease the program to a manageable logic of one function.

[Lars Fosdal 1732]

Can it be a leak in the application?  I.e. you use a DB resource, but never release it after use?

 

[limelect 47]

@Lars FosdalNo leak first thing I do to check it.

uses
 FastMM4,

[Vandrovnik 202]

3 minutes ago, limelect said:

@Lars FosdalNo leak first thing I do to check it.

uses
 FastMM4,

Do you also have this?

 {$IFDEF DEBUG}
  System.ReportMemoryLeaksOnShutdown:=true;
 {$ENDIF}

 

[limelect 47]

@Vandrovnik YES . Actually, no need if you have FastMM4,

 

 if TogFirst.IsFirstInstance then
  begin
 ReportMemoryLeaksOnShutdown := True;
    Application.Initialize;
    Application.MainFormOnTaskbar := True;
    Application.CreateForm(TForm1, Form1);
  Application.CreateForm(TfmDropDown, fmDropDown);
  Application.Run;
  end
  else
    TogFirst.ActivateFirstInstance;
 

[limelect 47]

The leak is not a problem to a professional

[Lars Fosdal 1732]

Leaks come in many forms.  It might not be in the Delphi code.

https://www.sqlite.org/malloc.html

[limelect 47]

@Lars Fosdal Thanks i will check it

[Attila Kovacs 617]

You'll not have the drink Kimi

[limelect 47]

@Lars Fosdal  It seems that most pas files associated with FireDAC are not

accessible. Furthermore, is there a way to know what is going on with my SQLite?

while executing? DEBUG?

I think this is not given by Delphi.

So I will revert to my test as I wrote before.

[Lars Fosdal 1732]

@limelect You probably should read the SQLite docs in detail.  Perhaps there are clues to debugging memory consumption there? Is it a configuration thing?

[limelect 47]

@Lars FosdalThanks I thought so too but did not find any external something to Delphi.

It is close to us (Delphi).

I loved the use of it since it has no DLL.

Edited November 11, 2022 by limelect

[Hans J. Ellingsgaard 19]

Do you use query or table components?

[Javier Tarí 23]

Excuse my question, but: Are you using a memory table or file based table?

[limelect 47]

I use query and table.

It seems that query has the problem

I will write a test program for 1 query and see what happens

[Hans J. Ellingsgaard 19]

My guess is that your tables has a lot of records, or each of your records holds a lot of data, and when you opens it, with a table or query, you get all the records into memory.

 

If that's the case, you will need to limit the number of records with a where clause in your query. You could also limit the number of fields for each record in an overview, and then select all fields only for an active record.

[limelect 47]

@Hans J. Ellingsgaardmaybe it is feasible for a search but not for insert where I have that problem too.

While searching for a solution on searach I found that if I have an EMPTY text to search I get a memory problem

[Hans J. Ellingsgaard 19]

4 hours ago, limelect said:

@Hans J. Ellingsgaardmaybe it is feasible for a search but not for insert where I have that problem too.

While searching for a solution on searach I found that if I have an EMPTY text to search I get a memory problem

Is that search based on a table component? If that's the case you'll end up loading all records into memory. You can instead make a search with a query and a where clause.

[limelect 47]

@Hans J. EllingsgaardAll searches are with query

For now, the search is OK. I changed the SQL statement .

P.S Empty searches do make memory problems.

The strange behavior is now on insert where I cannot pinpoint the 

problem. I am not sure where is the problem yet.

 

[Hans J. Ellingsgaard 19]

18 hours ago, limelect said:

The strange behavior is now on insert where I cannot pinpoint the 

problem. I am not sure where is the problem yet.

Do you use a FDQuery for insert? If that's the case do you hava a "select * from" in the sql command, or do you use an "insert into command"?

You could try to write a direct insert command and use a FDCommand component to see wath's happening.

[limelect 47]

For now, everything works. I will work with the program for weeks and see what happens.

 

Edited November 17, 2022 by limelect