Performance in tables with binary data/images

Question

I made a people maintenance screen. The client want me to store the photo of every person in the database, and I made it without problems. I have a separate table for the images with two fields, Id_person,and Image.

I'm a little worried because it's the first time that i work with images in database. will I have problems of performance when the table grows beyond 1000/5000 images? I suppose that the size of every image will make a difference. I'm sure that I will need to control that the user don't save very big images in the Database.

What would be a good size limit? The client only need pics of the face, but I'm sure that someone will try to make the pics with a "last model" camera in full quality ;)

Thanks.

Answer 1

It's usually preferred to keep a folder of images and the DB just references that folder. Ideally, each person has a unique ID and the files in the "images" folder match that ID.

If you really want to store the binary data directly, you can get a reasonable quality photo in 8KB of a JPEG (approx 250x250 pix @ 25% quality). Of course, this would be unacceptable for printing, but is fine for identification.

Only you will know if you can accept an additional 8KB per row in your database server.

Answer 2

If you absoultely MUST do it this way, I would say limit it to just a few kilobytes each. However, every database admin in the world will probably tell you that blobing images into a database field is a very, very bad idea. Most noticably you will see the performance decrease drastically when the database file grows beyond two gigabytes in size.

I would prefer to do as jheddings said and have a folder with each person's ID be the file name and just use a standard .jpg or something after that on a network share so all computers using the app can access the images.

Some find that simply using the ID isn't good enough incase the photo needs to be deleted or archived, in which case they will put a NVARCHAR(MAX) field into their database and store the network file path to the image instead of the actual image.

I would only blob the image if your customer absolutely cannot have a network share path.

Answer 3

as long as it is in separate table with ID|BLOB only there shouldn't any performance issues fetching that photo, but on the other side i prefer keeping in DB only references to files on hdd (or even better if its only user photo you dont realy need a reference because user with ID 1 goes to /images/1.jpg)