What's the best option?

[Guest]

Hi,
I need to store lots of product images. I was thinking of storing the paths
in the database but overtime it may become cumbersome as I'll be dealing with
lots of images.

I would need to display both a thumbnail and large versions. The average
size per image is 400kb and the max display is 1702 by 2542.

Would SQL Server be a good alternative?

Thanks

 

[sloan]

Storing images ~inside the db will affect performance.

Most people do it for security. (your bank has an image of your check...
for example)

...

A good compromise is:

ImagePath
IPID int , Path string


101,"C:\folder1\"
102."C\folder2\"

Image
ImageID int
IPID int (fk to the above table)
RelativePath string


2001, 101 , "picture\girl.jpg"
2002,101, "pictures\boy.jpg"
3001, 102, "family\mom.jpg"
3002, 102, "family\dad.jpg"

The ImagePath gives you the ability move files, and not have to update a
million rows.

Select ip.Path + i.RelativePath as FullPathName from ImagePath ip join
Image i on ip.IPID = i.IPID

so you'd get back
C:\folder1\picture\girl.jpg
C:\folder1\pictures\boy.jpg
C:\folder2\family\mom.jpg
C:\folder2\family\dad.jpg

That's how I implement it. Esp in a big project where you have a zillion
images, the ImagePath table allows easier updating.


If you don't need security, I'd keep the files as (real) files ... and have
paths to them.

But that's me.

Its cheap (compared to pulling it out of the database) to send a url of a
file that already exists out to your client-browsers.

 

[Guest]

See, I am dealing with tons of images. Do I then need to store these images
on our webserver?

I didn't mention this in my original question, I was also considering after
storing them in the database, I was considering creating a webservice to
handout the images as they will be used by quite a few applications
internally as well.

Thanks

 

[Steve C. Orr [MCSD, MVP, CSM, ASP Insider]]

Yes in many cases storing images in SQL Server is a good choice.
I wrote an article on the subject here:
http://SteveOrr.net/articles/EasyUploads.aspx

Since some of your images sound quite large, you may want to do some
performance testing to ensure it will meet your requirements.

 

[Kevin Spencer]

Since the images are associated with products, yes, storing their paths in
the same database as the product data would be a good idea. As for the
thumbnails, you can create an HttpHandler that will open an image, create a
thumbnail, and return it to the browser. Or, you can store the thumbnails in
the same folder as the full-size images, and use a naming convention to get
them. For example:

ProductA.jpg
ProductA_thumb.jpg

This way you only have to store "ProductA.jpg" in the database, and use the
naming convention to alter the image file name for the thumbnail.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[sloan]

If you want to "serve them up" to a client browser, then at some point the
file has to be on the webserver.

So it depends.

If you ~can just store them on the webserver, then that's the easier option.

Or , you can "copy them on demand".

See my modified example below to start out.



So that gives you the ORIGINAL location of the files.

If someone requests the item (product in your case) that is represented by
the dad.jpg image... you can copy it on demand.

(File Copy) from
\\fileserver2\folderB\family\dad.jpg
To
( web server ) C:\inetpub\wwwroot\urlimages\

you'll have to figure out a way to have unique file names ... one way

change the filename (replace \ with _ )
fileserver2_folderB_family_dad.jpg
and you end up wiht the file:

C:\inetpub\wwwroot\urlimages\fileserver2_folderB_family_dad.jpg

That will end up being (as a web link)
http//www.mysite.com/urlimages/fileserver2_folderB_family_dad.jpg

The reason you want a consistent file name is so you don't unnecessarily
copy images.

the logic is

rework the fileserver filename to the localfile name
see if the local filename exists
(C:\inetpub\wwwroot\urlimages\fileserver2_folderB_family_dad.jpg)
if it doesn't , copy it
if it does, just keep moving
rework the url so you can give the client a url that actually works (
http//www.mysite.com/urlimages/fileserver2_folderB_family_dad.jpg )

And then you have to write a cleanup service that runs at night or
something.


It depends what your goals are.

The solution above is good for a webfarm, because you don't have to keep X
number of copies of images (per web server). And you don't fill up the
harddrive with images.

But it takes a little work.

And based on what you're saying, I'd go with the solution above.
I've done this exact same thing. My company has millions of images we have
to serve up internally (intranet), and we have huge fileservers to hold just
the images.
The "copy on demand" solution works well.

And I STRONGLY recommend the


inclusion. Because eventually you'll have to move images from one file
server to another. This way you update 1 or 2 records, not thousands (or
millions) of records doing string manipulation.


Good luck.


Read the other posts about the thumbnails also. I won't repeat that
information unnecessarily.