In my experience storing and retrieving images from MS SQL is
significanly slower compared to pulling images via a file system.
My view as well - all apps that do this are moving away or
have moved away from this SQL method.
I've seen a lot of negatives on storing files as BLOBs in a database in this
thread. I think it's worth looking at the other side of the coin.
I'm not sure that it is universally true that the data access performance is
dramatically slower streaming bytes out of a database. Or more to the point,
I'm not sure that there's any reason to think a priori that performance
would be unacceptable. It seems to me that database performance would be
highly dependent on factors like indexing strategy, table partitioning,
table allocation into file groups on disk, fill factor, etc. There are
various client-side caching strategies, such as ASP.NET output caching that
can mitigate any performance issues.
There are a lot of large commercial applications that store BLOBs in a
database and not independently in the file system: Microsoft SharePoint and
Exchange, Novell GroupWise, IBM Lotus Notes, EMC Documentum...
Storing BLOBs in directly in the database has a couple of other benefits
that I haven't seen discussed.
Relational Integrity comes for free with any relational database system
worthy of the name. You know that the BLOB is going to be there if there is
a foreign key constraint to other data in your database. If you use a string
"pointer" to the file system, you have no assurance that the file will
exist. This is not usually a problem until a couple of years down the road
when the original developers are long gone and someone needs to move,
migrate or upgrade the application or the underlying OS. Then all the files
go missing. If you want to move the app to another server OS, you could be
in more trouble because the file path embeds platform-specific notions about
file-separator character, PATH_MAX and valid characters in paths and
filenames that are platform-specific.
Transactional Integrity comes for free with the database. A file is inserted
or it isn't. You can't have a part of a byte stream written to a file
buffer. Since you have a primary key in every table that holds your BLOBs,
you don't have to worry about whether any file might be overwritten by the
next upload.
Finally, the database acts as a sandbox between your application and the
underlying OS. It's pretty inconceivable that a malicious file inserted into
your database could somehow be executed by your underlying OS in the
ordinary course of things. Your clients could be affected but not the
server. If you're using .NET as your client database client, you also have
the option of code-access security to limit the I/O of your app so that it
can't possibly write to the server outside of the database:
//Permit read environmental variables PATH and PFPRO_CERT_PATH and nothing
else
[assembly: EnvironmentPermission(SecurityAction.RequestMinimum, Read =
"PATH;PFPRO_CERT_PATH;")]
//Permit read HKLM\Software\Microsoft\.NETFramework registry keys and
nothing else
[assembly: RegistryPermission(SecurityAction.RequestMinimum, Read =
"HKEY_LOCAL_MACHINE\\Software\\Microsoft\\.NETFramework;")]
//Permit read assemblies from the GAC and no other file IO
[assembly: FileIOPermission(SecurityAction.RequestMinimum, Read =
"C:\\WINDOWS\\assembly;")]
smime.p7s
Description: S/MIME cryptographic signature
