storing images in sqlserver using c#

Question

i want to store images of my employees with thier profiles in sql server database. i have following reservations.

whether i should compress images or not if yes please provide me sample code or article how should i retrieve images efficiently, i an afraid of asp.net application performance issue. i think with ten thousand employee records it will halt or slow down

Answer 1

Most image formats (such as JPEG, or PNG) already employ some compression algorithm, so unless your source is a RAW image or an uncompressed bitmap, additional compression won't help much. What might help is limiting the size of the image, say a maximum of 400 x 400 pixels. I don't know what the intended purpose is so I don't know if this will work well for you. Obviously, if you are storing the data directly from a digital camera, the images will be very large and you might want to scale them down first, to something more reasonable.

Answer 2

Sql Server is well equipped to handle large amounts of binary data, but as always, the devil is in the details and the design:

To avoid any performance issues that can happen with reporting or access from other applications due the Sql Server's paging process (all db engine use pages as a discrete unit of data manipulation) I would place the image data in a separate table that is linked 1-to1 to your employee table. Doing so will keep the binary data from being moved around with employee queries unnecessarily when accessed from other parts of your system. Your image display logic will of course have to join on the two tables to match up employee and image records.

Answer 3

I would suggest storing the path of the images on your sql table, and actually store the image in a secure folder. This has worked for me perfectly.

Answer 4

First thing, ten thousand records is nothing ... as long as you have the right indexes in place SQL Server will handle that with ease.

However, consider just using the file system to store things like images. The SQL Server record could just contain a pointer (ie. path) to the file and the asp.net application could just use that to read the file from disk and stream it to the browser.

Answer 5

If you are on SQL server 2008 or above then you can use the filestream feature that is like the best of both worlds. It stores the image in the filesystem but has it under transactional control and is also included in your backups when they are taken.

If you are not on 2008 or above then I would say to keep the images in the DB, see this old Microsoft white paper for my reasons why.

http://arxiv.org/pdf/cs.DB/0701168