469,364 Members | 2,346 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,364 developers. It's quick & easy.

flat file vs sql database

I'm hoping I might be able to pick someone's brain out there.

Our company is currently running an inhouse application in which data
is being stored in SQL. Included in this data are thousands (over
100,000) of .jpg images.

I am starting to run out of disk space and a developer suggested that
we take the images out of SQL and put them in a file. In doing they
indicated we could recover the SQL database faster if needed, we could
also manage the backup of the SQL server easier.

Does this make sense?

Nov 23 '05 #1
8 2414
The short answer is "yes" it does make sense. Beyond that, I'm not sure what
your specific question is...


"Inspector" <Jn*****@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
I'm hoping I might be able to pick someone's brain out there.

Our company is currently running an inhouse application in which data
is being stored in SQL. Included in this data are thousands (over
100,000) of .jpg images.

I am starting to run out of disk space and a developer suggested that
we take the images out of SQL and put them in a file. In doing they
indicated we could recover the SQL database faster if needed, we could
also manage the backup of the SQL server easier.

Does this make sense?

Nov 23 '05 #2
thanks for your reply. If I can ask another question, what are the
benefits of going with a the .jpg images in a file server rather than
sql.

Nov 23 '05 #3
Currently we are running a db of 300 gigs. In 2006 this could double.
Would this have any impact on the disk solution? Thanks for your help.

Nov 23 '05 #4
> thanks for your reply. If I can ask another question, what are the
benefits of going with a the .jpg images in a file server rather than
sql.


If they are in the database, then aspnet needs to retrieve that image
from the db-server and send it to the browser. If the images are on the
filesystem, then IIS can serve them, without bothering either aspnet or
the db. Also the client can have them cached. So it saves a lot of
processing.

You loose on security: anyone can retrieve those images, if they know
the filename (but that might not be a problem for you).

Hans Kesting
Nov 23 '05 #5
The problem I am facing is that the most important information (I'll
call it content) in the db accounts for 5% of the space. So in
considering back-up and recovery it makes sense to separate the content
from the images.

I guess my question would be, in the long run is the money better spent
moving the images into a new db or moving it to a file? That is,
assuming the programming for both is the same.

FYI - the reason this may sound so scrambled is that I am the COO not
the CIO. We have lost our development team and the area we are in does
not have a lot to choose from. I am concerned I may be dealing with a
new development team that can talk the talk, but when it comes to
walking the walk their knowledge isn't there.

Thank you for you time.

Nov 23 '05 #6
Okay, I'll give some general guidelines beyond the valid points made
elsewhere in this thread by Hans Kesting.

Regarding your question "is the money better spent... images in db vs in
files"...
I'll have to preface my response by saying that we are very much engaged in
*art* when it comes to answering the question, "how do we implement featrue
xyz?" So nobody here can tell you definitively what you should do. What we
can do is try to tell you what is better given certain facts, and only those
facts. If we get additional facts about your scenario then we might offer
completely different advice and rationalle.

That being said, it's generally easier to work with images when they are
stored in files and not in a database. It is a standard practice to store
information *about* the images in the database, but the actual images go
each into their own file on the server. The images/files are generally
organized into their own sensible folder structure. The information "about
the images" stored in the datbase could be extensive - and one piece of such
information is the name of the particular file (complete with path)
containing the image. This is also a cleaner implementation because the
database can do it's specialized job of sorting and searching and organizing
information (about your images); while the disk system does its specialized
job of serving up files (the actual images). The database will also perform
much faster if it's not dealing with images.

Regarding expense (money better spent)...
I suspect you are thinking strictly about hardware costs (i.e., new databse
server to handle expected 2006 volumes)... new database server or not (or
perhaps increased capacity of existing server). If you do not in fact need
to store the images in the database and you have plenty of available disk
space, then you might better spend the money elsewhere because the new
database server would be unnecessary (point favors going to files). Of
course if you decide to move all the images out of the database, then there
is real money to be spent in modifying your existing application(s) that
currently retrieve from the database. That could be far more than the
expense of a new database server (point favors keeping images in database).
Of course if you put "too many" images in the database in 2006 and it gets
"too big" then it would perform unacceptably; the whole system might also
become less reliable (i.e., it breaks more frequently) - in which case you
might eventually be "forced" to move all of the images out of the database
(point favors moving images out of database). Then there is the issue of
ongoing maintenance. It's generally easier and quicker to back up a database
without all those images in it (point favors moving images out of database).

It would generally be easier, IMO as an applications programmer and database
administrator, to deal with images stored in files and not in the database.
Of course if you choose that route, then you immediately incur some expense
in modifying your application(s) to retrieve images from disk (and not from
db). That short-term expense may be far more than the short-term cost of a
new database server. So, you could chose to go with the new database server
in order to save a buck now... but when the volumes make the system
unweildly in 2006 and beyond, you may be forced to move the images out
anyway... in which case you would have the most expensive long-term scenario
on your hands (paying for both a new database server AND development costs
required to rework your applications).

I hope this is helpful.

Jeff


"Inspector" <Jn*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
The problem I am facing is that the most important information (I'll
call it content) in the db accounts for 5% of the space. So in
considering back-up and recovery it makes sense to separate the content
from the images.

I guess my question would be, in the long run is the money better spent
moving the images into a new db or moving it to a file? That is,
assuming the programming for both is the same.

FYI - the reason this may sound so scrambled is that I am the COO not
the CIO. We have lost our development team and the area we are in does
not have a lot to choose from. I am concerned I may be dealing with a
new development team that can talk the talk, but when it comes to
walking the walk their knowledge isn't there.

Thank you for you time.

Nov 23 '05 #7
Yes. Thank you very much.

Nov 23 '05 #8
Thanks Stan. You all have provided some great information that I can
take back to my developer and IT management team. I appreciate it.

Nov 25 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by raykyoto | last post: by
1 post views Thread by Tim Fierro | last post: by
9 posts views Thread by FFMG | last post: by
9 posts views Thread by =?Utf-8?B?TmF2YW5lZXRoLksuTg==?= | last post: by
15 posts views Thread by lxyone | last post: by
1 post views Thread by lxyone | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.