Storing files on the filesystem

Question

I have an application that stores images in a database. Now I have learned that this isn't the best way to do this because of performance.

I've started extracting all the "blobs" out of the database and added them (through a virtual folder) into the data folder

the problem is that I allready have 8000 blobs stored and if I put them in the folder like this "data/< blobid >/< blobname.extension >" this folder will contain to many folder to be manageable.

I was wondering how you can store your files the best? group them in dates of creation like this "data/< year >/< month >/< day >/< blobid >/< name >". I also have to add that our files are stored in a tree in the database. I was wondering if I should map that treestructure to the filesystem, the only problem there is that you can move branches. That would mean that I have to move the branches on the filesystem.

Any help is welcome.

Grtz, M

Answer 1

What version of SQL Server are you using? Because if you are using 2008 you can use the FILESTREAM datatype to store images. This is just as efficient as storing them on the filestore but without any of the associated hassle. See Getting Traction with SQL Server 2008 Filestream.

Answer 2

Do you need to store the files in the relevant tree structure? If not you could name the file /YOURFOLDER/blobid_blobname.extension. This way the upload folder acts purely as a repository for the data rather than mimicking the data structure.

Answer 3

A simple strategy is grouping according to the first [few] digit(s). E.g.:

1/
    2/
        123.blob
        129.blob
    5/
        151.blob
2/
    0/
        208.blob

That way, you know you'll never have more than 10 subdirectories in a directory. You may of course use more or less levels (of directories) and/or more digits per level.

A more complex, dynamic system could create sublevels on demand: If the number of blobs in a certain directory exceed a preset maximum, create another 10 subdirectories and move the files in.

Answer 4

Most filesystems for BLOB data will set up a number of subdirectories. For instance, if you have IDs 1-10000, you could have:

00/
    00/
    01/
    02/
        00020.blob
        00021.blob
        ...
    ...
01/
02/
03/
...

The other question I have back for you: why is it so bad for you to manage them as BLOBs?