SQLiteDatabase.dylib hard crash on SELECT *

  1. ‹ Older
  2. 6 weeks ago

    Aaron H

    Mar 14 Pre-Release Testers Europe (Germany)
    Edited 6 weeks ago

    @ChristianSchmitz I found a bug. So a new plugin is available now for testing:
    Currently here

    :) The good news is that Christian's fixed plugin now works with DOD_Listbox. Yay!

    So that last bit I wrote above about RecordSets returned from the MBS not working the same was due to a bug in the MBS plugin which is now fixed.

    As far as I can tell, now we do have a drop-in replacement database object.

    Of course, the reason we're using this as a replacement is to do with memory management issues, so I do have concerns about RAM usage (thank you Jay for the tests). Before I stopped my tests yesterday I saw what looked like runaway RAM consumption when loading very large tables, but I wasn't sure if it was to do with Xojo, MBS, or MacOS.

  3. Emile S

    Mar 14 Europe (France, Strasbourg)

    @Aaron H but I wasn't sure if it was to do with Xojo, MBS, or MacOS.

    Download VirtualBox / Linux and Windows OS, install them, run your project there and you may get the answers.

  4. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    I'll working on a better cache to store them in UTF-8 and without the waste of memory there with those extra objects for options and cursors per field.

  5. Christian S

    Mar 15 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    A new build is here:
    https://www.dropbox.com/sh/dxhj0se3y81zmrp/AAA7xFyXKVkIWCxkm_JpMxqxa?dl=0

    The cache is now much better and just stores the value actually contained in the cell. (no more extra memory reserved for possible other value types). Strings are stored in UTF-8 to make them compact. And NULL values take no additional storage.
    So we are down to about 500 MB for my test project with 10 million values.

    Instruments shows memory is fully released when RecordSet goes out of scope. Just the memory management seems to fragment and size shown in Activity Monitor does not shrink as much.

  6. Greg O

    Mar 15 Xojo Inc

    @Aaron H :) The good news is that Christian's fixed plugin now works with DOD_Listbox. Yay!

    So that last bit I wrote above about RecordSets returned from the MBS not working the same was due to a bug in the MBS plugin which is now fixed.

    As far as I can tell, now we do have a drop-in replacement database object.

    Of course, the reason we're using this as a replacement is to do with memory management issues, so I do have concerns about RAM usage (thank you Jay for the tests). Before I stopped my tests yesterday I saw what looked like runaway RAM consumption when loading very large tables, but I wasn't sure if it was to do with Xojo, MBS, or MacOS.

    But... if you’re using a DOD listbox, you shouldn’t need to convert to MBS. They inherently pull only the data necessary to be displayed and requery for more rows when it needs to.

  7. Aaron H

    Mar 15 Pre-Release Testers Europe (Germany)
    Edited 6 weeks ago

    @Greg OLone But... if you’re using a DOD listbox, you shouldn’t need to convert to MBS. They inherently pull only the data necessary to be displayed and requery for more rows when it needs to.

    There are different ways to implement Kem's DOD_Listbox. The "on demand" aspect actually doesn't refer to the database but to the listbox rows, and its optimisation comes from a listbox with only as many rows as are visible, with data in a recordset that's held in memory, not called up with queries all the time. So the recordset isn't necessarily dynamic, the listbox rows are.

    When a large recordset is needed, there's no way around a large memory requirement. We need to be able to load multi-GB RecordSets, so this is why I'm working with the MBS object now, since the Xojo object crashes.

    There are still some inconsistencies, the main thing being now that MBS doesn't implement RecordSet.Edit and .Update, although it's often easy to get around that using SQL commands, without those RecordSet methods the object isn't "drop-in" which I'm making a goal here. There are times when using .Edit / .Update is more efficient, so it should be supported. It's possible with complicated subclassing / overloading, but ideally I hope those methods will be implemented in the plugin.

  8. Jay M

    Mar 15 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz A new build is here:
    https://www.dropbox.com/sh/dxhj0se3y81zmrp/AAA7xFyXKVkIWCxkm_JpMxqxa?dl=0

    The cache is now much better and just stores the value actually contained in the cell. (no more extra memory reserved for possible other value types). Strings are stored in UTF-8 to make them compact. And NULL values take no additional storage.
    So we are down to about 500 MB for my test project with 10 million values.

    Thanks Christian. Yes, it is much better than before, though still not as lean as Xojo's SQLite class. Here are my updated results:

    100,000 rows causes entire app to use 77 MB
    1,000,000 rows causes entire app to use 612 MB
    10,000,000 rows causes entire app to use 5.8 GB
    19,400,000 rows causes entire app to use 11.2 GB

    Compared to what I posted before, this is about 50% more than the Xojo SQLite class. But still better than 500%.

    I didn't post this before, but the MBS class is also slower (when AutoCache is on, verus Xojo SQLite class). And this hasn't changed much with the update. Here is a comparison between Xojo SQLite and the latest SQLDatabaseMBS:

    Xojo vs MBS
    100,000 rows: 0.7 sec vs 3.4 sec
    1,000,000 rows: 2 sec vs 13 sec
    10,000,000 rows: 69 sec vs 110 sec
    19,400,000 rows: 138 sec vs 215 sec

    Christian, I'm still grateful for your class, as it allows me to process almost unlimited SQLite records without memory restraints when AutoCache is off. Thanks again for your dedication to the Xojo community!

  9. Jay M

    Mar 15 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz Instruments shows memory is fully released when RecordSet goes out of scope. Just the memory management seems to fragment and size shown in Activity Monitor does not shrink as much.

    Yes, I noticed this also. But it's the same with Xojo's class, so it has something to do with Xojo's memory cleanup. Fortunately, most of this memory is reused when another recordset is created, so it's not a big deal.

  10. Christian S

    Mar 15 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    I would suggest to use pragma on SQLite to use big cache and not the default 2 MB.
    Disk I/O Is probably a big delay. What cache size do you use?

    Our data structures are not optimized for smallest possible size.

  11. Christian S

    Mar 15 Pre-Release Testers, Xojo Pro, XDC Speakers Germany
    db.SQLExecute "PRAGMA cache_size = 200000"

    brings down my test from 17 to 7 seconds. Still our plugin does extra UTF16/UTF8 conversion in-between, which I currently can't remove.
    Memory usage seems to be larger as we don't pack structures very close. Memory is allocated in 16 byte blocks and each value has a 16 byte block to control, another 16 byte block for the value data and possible more for the actual data.

    I think we keep it this way.

  12. Jay M

    Mar 15 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz db.SQLExecute "PRAGMA cache_size = 200000"
    brings down my test from 17 to 7 seconds.

    Didn't make any difference on mine.

or Sign Up to reply!