[Solved] Is there a maximum base file size?

[durham]

Hello all!

I need your help again. I have created a base table with text, numbers and photographs. I find the file size has grown to 85 MB and the program has slowed down, to the point where I sometimes have to close and re-open it. I thought the size for these files was pretty much limitless. Maybe the file size is causing these glitches - my computer is running everything else just fine.

Is there a maximum base file size?

Thanks for your help!

[r4zoli]

No maximum file size, but in practice the Base slow down with lot of records, especially with images.

You can faster it with running

Code: Select all

SHUTDOWN SCRIPT

command in Tools>SQL... command window. After that close and reopen file.

[durham]

Thanks! The photos did take a lot of space. Perhaps I can reduce their file size to speed things up as well.

I really appreciate your help. Thank you.

[DACM]

If you're unhappy with the database speed due to the size of the pictures (binary image files stored in the database), then you can regain performance by setting-up a separate table for the pictures as mentioned in the Hsqldb 1.8.0 Users Guide, on page 40.

And don't be intimidated by the discussion of 'nested SQL' commands and 'joins' necessary to store and retrieve 'Large Binary Objects' (LOB). That's how it all works under-the-hood, but you don't need to know those details in order to manage picture-file storage and display using Forms in Base. You'll simply create a separate table for picture storage and then use Base Form facilities to manage the storage and display as necessary. In other words, Base SubForms do the required 'nested SQL' automatically. A Base 'Image Control' is used on the SubForm to facilitate both storage and display of the associated picture(s) for each record. You decide whether you want one picture per record in the main table (1:1 relationship), or many pictures per record (1:n relationship).

You should also be familiar with this discussion of the 'cache' size settings used by OpenOffice.org. The default settings are very restrictive when working with large tables/queries with large binary-objects like photos. By default, you're data is squeezed through a narrow pipe (about 3MB of available RAM cache based on 6MB total including overhead). This cache size was selected by the *Office developers despite the recommended 48MB default for HSQLDB 1.8. You may find the default cache settings sufficient if you adopt the HSQLDB 1.8 workaround for photo storage. But you can adjust the cache settings using the basic steps in this post to access and edit the 'properties' file of your database.

Ultimately, this discussion led the user to switch to HSQLDB 2.x which supports the BLOB (Binary Large Object) data-type for things like photo storage. This improved version of HSQLDB also uses a separate table for LOB (photo) storage, but it creates and manages the table automatically and transparently from the user's perspective. Like SubForms in Base, this transparency eliminates the need for user-generated 'nested SQL' queries for image retrieval. And like the HSQLDB 1.8 workaround, HSQLDB 2.2.x maintains database performance no matter how many LOBs are stored, while increasing the maximum table size to 2TB. Otherwise, the entire database is limited to 2GB by default with HSQLDB 1.8. Well, actually, HSQLDB 1.8 can be setup to create databases with an 8GB limit, but this must be accomplished early in the design phase before adding tables and data. You could always migrate a 2GB limited database to a new one with the 8GB limit with HSQLDB 1.8. But if you're willing to go to that trouble then you might as well upgrade to HSQLDB 2.2.8 in the process and get a virtually unlimited database size with up to 2TB per table. But you would need to migrate your 'embedded database' to a split-file database and then upgrade to HSQLDB 2.x -- see my signature links for details.

Here's a quick demo of the HSQLDB 1.8 workaround and SubForm utilization:
Church_Directory.odb
...