flat file vs sql database

Discussion in 'ASP .Net' started by Inspector, Nov 23, 2005.

  1. Inspector

    Inspector Guest

    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?
    Inspector, Nov 23, 2005
    #1
    1. Advertising

  2. Inspector

    Jeff Guest

    The short answer is "yes" it does make sense. Beyond that, I'm not sure what
    your specific question is...




    "Inspector" <> wrote in message
    news:...
    > 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?
    >
    Jeff, Nov 23, 2005
    #2
    1. Advertising

  3. Inspector

    Spam Catcher Guest

    "Inspector" <> 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 ()
    Spam Catcher, Nov 23, 2005
    #3
  4. Inspector

    Inspector Guest

    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.
    Inspector, Nov 23, 2005
    #4
  5. Inspector

    Inspector Guest

    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.
    Inspector, Nov 23, 2005
    #5
  6. Inspector

    Hans Kesting Guest

    Re: =?UTF-8?B?ZmxhdCBmaWxlIHZzIHNxbCBkYXRhYmFzZQ==?=

    > 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
    Hans Kesting, Nov 23, 2005
    #6
  7. Inspector

    Inspector Guest

    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.
    Inspector, Nov 23, 2005
    #7
  8. Inspector

    Jeff Guest

    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" <> wrote in message
    news:...
    > 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.
    >
    Jeff, Nov 23, 2005
    #8
  9. Inspector

    Inspector Guest

    Yes. Thank you very much.
    Inspector, Nov 23, 2005
    #9
  10. Inspector

    Spam Catcher Guest

    Re: =?UTF-8?B?ZmxhdCBmaWxlIHZzIHNxbCBkYXRhYmFzZQ==?=

    "Hans Kesting" <> wrote in
    news::

    > 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 ()
    Spam Catcher, Nov 24, 2005
    #10
  11. Inspector

    Inspector Guest

    Thanks Stan. You all have provided some great information that I can
    take back to my developer and IT management team. I appreciate it.
    Inspector, Nov 25, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mohammad S Khan
    Replies:
    3
    Views:
    8,663
    Chris Uppal
    Aug 31, 2004
  2. mir nazim
    Replies:
    7
    Views:
    534
    Brian Kelley
    Nov 24, 2003
  3. John Benson
    Replies:
    0
    Views:
    296
    John Benson
    Nov 22, 2003
  4. Tim Churches
    Replies:
    2
    Views:
    406
    mir nazim
    Dec 8, 2003
  5. Art Decco

    Flat file database

    Art Decco, Dec 23, 2003, in forum: Python
    Replies:
    10
    Views:
    795
    mir nazim
    Dec 24, 2003
Loading...

Share This Page