mORMot Open Source

wxinix

Member

Registered: 2020-09-07

Posts: 121

CreateAndFillPrepare throws out of memory exception for large table

mormot.db.raw.sqlite3, line 6810, had the max memory hard coded as 4096 - is that intentional? 

"TSqlRequest.Execute: output overflow after 512 MB for []'.

function DirectExplainQueryPlan(DB: TSqlite3DB; const aSql: RawUtf8): RawUtf8;
var
  R: TSqlRequest;
  cnt: integer;
begin
  result := R.ExecuteJson(DB, 'explain query plan ' + aSql, true, @cnt, 4096,
    [twoForceJsonExtended, twoIgnoreDefaultInRecord]);
  if cnt = 0 then
    result := ''; // no query plan
end;

Last edited by wxinix (2021-12-29 01:56:45)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: CreateAndFillPrepare throws out of memory exception for large table

Do you have a query plan bigger than 4KB?

What is the SQL?

My guess is that the "explain query plan" is fine, but your request is too big.
There is indeed a query resultset size limitation at 512MB as stated by the error message: "TSqlRequest.Execute: output overflow after 512 MB for []".
512MB of JSON is way to much.
Use paging in your query for such tables.
If you really need to have all content of a table, use low-level SQlite3 direct access, but not the JSON/ORM level.
You may want to change the TSqlDataBase.StatementMaxMemory property to a higher value, but I would not recommend that because it would consume a lot of memory and the request will be blocking for other process.