Large SQLIite Tables and Memory Use

Question

System:

• Windows 10 pro
• Delphi Alexandria (11) Pro
• SQLite (local access only)

I have a FireDAC TfdTable on a data module that accesses a large table (about 750 MB). When I do Table.Open while watching the Task Manager memory report, it looks like the entire table is being loaded into memory instead of a small subset (usage goes from about 24 MB to 1.1 GB).

Referring to the Delphi docs about Live Data, I have the Fetch options set per the recommendations and have a unique key on the table, though its name is not "PRIMARY" but it is the selected index when opening the table.

Fetch Options:

• Cursor Kind: ckAutomatic
• LiveWindowFastFirst: True
• LiveWindowParanoic: True
• Mode: On Demand
• Record Count Mode: cmTotal
• Recs Max/Min: -1
• Rowset Size: 25
• Unidirectional: False

Am I wrong to expect otherwise? If not, any suggestions on how to reduce memory use?

Thanks Brian

Answer 1

Add a primary key to your table and your problem will be solved.