Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are storing a list of images in a table in an image column. These are stored as their raw image size so they can be viewed in their details at full resolution.

The problem I have is I want to show them in a listing in our application and I just want to show them as thumbnails. When I retrieve them via a view our entityFramework code bombs with out of memory errors trying to resolve all the images into fields in the code.

I'm wanting to know whether I can resize the image column at the database view level before it hits the application. i.e. In our view have a function that takes the full size image and resizes it to 80x80 and returns it. e.g.

Select fxShringImage(EntityImage) From EntityImage

Any ideas? Is this possible?

share|improve this question
    
I don't know if this is possible but certainly sounds like a problem to be solved at the application level (or, even better, by slightly changing the current design). –  dezso Jul 16 '13 at 8:40

2 Answers 2

up vote 4 down vote accepted

First and foremost do not use the IMAGE data type, is an old deprecated type:

text , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

You need to store multiple images, one for each resolution desired (full/preview/thumb). In general these resized images must be generated during the upload of the image. Strictly speaking it is possible to generate the preview/thumb on-demand on first display, or even on each access (ie. not saved), but this is seldom the right choice. In general a file is uploaded once and viewed multiple times, so it makes more sense to pay the penalty of generating the thumbnail at upload (write) time. And users are perceive the site/application as 'slow' if the image is generated on-demand on first access. See Asynchronous Execution for how to run jobs like uploaded image re-size in an reliable asynchronous manner.

As for loading fat content into entity lists in the client (irrelevant whether is images, documents or whatever other BLOB), just don't do it. You will kill your server, your network, your appserver, your client app, everything. Load narrow lists with only the fields needed for display. Lazy load on-demand expensive content. This is CS101 and applies to every technology.

PS. I won't even go into the topic of whether is better to store images on disk or in db. I assume you already did due diligence for that.

share|improve this answer
    
Hi Remus, Thanks for your response. I know the big no no's with the image and db problems. But we are dealing with a maintaining an old inhouse system. So changing this would require a fundamental shift which is not worth the investment. Thanks for your advice though. –  Raven Jul 16 '13 at 21:49

The only way to do this would be to put a SQLCLR function within the database which would then resize it. This wouldn't be recommended as this would be very expensive as any rows returned by the query would need to have their image resized over and over, even if the row wasn't used by the client application. A better idea would be to store a separate thumbnail sized image somewhere.

On a separate note storing huge images in the database isn't the best of ideas and should be avoided when possible as if fills the buffer cache. If needed turning on FILESTREAM can fix a lot of these problems as the images would actually be saved on the file system instead, but would still be accessible through the normal T-SQL query. Using this method you would still need to manually store the smaller version of the image in a separate location from the large full size image.

share|improve this answer
    
Would using FILESTREAM take care of the issue with the buffer cache? –  Mikael Eriksson Jul 16 '13 at 8:06
1  
@MikaelEriksson - Clients can stream the filestream data directly.. –  Martin Smith Jul 16 '13 at 8:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.