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