Deleting pictures doesn't reduce file size!

Discuss the database features
Post Reply
durham
Posts: 38
Joined: Wed Feb 03, 2010 2:39 am

Deleting pictures doesn't reduce file size!

Post by 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. :roll:

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
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: what the heck have I done now?!

Post by RPG »

Hello

Maybe you can find here information.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: what the heck have I done now?!

Post by 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.
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
durham
Posts: 38
Joined: Wed Feb 03, 2010 2:39 am

Re: Deleting pictures doesn't reduce file size!

Post by 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
durham
OOO 4.1.1 on winows 7 64bit
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Deleting pictures doesn't reduce file size!

Post by RPG »

Hello

There is no automatic garbage procedure.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Deleting pictures doesn't reduce file size!

Post by 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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
durham
Posts: 38
Joined: Wed Feb 03, 2010 2:39 am

Re: Deleting pictures doesn't reduce file size!

Post by durham »

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

Where do I find SHUTDOWN?
durham
OOO 4.1.1 on winows 7 64bit
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Deleting pictures doesn't reduce file size!

Post by r4zoli »

Use Tools>SQL... command window.
Close file and reopen it.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
durham
Posts: 38
Joined: Wed Feb 03, 2010 2:39 am

Re: Deleting pictures doesn't reduce file size!

Post by 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
durham
OOO 4.1.1 on winows 7 64bit
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Deleting pictures doesn't reduce file size!

Post by DACM »

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
Post Reply