Our system needs to store tiff images of about 3k each in size. We received about 300 at a time, and need to process them pretty quickly. Later, once we have received say around 100,000 of these, there are transferred off to another archival system or purged.
Would storing the images in SQL Server or the file system give us better performance (especially for the initial save, the later archiving off is less performance critical)? Are there any other considerations or gotchas to be aware of?
Storing the images in the filesystem will give you better performance. You just need to put an entry into a relevant database table for the tiff image attachments - and use that to get the path of the image on the filesystem.
You might want to further boost performance by hosting the images on a web server - IIS (if relevant) and have your client applications (again if relevant) retrieve them directly frmo there instead.
In my experience SQL Server has been decent with storing blobs into the database. As long as I follow Best Practices related to queries, normalization, etc. I have found them to work well.
For some reason, I personally do not want to store huge PDF and DOC and JPG files in my database, but then, that is exactly what Microsoft SharePoint does, and does well.
I'd definitely consider putting blobs in my db.
The SQL Server 2008 version has a new feature called FILESTREAM. Part of their documentation also has a section on best practices, in which the MS folks state that FILESTREAM should come into play if the BLOB objects are typically larger 1 MB.
That MSDN page states:
When to Use FILESTREAM If the following conditions are true, you should consider using FILESTREAM: - Objects that are being stored are, on average, larger than 1 MB. For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.
So I guess with a 3 KB TIFF, you could store that nicely inside a VARBINARY(MAX) field in your SQL Server 2005 table. Since it's even smaller than the 8k page size for SQL Server, that'll fit nicely!
You might also want to consider putting your BLOBs into their own table and reference your "base" data row from there. That way, if you only need to query the base data (your ints, varchars etc.), your query won't be bogged down by BLOBs being stored intermingled with other stuff.
Marc
The satellite catalog system at INPE/Brazil stores a reference of tiff images stored in filesystem. But the images are a little bigger - +/- 100 MB. If the file must be displayed at browser, the php code reads the tiff content at disk and draw it.