SQLITE Memory Issues?

Databases
1

I’m getting a huge memory spike (500MB) when making a very modest addition (~2000 records to a single table) to a SQLite db.

Actually the first time through the process it doesn’t spike, but additional spikes occur every time I repeat the process thereafter.

That behavior made me think that I must be creating additional objects, and/or not clearing out old ones, each time through. But I have been through the code and created some monitors to watch thread statuses, and I have become comfortable the controlling thread and other objects are properly going out of scope at the end of each iteration (and I’m manually forcing a few objects to Nil after using them, just to be sure). I have looked hard for any circular references and have used a WeakRef to refer from the thread to the DB.

Although I am not entirely certain at this point, I wonder if there is perhaps a SQLite weirdness. Although I don’t know whether they are supported in Xojo’s SQLite implementation, I’ve tried a couple of pragmas (shrink_memory and sqlite3_db_release_memory), but no joy. I’m not sure if that’s because they are not implemented, and so doing nothing, or if the problem lies elsewhere, and so they have no effect.

[EDIT: The database size on disk is about 90 MB and the records I’m adding are about 25 columns, text only, and less than 1 KB each.]

Any general thoughts would be appreciated.

Regards,
Will

IDE on Windows 11 64-bit.
32 GB RAM available.
Xojo v2024r2.1.
Desktop app - Windows 64-bit only.
No plugins related to SQLite.

2

If you think there is an objects leak you can pause you code in the debugger and then look at “Globals > Runtime > Contents” to see a list of each object that Xojo has created. If you run through a few times you can see if something is accumulating there. In code you can access the Runtime.ObjectCount and Runtime.MemoryUsed properties, to keep a numerical eye on Memory usage and number of created Objects.

3

Thanks Ian. I have done that (as well as carefully watching memory use and the number of handles indicated by the task manager). The Contents are so extensive that it takes several seconds from them to load, and what I find is mostly ton of inscrutable Delegates. No clue what those are or where they come from.

4

If you are not creating the delegates yourself then it could be the SQLite plugin.

Can you produce a cutdown version that just does the inserts and see if that suffers the same problem. If so it would be worth adding a bug report, together with the example project.

If the problem isn’t there then you need to expand your search.

5

I may have to do that, but I think I will try a few other ideas first.