REALDatabase causes memory leak?

[tompaulman]

I’m developing a hobby project in REALBasic 5.5.5 that involves lots of data stored in REALDatabase file with 3 tables. The largest table has about 9000 rows but this is just a proof of concept. If I ever finish the app, it will work with about 100k rows. Despite the number of rows, the database is not heavy - the RDB file has less than 1 MB. Each table contains 7 or less columns and they are all mostly integer, with one date column, one boolean column and one string column.

Now the problem:
I need to execute a for...next loop with large number of iterations, each of which will run a SELECT query, retrieve some values from the RecordSet, create an object using the data, perform some calculations and print the result to an EditField. It worked fine until my data grew bigger, and then, at one point, OS 9 froze during the execution.
In Build settings I increased the memory up to 112 MB. With 320 iterations, the whole loop takes about 45 seconds, which is solid given the amount of code execution and data involved. However, once the execution is done, almost all memory is used up. I tried googling about known memory leaks in REALBasic but didn’t come across anything that would explain this.
Initially I thought the issue was in my code, but I couldn’t find anything wrong. So I decided to omit all the object creations and calculations and I tested a simple code that did nothing but run SELECT queries in a loop and print one field from each RecordSet.

    Dim i, lastId as Integer
    Dim rs as RecordSet
    lastId = DBHelpers.getLastId // DBHelpers is a module that I created for easier interaction with the database
   
    for i = 1 to lastId
      rs = db.SQLSelect("SELECT * FROM game_data WHERE gameId = "+Str(i)+" AND gameType = 'A'")
      textField.text = textField.text+rs.field("n").StringValue + Chr(13)
    next

And I got the same issue with clogged memory. Since the RecordSets are overwritten / garbage collected on each iteration, they should not get stuck in memory. Also, I have another table with only 290 rows. And when I modify this code to execute on the smaller table, the memory use increases only slightly, despite the fact that the number of select queries is identical. So the issue must be related to the number of records in table. Which is ridiculous, because databases are supposed to be optimised for millions of rows.

Memory before I executed the code:


Memory after I executed the code:


Finally, I tried running DebugDumpObjects before and after the execution. In both cases it shows the same objects in memory. Unfortunately it doesn’t show the amount of memory each object takes.

Did anyone come across the same issue, or can anyone think of a possible solution?

[Jubadub]

I'm late in replying to this, but just in case...

It's as if your app is caching the actual tables. By default, considering all that you already tried, the only solution that comes to my mind is to try not using a REALdatabase file, but instead another database solution. Maybe even another kind of data storage solution or approach altogether. Then see how that goes.