Is it a good idea to use filestream to store images and thumbnails?

Question

We are developing a .NET application that stores and searches for a large number of images (> 100,000).

When we upload the image, we need to create a thumbnail and save the original image and thumbnail in the database (SQL Server 2008 R2).

I read that the best way to store images is to use FILESTREAM

http://msdn.microsoft.com/en-us/library/cc949109.aspx

but when the images are smaller, this is unlikely.

Is it a good idea to store images and thumbnails using filestream? Is there a better alternative?

Answer 1

There's a really good article from Microsoft Research called In Blob or Not To Blob that discusses this issue in detail.

Their conclusion after a large number of performance tests and analysis is as follows:

• if your images or documents are usually below 256 KB, storing them in the VARBINARY column of the database is more efficient

• if your images or document are usually larger than 1 MB, they are more efficiently stored in the file system (and with the SQL Server 2008 FILESTREAM attribute, they are still under transaction control and part of the database)

• between these two, it depends a little on your use

If you decide to place your photos in a SQL Server table, I highly recommend using a separate table to store these images - do not store the employee photo in the employee table - keep them in a separate table. Thus, the Employee table can remain sparse, medium and very efficient, assuming that you do not always need to select an employee photo as part of your queries.

For filegroups, check out Files and Filegroup Architecture to log in. Basically, you will either create your database with a separate filegroup for large data structures from the very beginning, or add an additional filegroup later. Let me call it "LARGE_DATA".

Now that you have a new table to create that should store the VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this group of files for big data:

  CREATE TABLE dbo.YourTable (....... define the fields here ......) ON Data -- the basic "Data" filegroup for the regular data TEXTIMAGE_ON LARGE_DATA -- the filegroup for large chunks of data 

Check out the MSDN entry in filegroups and play with it!

Answer 2

Actually, the correct answer is that it depends. If the images are relatively small, you can save them in the varbinary (max) field.

Why not FILESTREAM? - FILESTREAM performance achieves best results with large BLOB values ​​- 1Mb +