getting images out of mssql in C# using streams

Question

Hi there. I have a database which stores .png images as the sql "image" type. I have some code which retrieves these images as a byte[], and sends them to the page via the FileContentResult object in .Net. Performance is key in this application, and the images have to be retrieved and displayed as quickly as possible. My question is, can this operation be performed quicker by passing a byte stream from the database to the browser, and not at anytime storing the whole byte array in memory. If this is possible and worthwhile doing, how do I do it?

Here is the code I have so far:

// Get: /Image/Get/5
        public FileResult Get(int id)
        {


            Response.Cache.SetExpires(DateTime.Now.AddSeconds(300));
            Response.Cache.SetCacheability(HttpCacheability.Public);
            Response.Cache.SetValidUntilExpires(true);

            // Get full size image by PageId.
            return base.File(page.getFullsizeImage(id), "image/png");
        }

And

public byte[] getFullsizeImage(int pageId)
        {


                    return (from t in tPage
                            // Filter on pageId.
                            where t.PageId == pageId
                            select t.Image).Single().ToArray();


        }

Thanks for any help!

Answer 1

Edit: Based on your comments, I think you should consider using DeepZoom from microsoft. Essentially, what this allows you to do is generate a specialized image file on the server. When a user is browsing the image in full view, just the couple of million or so pixels that are displayed on the screen are sent to the browser via AJAX. Then when the user zooms in, the appropriate pixels for the zoom level and x and y axis are streamed out. There is a DeepZoom Composer which can be accessed via the command line to generate these image files on demand and write them to a network share. Your users will be really impressed.

Take a look at this example. This is a massive image - Gigabytes. in about the middle of the image you will see some newspaper pages. You can zoom right in and read the articles.

End of Edit

Do you have to have images with a large file size? If they are only meant for displaying in the browser, they should be optimized for the web. All main image editing applications have this ability.

If you do need the large file size, then you could provide optimized images and then when the user clicks on the image, allow them to download the full file. They should expect this download to take some time.

In Photoshop, the task is "Save for web". There is a similarly named plugin for Gimp.

I know that this doesn't answer your direct question ("can this operation be performed quicker by passing a byte stream"), but it might help solve your problem.

Answer 2

Changing the linq from single to first should give you nicer SQL, if PageId is the primary key you can safely assume first and single will return the same result.

Answer 3

A nice question.

Reality is the code required to send the image as a stream is really minimal. It is just Response.Write~~~ byte array and setting the HTTP's content-type header which must be very fast.

Now you seem to need to open up your database to the world to get it done quicker. That, being probably possible using features that allow SQL server to serve HTTP/interact with IIS (long time ago I looked at it), not a good idea so I do not believe you should take that risk.

You are already using the caching so that is cool but files being large, cache gets purged frequently.

But one thing to do is to have a local File Cache on the IIS and if image is used, it is written to the file on teh web server and from then on (until maybe next day when this is cleared) this other URL (to the static asset) is returned so requests would not have to go through the ASP.NET layer. It is not a great idea but will achieve what you need with least risk.