在SQLite，FireDac，Delphi中插入记录时内存不足 [英] Out of memory when inserting records in SQLite, FireDac, Delphi

问题描述

我有一个Delphi应用程序，它通过FireDac组件TFDTable将大约200,000条记录（约1GB）插入到SQLite数据库中。插入时，我看到应用程序内存增加，直到出现 Out of Memory Error。我猜想它与缓存和分页有关，但是我无法找到任何能解决该问题的方法，除非每1000条记录关闭并重新打开数据库一次。有想法吗？

I have a Delphi app that inserts some 200,000 records (about 1GB) into an SQLite database via the FireDac component TFDTable. As it inserts, I can see the application memory increasing until I get an "Out of Memory Error". I'm guessing it has something to due with cache and paging but I cant find anything that fixes it short of closing and reopening the database every 1000 records or so. Thoughts?

已编辑...
对不起，我的意思很简单...
代码很简单，所以我没有包含它，但是看起来基本上是这样的：

Edited... Sorry for the thinly worded question... The code was simple so I didn't include it but looks basically like this:

procedure DoIt;
begin
  myDB.Insert;
  myDBField1.AsString := StringOfChar('-',1000);
  myDB.Post;
end;

现在，我希望内存可能会增加，因为字符串很可能会复制到数据库缓存中。如果我使用GetMemoryManagerState（）查看分配，则实际上可以看到这一点。我希望在某个时刻，当数据写入磁盘时，缓存中的内存将被刷新。但是，事实并非如此。直到我收到内存不足错误为止。

Now, I expect the memory might increase as the string is likely copied to the DB caches. If I look at the allocations using GetMemoryManagerState() I can actually see this. I would expect that at some point, the memory in the cache would be flushed as the data is written to disc. However, it does not seem to be. It just keeps going until I get an "Out of Memory" error.

通常，大多数对象属性都设置为默认状态，除了在连接中选择sqlite和将字段添加到表中。

In general most of the object properties are all set to default states except selecting sqlite in the connection and adding fields to the table.

我知道这里没有很多事情要做。但是我也不认为这不会失败，我希望有人可能也遇到了类似的问题。

I know there is not a lot to go on here. But I didn't think this would fail either and I was hoping someone may have had a similar issue.

推荐答案

TFDTable 是围绕查询的精简包装可以构建用于与基础DBMS表一起操作的SQL命令的对象。它具有自己的存储空间（ 表 对象），它将获取到客户端的数据以及您插入的元组存储在其中。但是所有这些都在内存中，没有底层文件缓存。

TFDTable is a thin wrapper around a query object that can build SQL commands for operating with the underlying DBMS table. It has its own storage (Table object) into which it stores data fetched to the client as well as tuples that you insert. But all that is in memory, there's no underlying file cache.

尽管可以在插入时清除内部存储空间，但 TFDTable 不是用于插入如此大量数据的好对象。更好地使用 TFDQuery 结合称为 阵列DML 的批处理命令执行技术> 甚至可以为本地DBMS引擎带来真正的性能提升。而且 TFDQuery 不会

Although that internal storage can be cleared whilst you're inserting, TFDTable is not a good object for inserting data in such amount. Better use query object like TFDQuery which in combination with a batch command execution technique called Array DML can bring you real performance increase, even for local DBMS engine. And TFDQuery won't be caching inserted tuples.

当您使用索引参数绑定时，FireDAC原生支持SQLite的这种技术。此代码应插入200次批处理的1000个唯一元组：

FireDAC supports this technique for SQLite natively when you use indexed parameter binding, e.g. this code should insert 200 times batch of 1000 unique tuples:

const
  BatchSize = 1000;
  TotalSize = 200000;
var
  Batch: Integer;
  Index: Integer;
begin
  FDQuery.SQL.Text := 'INSERT INTO MyTable VALUES (:p1, :p2)';
  FDQuery.Params.BindMode := pbByNumber;
  FDQuery.Params.ArraySize := BatchSize;

  for Batch := 0 to TotalSize div BatchSize - 1 do
  begin
    for Index := 0 to BatchSize - 1 do
    begin
      FDQuery.Params[0].AsIntegers[Index] := (Batch * BatchSize) + Index;
      FDQuery.Params[1].AsWideStrings[Index] := 'Some Unicode string value';
    end;
    FDQuery.Execute(BatchSize, 0);
  end;
end;

这篇关于在SQLite，FireDac，Delphi中插入记录时内存不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！