73537

Store tiff images in SQL Server or file system?

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?

Answer1:

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.

Answer2:

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.

Answer3:

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

Answer4:

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.

Recommend

  • OpenCV - iterate over each blob in a binary image and use it as mask
  • What would be the best way to send NSData through PHP into MySQL?
  • Save documents as BLOB in SQL or on file system
  • what is call to function $openid->validate do?
  • image view zoom with finger spread in android like gallery images
  • where is memory allocated for pointers and their data?
  • Checking metadata of Amazon S3 file using iOS AWS SDK in Swift
  • What is the recommended way for sending personalized images in html emails?
  • Extract text from “” HTML tag
  • Get the computer user name in a web application
  • Serverless Framework Dynamo DB Table Resource Definition with Sort Key
  • Add Windows Feature from C#
  • back button function for phonegap windows phone 7
  • Can long-polling be achieved in Restlet by just making the thread sleep?
  • Retaining data after updating application
  • Sensibility of combined Maven/Ant+Ivy build management for dual platform Desktop/Android deployment?
  • ASP.NET MVC 2 Preview 2 - display directory list rather than home/index
  • Jquery popup on mouse over of calendar control
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • how to upload multiple files in c# windows application
  • Spring Cloud Microservice Architecture Confusion
  • MS Access - How to change the linked table path by amend the table
  • JBoss External Properties Files in Classpath
  • Unable to get column index with table.getColumn method using custom table Model
  • Connect .sks to skscene.h
  • How to suppress a dialog
  • Transactional Create with Validation in ServiceStack Redis Client
  • Why does access(2) check for real and not effective UID?
  • Checking free space on FTP server
  • Change Inet root folder for iis 7
  • Handling un-mapped Rest path
  • Can I display google adwords (AdView) in javafx on android
  • PHP - How to update data to MySQL when click a radio button
  • script to move all files from one location to another location
  • ILMerge & Keep Assembly Name
  • Can I make an Android app that runs a web view in Chrome 39?
  • Symfony2: How to get request parameter
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • How do I rollback to a specific git commit
  • Why do underscore prefixed variables exist?