In memory DB size

Is there a way to see how much memory an in-memory SQLite database is consuming?

I played around with some Pragma Values, I put in a Select Statement. But the values don’t seem right…
The page_count tells me the number of pages in the Database. The page_size value tells me the size of each page. I assume that I just have to multiply those values.

Where “db” is an SQLite database:

dim pCount, pSize as Integer
dim rs as RecordSet

rs = db.SQLSelect("PRAGMA page_count")
if rs = nil then Return 0
pCount = rs.IdxField(1).IntegerValue

rs = db.SQLSelect("PRAGMA page_size")
if rs = nil then Return 0
pSize = rs.IdxField(1).IntegerValue

Return  pCount * pSize

But maybe there is another approach? Maybe there is a way to determine the memory footprint of the db property. As I said: db is a property of type SQLiteDatabase.

Any suggestions?

What values do you get?

Well… when I change data to get smaller records, after performing a VACUUM statement the sizes remain the same.

I use an in-memory db to cache some graphical elements. Re-rendering all these elements would consume too much CPU time.
The reason why I choose for a db, is that the elements are easier to find. And it is easier to give each record a time-to-live. This keeps the cache nice and small, as it gets rid of images that are not used for a while.

But, I want to check how large the cache is. Thus, I need to know the footprint of my cache DB. If it is getting too large I might want to delete some older records or something like that.

I was hoping that there was a way to read the size of my db property the same way I can do with a memoryblock. This way I can check it’s size much easier.

You can set the maximum size like this:

PRAGMA page_size = 512 PRAGMA max_page_count = 65536
You will get an error when this size would be exceeded.

Yeah, but I want to know that beforehand. My guess is that the PRAGMA values I used were correct. But if a record is smaller than page, it will still use the entire page. OR something along those lines.

I could set the page_size to a smaller value though… I think…

For future MBS SQL Plugin, I add a MemoryUsed function to query memory usage.