Storing Images in Sql Server

[duckweb]

We are storing images in sql server 2000 using the image data type. We are using ASP to display these images images in a recordset on our webpages, but even pulling small sized images seem to take a looong time to load.

Does anyone know any performance enhancements in asp or sql server to make them load faster?

 

[mrdenny]

Your best bet would probably be to move the images out of the database and store then if a folder, with a path to the image storred in the database. Then just have the ASP page load the picture referrenced by the path from the database.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[NoCoolHandle]

Denny, I simply can't agre!

Pulling images from the db should be a snap. I have gig's of image data that I pull out of my db and dynamicly scale to fit in the users browser. Each image is between 1 and 2 meg in size. Performance is as good as or better than the disk (if is is used often it is cached.

Duckweb.

What type of recordset are you using, and how many images are you bringing back?

 

[duckweb]

We looked at both options--storing them on folders and the db and chose db b/c we heard sQL Server could handle it. Maybe we were wrong.

Anyway, we are using something like this for recordset:

Set rs = Server.CreateObject("adodb.recordset")
SQL = "SELECT image_blob FROM tb_Images WHERE id = " & Request("id")
set rs = con.execute(SQL)


One other note: We are using ASPJepg component by Persists to resize these images on the fly. I don't know if that may be causing a problem. They seem to come up very fast on our internal dev enviornment, but slow on the production environment.

Any other ideas?

 

[NoCoolHandle]

Ok the recordset looks like it should be a firehose (readonly-forward only) this is good. It also looks like the query should only be returning a single image. Good also.

What I suspect might be your problem is that you might (do) need an index on the ID column if there isn't one. (my guess is that this is your primary key and will have a unique clustered index as part of the constraint- but I have learned to double check everything.) No index means a table scan of all records so will slow things down.

Regarding the aspjepg component. I have no real knowledge of this, but if it is a COM object - which I suspect it is, how and where is it registered. It sounds like there might be a problem with latency caused by network issues. If it was copyed to the local server but registered by mistake on a remote system that could slow things down. And or if your sql system is sitting across a busy network segment that might explain some of the delay.

I am suspicous because it says it runs great in dev env but is slow on the production system..

HTH


Rob

PS have you looked at aspx (dot net) it can do resize of images in the webpage. I could send you (read post here) a copy of a webpage I use to resize images after reading them from the db and return the new image to the webclient.

For a demo of dynamic sizing via aspx you can look at my very crude family photoalbum. The only problme is that as a comcast customer they limit the upload so large images will take a while over the internet. You can "hack" the URL and dynamicly resize or you can do it on the thumbnails page.

To hack the url just change the res=640 to what ever you want the largest side to be. i.e. 640 gives you a 640x480 image good for slow network connections 6400 gives you a 6400 x 4800 image and will take a while because it will be around 15 meg (at a guess). I have even used this to "blow an image up to 120mg but that was really slow on the upload from my cable modem.

Oh yeah

http://www.lynchtek.com/family

should get you there.