Greetings,
I have A SQLite Database with around 13 mil records, and I try to do a select statement and the app crashes immediately with the DB error : Unable to allocate memory requested for the operation.
Any idea what is wrong there and how should I avoid that error ? This is the first time I get this kind of error.
XOJO 2019R3.2
MacOS Mojave
MacOS Desktop app.
Thanks
Please show us the select and the schema.
CREATE TABLE IF NOT EXISTS base1 (
Nct2 integer(9) NOT NULL,
R11 integer(2),
R2 integer(2),
R3 integer(2),
R4 integer(2),
R5 integer(2),
R66 integer(2),
Qr11 integer(2),
Qr2 integer(2),
Qr3 integer(2),
Qr4 integer(2),
Qr5 integer(2),
Qr66 integer(2),
Rnx1 integer(2),
Rnx2 integer(2),
Rnx3 integer(2),
Rnx4 integer(2),
Rnx5 integer(2),
Rnx6 integer(2),
R12 integer(3),
R23 integer(3),
R34 integer(3),
R45 integer(3),
R56 integer(3),
P12 integer(2),
P23 integer(2),
P34 integer(2),
P45 integer(2),
P56 integer(2),
Rr1 integer(2),
Rr2 integer(2),
Rr3 integer(2),
Rr4 integer(2),
Rr5 integer(2),
Rr6 integer(2),
Nctr integer(9),
P16 integer(5),
Rr12 integer(2),
Rr22 integer(2),
Rr32 integer(2),
Rr42 integer(2),
Rr52 integer(2),
Rr62 integer(2),
id integer PRIMARY KEY AUTOINCREMENT,
Krr16 integer(5),
Kkrr16 integer(9)
);
and the code in the Thread Run is :
[code]Var fTot As RowSet
Var sql As String
sql = “SELECT Rr12, Rr22, Rr32, Rr42, Rr52, Rr62, Nct2, Kkrr16 FROM base1 WHERE Nct2 > 3017 AND Kkrr16 = 0”
Try
fTot = bDB.SelectSQL(sql)
If fTot <> Nil Then
pTot = fTot.RowCount
pSt = 0
Me.AddUserInterfaceUpdate("pSt":pSt)
Me.AddUserInterfaceUpdate("pTot":pTot)
End If
Catch err As DatabaseException
Break
End Try[/code]
The funny part is that it says on the website under SQLiteDatabase that
[quote]Threading
SelectSQL and ExecuteSQL statements do not block when called from within Threads.[/quote]
But the app freezes, it brings the CPU to 99%, ram around 2.3 GB then decreases and then crashes with that error. But on this time the app is unresponsive and I get that spinning wheel and this is in a thread.
I tried same project in 2019R3.1 and same error .
I use SQLPro for SQLite and it works smooth there with no errors for the same query where I get around 1 mil records . So it seems that the issue is somewhere in XOJO or in the SQLite Version I guess, but if it was in the SQLite version then it would not work on SQLPro as well I assume .
13 million rows requires about 4Gb in memory since every integer when read from disk is converted to an signed 8 byte integer
See https://sqlite.org/datatype3.html 2. Storage Classes and Datatypes
It might also require it being in physical RAM
Open Activity Monitor & see of that reveals anything
Is there a crash log (I assume this is a hard crash?)
Why you writing "integer(9) " or “integer(2)” etc? This has no meaning in SQLite. Probably won’t harm as SQLite will just take them as integers.
13 million rows is not that many to SQLite which can handle database of many Gbytes.
You running this in the debugger or as an app? Have you tried stepping through with the debugger?
[quote=497281:@Norman Palardy]13 million rows requires about 4Gb in memory since every integer when read from disk is converted to an signed 8 byte integer
[/quote]
But he has a where clause so won’t necessarily get 13M rows.
[quote=497281:@Norman Palardy]13 million rows requires about 4Gb in memory since every integer when read from disk is converted to an signed 8 byte integer
See https://sqlite.org/datatype3.html 2. Storage Classes and Datatypes
It might also require it being in physical RAM
Open Activity Monitor & see of that reveals anything
Is there a crash log (I assume this is a hard crash?)[/quote]
Hello Norman, I do have 32 Gb ram on my MacBook so that is not an issue , while monitoring on the RAM side on Activity monitor the app goes to max 2.3 Gb and then it decreases until It crashes the app. because I have a break on the Exception the app does not crashes and I stop it manually, I guess I could build it and run it as build and see if it crashes and get some logs.
[quote=497282:@Tim Streater]1) Why you writing "integer(9) " or “integer(2)” etc? This has no meaning in SQLite. Probably won’t harm as SQLite will just take them as integers.
13 million rows is not that many to SQLite which can handle database of many Gbytes.
You running this in the debugger or as an app? Have you tried stepping through with the debugger?[/quote]
I guess the DB was imported from a FoxPro database where it had all those Column sets and they imported them with structure as well.
Well that was the point of it and apparently it crashes while in the Thread while doing the bDB.SelectSQL statement as it reaches out of memory of some internal limitation of the driver I guess or library or some sort.
Debugger only and once it reaches to the statement it crashes with that error.
Having same select in SQLPro it finishes all the record in 19.7 Seconds.
I Just did a test on Windows 10 with same code and same DB and apparently it has same effect on the code and workflow, same error, app goes up to 2.38 GB on ram then decreases and stops and crashes with same error. So I assume that this might be a XOJO limitation I guess.
I hope XOJO team could do a test and maybe fix the issue if indeed there is a XOJO issue.
The weird part , even if the code is in the Thread I still get all the interface blocked and the spinning wheel and eventually the app becomes unresponsive, even if it should not.
Aurelian,
Maybe the SQLite Pragma cache_size? SQLite Pragma cache_size
In Xojo use it like: myDB.SQLExecute "PRAGMA cache_size = 40000"
database plugins in xojo are blocking the user interface. even with a thread. for years.
don’t know if it will be fixed one day
if you want that behavior, the only way is to use MBS database plugins.
Apparently same issue on Console app as well, crashes on the same size .
[quote=497291:@brian franco]Aurelian,
Maybe the SQLite Pragma cache_size? SQLite Pragma cache_size
In Xojo use it like: myDB.SQLExecute "PRAGMA cache_size = 40000"[/quote]
Apparently doing that and even increasing it to 6000 it went up to 3.2 Gb and then crashed straight away with same error. so it went little bit higher but crashes, I guess some memory management in XOJO goes wild and crashes all. So far no matter whatever I do more than 3.2 Gb does not go and crashes. Is this linked maybe on 32 Bit , some bits of XOJO Plugin not moving on 64 Bit maybe as 3.2 Gb crash is kind of the 32 Bit memory Limit as far as I know.
[quote=497292:@Jean-Yves Pochez]database plugins in xojo are blocking the user interface. even with a thread. for years.
don’t know if it will be fixed one day
if you want that behavior, the only way is to use MBS database plugins.[/quote]
Well I did not wanted to use any plugins but apparently none of the XOJO DB plugins are reliable so I guess I’ll try to do some tests with Christian’s plugins and see if there is a better result than this.
Where is dDB defined and the database opened?
Have you tried opening the db with the sqlite3 CLI program?
[quote=497297:@Tim Streater]Where is dDB defined and the database opened?
Have you tried opening the db with the sqlite3 CLI program?[/quote]
Yeap just did the test, same error same Memory limit crash, 3.2 Gb , and I put all the code in the Console Run event to avoid any issues. Looking now on MBS Plugins to see what options I have there and if it works
No, I was asking about opening the db and doing the select by hand in the sqlite3 CLI program, not a Xojo program written for console. And I still want to know where dDB is defined and the database opened in your Xojo program.
Ah, well I said I used SQLPro for that and it works perfectly on the Query side . Command line I’ll have to dig some code and do some tests for that as well to see if it works or not
its possible that “something” in the sqlite plugin Xojo provides is restricted to 32 bit size