API 1 vs API 2 SQLite memory consumption

Databases
1

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]

2

Please fill a bug report and use API 1.0

3

<https://xojo.com/issue/58739>

4

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
5

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

6 
SQLiteDB.Connect

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

7

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?

8

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.

9

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