API 1 vs API 2 SQLite memory consumption

macOS 10.14.6, Xojo 2019R3 or Xojo 2019R2.1

Hello,
I load data into an in-memory SQLite database. When updating the code to API 2.0 the memory consumption of the app gets huge (the app even crashes in 32 bit mode).

Comparing the memory consumptiom I get:
API_1 memory usage: 289.3 MB
API_2 memory usage: 8.34 GB (the app crashes in 32-bit mode)

I am doing something wrong or is this a memory leak?

Here is a simplified example of the API 1.0 code:

[code]dim SQLiteDB as new SQLiteDatabase
dim s as String

dim r, c as Integer
const NrOfRows = 1000000
const NrOfColumns = 20
dim DBRow as DatabaseRecord

if SQLiteDB.Connect = FALSE then
MsgBox(“SQLiteDB.Connect = FALSE”)
Return
end if

s = “CREATE TABLE Data (Row INTEGER)”
SQLiteDB.SQLExecute(s)

for c = 1 to (NrOfColumns)
s = “ALTER TABLE Data ADD COLUMN col_” + Str© + " TEXT"
SQLiteDB.SQLExecute(s)
next

for r = 1 to NrOfRows
DBRow = new DatabaseRecord
DBRow.IntegerColumn(“Row”) = r

for c = 1 to NrOfColumns
DBRow.Column(“col_” + Str©) = Str® + " / " + Str©
next

SQLiteDB.InsertRecord(“Data”, DBRow)
next[/code]

Here is a simplified example of the API 2.0 code:

[code]var SQLiteDB as new SQLiteDatabase
var s as String

var r, c as Integer
const NrOfRows = 1000000
const NrOfColumns = 20
var DBRow as DatabaseRow

if SQLiteDB.Connect = FALSE then
MsgBox(“SQLiteDB.Connect = FALSE”)
Return
end if

s = “CREATE TABLE Data (Row INTEGER)”
SQLiteDB.ExecuteSQL(s)

for c = 1 to (NrOfColumns)
s = “ALTER TABLE Data ADD COLUMN col_” + Str© + " TEXT"
SQLiteDB.ExecuteSQL(s)
next

for r = 1 to NrOfRows
DBRow = new DatabaseRow
DBRow.Column(“Row”).IntegerValue = r

for c = 1 to NrOfColumns
DBRow.Column(“col_” + Str©).StringValue = Str® + " / " + Str©
next

SQLiteDB.AddRow(“Data”, DBRow)
next[/code]

Please fill a bug report and use API 1.0

feedback://showreport?report_id=58739

why are you using ALTER TABLE to create you table…

build a CREATE TABLE SQL statement… and execute ONE query not hundreds.

Same with populating… Build INSERT INTO statements

it will be MUCH faster, and probably use less of a memory footprint

s = "CREATE TABLE Data (Row INTEGER"

for c = 1 to (NrOfColumns)
s=s+",COL_"+str(c)+" Text,"
next
s=s+")"
SQLiteDB.ExecuteSQL(s)
//
//

s="INSERT INTO data (row,"

 for c = 1 to NrOfColumns
s=s+"col_"+str(c)+",")
next c
s=s+") VALUES("

dim t as string
for r = 1 to NrOfRows = new DatabaseRow
t=str(r)
  for c = 1 to  NrOfColumns
   t=t+"," +Str(r) + " / " + Str(c)
/// OR perhaps t=t+","+str(r/c) // depends on your requirement
  next
t=t+")"
 SQLiteDB.ExecuteSQL(s+t)
next

Well… for bug tracking reasons , his comparative example is just perfect, no need to have any optimization, just to prove the leak.

SQLiteDB.Connect

Should have no return value in 2019R3 and should be inside a try catch.

I get 3300 ticks with API 1, 6300 ticks with API 2 and 2700 ticks with ExecuteSQL instead of AddRow.
The alter table command is only executed 20 times, no?

Hello and thanks for your replies,
the examples above are not real code. I symplified and shortened the original code to only focus on the difference between the two API.

The problem was acknowledged by the engineering and pointed that the SQLite Plug-in needs a review in this part.