In memory DB size

  1. 2 months ago

    Edwin v

    Aug 11 Pre-Release Testers, Xojo Pro The Netherlands

    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.Close
    
    rs = db.SQLSelect("PRAGMA page_size")
    if rs = nil then Return 0
    pSize = rs.IdxField(1).IntegerValue
    rs.Close
    
    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?

  2. Greg O

    Aug 11 Xojo Inc Somewhere near Raleigh, NC

    What values do you get?

  3. Edwin v

    Aug 11 Pre-Release Testers, Xojo Pro The Netherlands
    Edited 2 months ago by Edwin v

    @Greg O'Lone 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.

  4. Eli O

    is not verified Aug 11 Europe (Berlin, Germany)

    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.

  5. Edwin v

    Aug 11 Pre-Release Testers, Xojo Pro The Netherlands

    @Eli O 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...

  6. Christian S

    Aug 11 Pre-Release Testers, Xojo Pro Germany

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

or Sign Up to reply!