Deleting pictures doesn't reduce file size!

[durham]

OK, I just don't understand this!

I have a database of about 30 items for a report I am doing. I added some pictures and it got quite slow - and large 72MB. I asked for help here and the answer was that my picture files had greatly enlarged the database and was slowing it down. That being the case, I decided to reduce the file size of the pictures - in most cases going from 400kb to 20kb. I replaced all the pictures in the database and deleted the larger files from the computer. I then saved the database and closed it...

What the heck the database is now 96MB!

I deleted the old pictures in the database before I entered the new ones.
I deleted the old pictures from the computer - and emptied the trash.

How can the database now be larger when it has smaller picture files?? That was the only change I made.

I need to store maximum information in the smallest file size possible - and I seem to be going the other way!

I sure would appreciate any help you can give me.

Durham

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[RPG]

Hello

Maybe you can find here information.

Romke

[DACM]

I would also try issuing ' SHUTDOWN COMPACT ' from Tools > SQL... then restart Base.

And you may find this or that interesting.

The bottom line: the built-in database engine (HyperSQL) supports large binary data (pictures, video, etc.), but not in a modern or efficient way so you get a performance drop when simply adding pictures to a table. But a workaround documented in the HyperSQL database (HSQLDB) documentation regains the performance lost, but at the cost of increased complexity for queries and setup requires some additional knowledge of relational database table design techniques and structures. And even with the workaround, large pictures (>3MB each) may load slower than necessary unless you adjust the HSQLDB engine's cache memory settings manually.

Otherwise, there is a more modern approach which fixes all the problems and implements the dual-table method automatically by design, so the user does not need to use nested SQL statements. That approach requires native 'Binary Large OBject' (BLOB) support available through upgrade to HSQLDB 2.0. This upgrade may not be for everyone because it currently (until OOo 3.4) requires other workarounds during database design and ultimately disables support in Base for the native "Embedded database" -- although you can migrate these legacy databases to the new format as necessary with some effort. You can read more about this database engine replacement here.

[durham]

Thanks for your help - I think I will set up differently in the future.

However, I am still really curious to know why the database increases in size after I replace the larger picture files with smaller ones. I expected the database to get smaller - not increase by 20mb!

Any ideas?

Thanks.
Durham

[RPG]

Hello

There is no automatic garbage procedure.

Romke

[r4zoli]

Because OOo Base/built-in hsqldb creates backup file which contain earlier database state.
In your case you added smaller pictures, the bigger version remains in backup file within odb file database, in consequence, the file size grows with added picture size.
Then you can remove this backup file with SHUTDOWN.

[durham]

Looked for SHUTDOWN in my toolbar and in the help files but can't find it.

Where do I find SHUTDOWN?

[r4zoli]

Use Tools>SQL... command window.
Close file and reopen it.

[durham]

Thanks! I followed your directions - but it only dropped 3mb. There must be something else going on. I'll just have to live with it for this job and be more careful starting out next time.

Thanks for your help. I really appreciate it!

durham

[DACM]

Use CHECKPOINT DEFRAG or perhaps SHUTDOWN COMPACT