Blobs or Filesystem for images

[Thunder]

the problem is that the blob size on SERVOY is much bigger than the original pdf (2mb vs 600kb or so, depending what's in the pdf)...

This reminded me of a solution which we built for a customer which already does store files as blobs. The solution is supposed to store word files exclusively, but during building it, I was sticking any old file in there as a test. I have just had a look at it and retrieved the 3 attached files that are currently in it. They are 2 jpegs and 1 tiff totalling 6MB.

The sybase .db file for that solution is 45.9MB!!! For reference point, I have 10 x .db files in the Servoy database folder and the next biggest one - the servoy repository - is only 7.8MB, out of the other .db files that I have built, the biggest (a much more extensive solution than the 45MB solution) is 3.8MB and they from there go down to about 1.5MB.

It seems that Servoy does swell far beyond what the files being put in are (I could have done this more scientifically by exporting the solution and importing as fresh empty version and starting from scratch adding files) it does seem however that there is some kind of vacuum in Servoy which adds stuff over and above the blobs that should actually be in there, or that they are not being removed by deleting records. As another test, I deleted the record that contained the biggest file (the 5.7MB .tiff)

This had 2 very surprising effects. Firstly, it took about 7 seconds to delete the record on my dual 2.0Ghz G5, and second - and most worrying, it increased the .db file to 89.3MB. I opened and closed Servoy a couple of times, same thing. I then shut down dbsrv9 as well and restarted servoy, this brought the size of .db back to 45M.9MB (did not reduce by the size of the file being removed either). Deleting one record containing one blob of 5.7MB doubled the size of the Sybase .db file until dbsrv9 was quit and restarted...

I remain entirely unconvinced. I think Andrea said it best, a filesystem is for files - especially when I don't understand what Servoy (or in fact Sybase) is actually doing with these blobs....

[Harjo]

Good point!

maybe Jan A. knows a Sybase-guru who can answer this behaviour of Sybase (or Servoy?)

[pbakker]

I think the behavior of blobs in the DB is not something Servoy controls, but is DB specific.

As for a DB size not shrinking when removing a record: That is default DB behavior. space occupied once remains occupied by the DB, untill you rebuild the table.

As for the size temporarily increasing: Probably has to do with tracking, rollback segments, archive options etc.

BTW: on a filesystem, the space also remains "occupied" after deleting a file. Only because the filename entry is removed from the FAT, you cannot see it anymore. To really remove a file, you need to clean up your harddrive

5 more cents...

Paul

[Thunder]

As for a DB size not shrinking when removing a record: That is default DB behavior. space occupied once remains occupied by the DB, untill you rebuild the table.

Hmmmmmm.... I want to avoid the database needing maintenance. Even more reason I think to keep the database lean and use the filesystem for file storage...

BTW: on a filesystem, the space also remains "occupied" after deleting a file. Only because the filename entry is removed from the FAT, you cannot see it anymore. To really remove a file, you need to clean up your harddrive

Not on my harddrive it doesn't..... I remove a file and I get the space back - directly, immediately and observably. The fact that the actual data of the file is not actively erased (or written over) when I empty my trash is simply a case of unneccesarily wasting cycles (it is more work to actively write over the space a file occupies than not - which makes erased files retrievable). When deleting a file, the directory of my disk is told that the space previously occupied by the file is now available again. This has the effect of giving back bytes. At least on my Mac's HFS+ disk it does. Not sure about NTFS or FAT/FAT32 but surely it is also just a directory issue...

Back on topic, if I am right about the file system giving back space for deleted files, it seems that this is another reason to not use Sybase for file storage. I don't forsee that there will be a tremendous number of files being deleted, but if for example someone accidentally puts a 50MB file in the database rather than the 5MB version they wanted to, then deletes the 50MB version and adds the 5Mb version, we end up with an awful lot of extra, useless data which won't disappear until the database is rebuilt..... Not looking good..

[a.mariottini]

Back on topic, if I am right about the file system giving back space for deleted files, it seems that this is another reason to not use Sybase for file storage. I don't forsee that there will be a tremendous number of files being deleted, but if for example someone accidentally puts a 50MB file in the database rather than the 5MB version they wanted to, then deletes the 50MB version and adds the 5Mb version, we end up with an awful lot of extra, useless data which won't disappear until the database is rebuilt..... Not looking good..

This is not the way things go and this is not what Paul said.

The 50Mb are still in the database, but they are reused for
newer data. Exactly the same way the filesystems does.
Simply the database subtract space to the filesystem, but
internally the database has 50Mb of free space to store data.
From the point of view of filesystem this is used space, from
the point of view of the database not.

But this is a sleaziness......