to store or not to store an image

[Guest]

Hello all!

I am amazed how many posts I have read to store an image in SQL, and just as
many against it. So I learned how to store an image in a SQL db and retrieve
the image. A little tricky, but not too bad. But then I thought I wanted to
try the other way, by putting the file location in SQL and storing the actual
image in another directory.
I plan to have many images on my web application, up to as many as 5000.
Not to mention the other tables I will have, that will have just as many
records.
So I think I will go the old fasion route, and just put in the path in the
DB instead of the image.

Do I dare ask? What do you think is the best way to go?

Second thing is, I have found a ton of articles on how to store the image,
but can't find any that shows an example of just storing the file path. If
somebody would have an example of this, or point me in a direction, that
would be great!

Thanks for all your help!!!

Rudy

 

[W.G. Ryan eMVP]

Rudy - the problem with storing the images is that you don't know for sure
how big the thing could grow in most instance. Today 5,000 - tomorrow maybe
it turns into 10,000. It's a lot easier to buy network storage than to
accomodate the cahnge in the databse. + you can't use indexes on blob
fields. If you have 5,000 images, then I would avoid using Blobs - having
done it in the past. Let me put it this way - the benefit of being 'right'
about storing in the db is virtually indistinguishable from the benefit of a
network drive - at least as far as the end user goes. The cost of being
wrong about the db is very very high. So there's basically a lot of risk
for a very little upside. IMHO, the math doesn't add up. The exception is
cases where you absolutely don't want the images exposed to end users except
through your application and you store them in the db and then have an a33
load of security on the db. I did this at my former job w/ about 10,000
pdfs, peformance totally sucked but the images were confidential and were
accessed rarely - one thing I forgot to mention above is the frequency of
access. The less they are accessed, teh less of a risk with storing in the
db.

I'd definitely ask my DBA for his/her opinion on the subject too - mine
would probably say no way without a really compelling argument.

As far as storing paths goes, just store the path as a varchar and then use
a ExectueScalar command or fill a datatable and use either the File or
FileInfo class passing in the value to get the file reference.

 

[Steve C. Orr [MVP, MCSD]]

In addition to the variables W.G. Ryan mentioned, the size of the images is
also a factor in determining which technique is more performant.
In most cases I prefer saving to the DB, and the reasons are outlined in
this article:
http://SteveOrr.net/articles/EasyUploads.aspx

 

[Kevin Spencer]

Do I dare ask? What do you think is the best way to go?

In terms of performance, storing images in a database is a huge waste of
resources. Why? Because the file system is a database. And a database app
such as SQL Server uses the file system to store its data. So, let's take a
look at the flow when using a database and when not using a database:

File System Only:

1. File is fetched from file system.

Database

1. Network Connection is made to database
2. Query is sent to database
3. Database parses query.
4. Database file is fetched from file system.
5. Image is retrieved from database file
6. Network Connection is closed


--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Guest]

In terms of performance, storing images in a database is a huge waste of
resources. Why? Because the file system is a database. And a database app
such as SQL Server uses the file system to store its data. So, let's take a
look at the flow when using a database and when not using a database:

File System Only:

1. File is fetched from file system.

Database

1. Network Connection is made to database
2. Query is sent to database
3. Database parses query.
4. Database file is fetched from file system.
5. Image is retrieved from database file
6. Network Connection is closed


--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Guest]

Hello all!

Thank you for the great response. WG, I'm with you. Coming from the
hardware side, my thought is if I get too deep, and found the DB solution
didn't work for me, it would be tough to get out. Access is also a huge
thing. Theese images will be accessed a great deal, so I know there is going
to be alot of traffic.

Steve, I read your atricle. As a matter of fact, that was the article I used
to learn how to store and retrieve images on the DB. Nice job, laid out very
nicely. The reason I was trying the image in the DB way, beacuse I was going
to have my website put up on the webfarm for a few months, until I have
enough revenue to set buy the servers and hardware to run out of my office.
I didn't want some guy at this place snooping aroundand looking at the
images. I could have the images stiored at my location, and still have SQL at
the web farm, but it seems clunky that way.

But now I think go into debt big, buy the hardware and run it out of my
location. So I think in my case, storing the file path is the way to go. the
oterway seems fine for security and low access, but for high access it just
seems easier to do it the other way.

Thanks again for all your time.

Rudy

 

[Guest]

Hi W.G.

Could you please give me an example how you would use the file info class. I
would save the pics to something like c:mypics. How would I get the pic name
and so forth. Or if you know of a place that shows this. That would be
great!!!

Thanks for your help!!!

Rudy