Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > flat file vs sql database

Reply
Thread Tools

flat file vs sql database

 
 
Inspector
Guest
Posts: n/a
 
      11-23-2005
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?

 
Reply With Quote
 
 
 
 
Jeff
Guest
Posts: n/a
 
      11-23-2005
The short answer is "yes" it does make sense. Beyond that, I'm not sure what
your specific question is...




"Inspector" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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?
>



 
Reply With Quote
 
 
 
 
Spam Catcher
Guest
Posts: n/a
 
      11-23-2005
"Inspector" <(E-Mail Removed)> wrote in news:1132708453.210807.228180
@g49g2000cwa.googlegroups.com:

> 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.


Yes, unless you have a lot of resources, it's more efficent to store large
binary objects on disk rather than in a database.

However, there are some advantages to storing in DB (i.e. easier retrival,
file management, security, etc)... so only you can decide if it's worth
moving the images to the file system.

--
Stan Kee ((E-Mail Removed))
 
Reply With Quote
 
Inspector
Guest
Posts: n/a
 
      11-23-2005
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.

 
Reply With Quote
 
Inspector
Guest
Posts: n/a
 
      11-23-2005
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.

 
Reply With Quote
 
Hans Kesting
Guest
Posts: n/a
 
      11-23-2005
> 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


 
Reply With Quote
 
Inspector
Guest
Posts: n/a
 
      11-23-2005
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.

 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      11-23-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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.
>



 
Reply With Quote
 
Inspector
Guest
Posts: n/a
 
      11-23-2005
Yes. Thank you very much.

 
Reply With Quote
 
Spam Catcher
Guest
Posts: n/a
 
      11-24-2005
"Hans Kesting" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> 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).



In regards to security - if you store on a file system you'll have to build
an extra layer of security.

Perhaps you can use ASP.NET to read the files to a buffer before sending
them to the client - this will prevent direct access of your graphics
files, and thus you can store the graphics file in a secure, non-IIS
accessible directory.

--
Stan Kee ((E-Mail Removed))
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
java with ISAM , flat-file database with multiple keys Mohammad S Khan Java 3 08-31-2004 12:59 PM
small, fast and cross-platform flat-file database for python mir nazim Python 7 11-24-2003 05:23 PM
small, fast and cross-platform flat-file database for python John Benson Python 0 11-22-2003 10:06 PM



Advertisments