views:

75

answers:

2

Hi, I have a desktop application that needs to upload/download images to/from service computer over TCP Protocol.

At first, I stored images in file system, but I need to in MS SQL DB to compare which solution is better. Number of images is over half a million. I don't know yet will there be any limitation on size of a photo.

If you have done smth like that, please, write what your opinion upon this question.

Which one is faster, more safe? Which of them works better with this number of photos? If I'll store on DB, do I need to store images apart from all other tables which I use for my application and which type works better - image or varbinary on DB?..and so on.

Thank you.

+1  A: 

Which version of SQL server? Version 2008 adds FILESTREAM which is specifically designed for this purpose. FILESTREAM data can be located on disk which makes it very fast to access.

If this is not an option, you could look into creating a separate filegroup for your image data (to give you the most flexibility when partitioning your data) and use the varbinary(max) or image data types.

A SQL guru will probably chime in with better info.

Josh Einstein
You replied to the wrong answer. But to answer your question, you can create a separate table in the same db but with a different *filegroup* which means the data is physically stored in a separate file. Should have the same benefits as having a separate db but easier to use.
Josh Einstein
@ArsenMkrt I've read about that, but I've a question, If I'll store path in DB and file in file system, how can we know that if there's a row in that table, you'll always find the referenced file? Thank you for your example.
hgulyan
@Josh Einstein sorry, I'm very new here, I got that now :)I think I must try working with filestream. Right now server is 2005, but we're testing on 2008 new apps, so later we'll pass to sql server 2008. hmm..interesting, I didn't know, that you can use separate filegroup in db? how can I do that?
hgulyan
http://msdn.microsoft.com/en-us/library/ms179316.aspx
Josh Einstein
Thank you, Josh :)
hgulyan
+3  A: 

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

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

  • if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

  • in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee foto in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee foto, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it "LARGE_DATA".

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large 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 intro on filegroups, and play around with it!

marc_s
@marc_s Thank you Marc for the article. I think photos will be somewhere between 256Kb and 1Mb. I'm testing on separate table, but I also wanted to know, maybe I need to have a separate db (not only table)?
hgulyan
@hgulyan: no, a separate DB won't help much. Try separate table first, and possibly a separate filegroup (and thus file on disk) next. I don't see any benefit in putting those pictures into a separate database alltogether.
marc_s
@marc_s: Ok, I just thought maybe using separate db would be better for back up and I've read that storing large number of some data can cause whole sql server to work slower. Is that so?Could you, please, provide any info on how to use filegroups in sql server? :)
hgulyan
@hgulyan: yes, intermixing "regular" data and large blobs in the same table can hurt, since when you do a "SELECT *" on your table, you always get back all the data, including the pictures. If you keep it in a separate table, you shouldn't have any of those problems.
marc_s
So this large amount of data in seperate table won't affect on performence of whole db? Thank you for your detailed answer. It really helped me a lot. It's time to try it.
hgulyan
@marc_s: Let me, ask you another question.Why is FILESTREAM optional? Does it need any additional resources? Can it cause any seciruty problems, if I enable remote access?
hgulyan