SQLite Forum

Question about memory management
Login

Question about memory management

(1) By Jonny Dex (JonnyDex) on 2021-11-04 15:39:08 [link] [source]

Firstly, hello. I'm brand new to SQLite but 3+ decades of coding in Clarion from Soft Velocity.

I implemented SQLite as I was told it is fast, and that it is. It would be perfect for my application save for one problem. As I import csv files into databases - one for each subject I am importing - my computer memory fills right up. All 64 GBs get used and the system crashes. I'm running 30 programs at once and each is doing an import and there are millions of records per import. I can manage 5 imports at once to be safe. I have tried closing the database every 20,000 records to free up buffers, but that has not helped. Meanwhile I am back to Btrieve which is working but slower.

Lastly, one other perplexing issue is that the import process self terminates between 40% and 65% of the way through the imports with SQLite, but with Pervasive, the imports complete. Any comments?

SQLite has so many advantages that I would really like to resolve these problems Also, I love that I can distribute my app without buying another Btrieve license.

Can anyone enlighten me as to what is going on with memory. My first thought is that SQLITE_DEFAULT_MEMSTATUS being set to zero is the culprit, but, like I say, I'm brand new.

(2) By Richard Hipp (drh) on 2021-11-04 15:47:01 in reply to 1 [link] [source]

As I import csv files into databases - one for each subject I am importing - my computer memory fills right up.

Can you be more specific about what you are doing to accomplish this import? Are you using the CLI? If so, what commands are you using? Or did you write your own program/script to do the import? Can we see it?

(3) By Jonny Dex (JonnyDex) on 2021-11-04 16:15:47 in reply to 2 [link] [source]

Not using the CLI. I wrote a program in Clarion. Clarion has native support for SQLite. All I have to do is place the sqlite3.dll in my developer's folder and compile. We have a data dictionary that holds the name of the driver to use. I simply replaced Brtieve with SQLite and ran the program. I know that is not a lot of help. I do not want to use the CLI as I want to run multiple imports from a GUI. I accomplish that by calling the same program over and over from a master program until I have 30 instances of them running and each is writing to it's own database.

The code itself is simple. I read in a csv record, assign the fields to the fields in the SQLite database and then add them to the database. Loop until done.

(4) By Warren Young (wyoung) on 2021-11-04 16:18:42 in reply to 3 [link] [source]

If importing your CSV via the CLI works, then the problem is in the Clarion code or its SQLite adapter.

Try one and see.

(5) By Larry Brasfield (larrybr) on 2021-11-04 16:53:38 in reply to 1 [link] [source]

Unless you are using an in-memory database, doing a CSV import should not present any particular problem for the CLI.

Setting SQLITE_DEFAULT_MEMSTATUS to 0 will hinder getting memory usage data from the SQLite instance, but will not be the culprit in excessive memory use. If you want to see where that problem is, you may want to leave that option at its default (1).

I think you should explain how your Clarion code does the import before hoping for much help here. SQLite is not generally a resource hog.

(6) By Jonny Dex (JonnyDex) on 2021-11-04 17:17:26 in reply to 5 [link] [source]

Actually, this has helped. Thank you. I have learned that this is not a problem or bug that others are having. Now I will try other avenues of discovery.

(7) By Jonny Dex (JonnyDex) on 2021-11-04 20:58:08 in reply to 5 [link] [source]

Could this have anything to do with my issue?

       https://sqlite.org/c3ref/db_release_memory.html

Free Memory Used By A Database Connection int sqlite3_db_release_memory(sqlite3*); The sqlite3_db_release_memory(D) interface attempts to free as much heap memory as possible from database connection D. Unlike the sqlite3_release_memory() interface, this interface is in effect even when the SQLITE_ENABLE_MEMORY_MANAGEMENT compile-time option is omitted.

(8) By John Dennis (jdennis) on 2021-11-05 06:58:20 in reply to 3 [link] [source]

The code itself is simple. I read in a csv record, assign the fields to the fields in the SQLite database and then add them to the database. Loop until done.

"add them ... loop until done" suggests a series of inserts, rather than an import. Are these being done in a single transaction? Or each being done in its own transaction?

(9) By Simon Slavin (slavin) on 2021-11-05 07:52:35 in reply to 7 [source]

Your problem appears to be related to Clarion, not SQLite.

Use the SQLite Command Line Shell to import one of the CSV files, as someone upthread asked. Does it work ? In that case, the problem is with your code, or is internal to Clarion. In that case, you are probably better off asking for help in a forum about Clarion.

If the SQLite Command Line Shell fails to import the CSV file please tell us. We willl be able to investigate since it was written and is supported by the same team which wrote SQLite itself.

(10) By Jonny Dex (JonnyDex) on 2021-11-13 14:01:54 in reply to 9 [link] [source]

I agree and thank you.