Deleting pictures doesn't reduce file size!
Deleting pictures doesn't reduce file size!
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).
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).
durham
OOO 4.1.1 on winows 7 64bit
OOO 4.1.1 on winows 7 64bit
Re: what the heck have I done now?!
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: what the heck have I done now?!
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.
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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: Deleting pictures doesn't reduce file size!
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
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
durham
OOO 4.1.1 on winows 7 64bit
OOO 4.1.1 on winows 7 64bit
Re: Deleting pictures doesn't reduce file size!
Hello
There is no automatic garbage procedure.
Romke
There is no automatic garbage procedure.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Deleting pictures doesn't reduce file size!
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.
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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: Deleting pictures doesn't reduce file size!
Looked for SHUTDOWN in my toolbar and in the help files but can't find it.
Where do I find SHUTDOWN?
Where do I find SHUTDOWN?
durham
OOO 4.1.1 on winows 7 64bit
OOO 4.1.1 on winows 7 64bit
Re: Deleting pictures doesn't reduce file size!
Use Tools>SQL... command window.
Close file and reopen it.
Close file and reopen it.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: Deleting pictures doesn't reduce file size!
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
Thanks for your help. I really appreciate it!
durham
durham
OOO 4.1.1 on winows 7 64bit
OOO 4.1.1 on winows 7 64bit
Re: Deleting pictures doesn't reduce file size!
Use CHECKPOINT DEFRAG or perhaps SHUTDOWN COMPACT
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria