locked
Which is better ? Storing images in BLOB or in the File System ? RRS feed

  • Question

  • User-199781312 posted

    I would like to know which is better?  Storing images in BLOB or in the File System ? I've got around 400 to 5000 images.


    And at run time there are chances where I'll need to retirieve about 100 - 150 images.


    Would like to have suggestions please.


    Monday, January 11, 2010 10:06 AM

Answers

  • User2130758966 posted

    Thanks rtpHarry,

    But I would also like to know, which method is faster, I'm really looking at Performance. I'vev got one app that uses BLOB and it takes eternity to load back the images for display.

    I don't know if you have heard of Pinal Dave before or read his blog but I just did a search and found this post by him. He is a leading expert in sql databases and his post says that filesystem is faster than database:

    In fact, I didn't read his whole article and I just finished it off and it is actually saying filestream - a bit misleading from his initial statement so I did a further search and found this thread which also points in the direction of filestream:

    This is a new feature of sql server 2008 so if you aren't running that then I still stick with storing images as files in the filesystem and storing the path in the database.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 4:22 PM
  • User-952121411 posted

    The only time I reccomend storing files as a BLOB in SQL is when the images or files contain sensitive data (i.e. Medical Images, etc.) where you need the security that SQL Server provides.  Otherwise storing and reading the file from a File Sytem is much faster; you can keep a reference to the file's location in SQL Server for the best method.  Read this as well:

    "performance wise, including an <IMG SRC> tag generated by the database and pointing to a file that already exists is going to be faster than pulling the file out of the database, generating a temp file on the web server, and streaming that to the user. Also, table scans take more resources when there is an image datatype as opposed to a varchar that simply holds a 'pointer' to the file's location." 

     

    Hope this helps! Smile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 9:58 PM

All replies

  • User2130758966 posted

    I have always preferred to store items on the filesystem and store the paths in the database.


    I think the only advantage to putting it in a BLOB is that you can move the database around and not have to worry about distributing files. For normal websites this wouldn't really matter but for some kind of product like a CMS or website addon it would be a handy way to include a resource file with the distribution...


    The reason I prefer the filesystem is because that is what the filesystem is designed for - serving files. I don't see any valid reason to put all that stress into a bottleneck by pushing all the data through the database.

    Monday, January 11, 2010 11:26 AM
  • User-199781312 posted

    Thanks rtpHarry,


    But I would also like to know, which method is faster, I'm really looking at Performance. I'vev got one app that uses BLOB and it takes eternity to load back the images for display.


    Monday, January 11, 2010 12:06 PM
  • User1753744165 posted

    It depends on what you are doing with the data. Are you just taking the images and displaying them on a web page or do you want to store them in binary form for future manipulation? If you are just displaying them, store them in the File System, and store a path in your DB so it can be just linked in (fastest since the amount of data being passed would be just some string data compared to probably 15k of bytes). If you want to Stream the images to something else like a windows application, the database is a good solution because the images are already in binary format, and there would be no need to download the image.

     

    Nick

    Monday, January 11, 2010 12:30 PM
  • User2130758966 posted

    Thanks rtpHarry,

    But I would also like to know, which method is faster, I'm really looking at Performance. I'vev got one app that uses BLOB and it takes eternity to load back the images for display.

    I don't know if you have heard of Pinal Dave before or read his blog but I just did a search and found this post by him. He is a leading expert in sql databases and his post says that filesystem is faster than database:



    Monday, January 11, 2010 4:18 PM
  • User2130758966 posted

    Thanks rtpHarry,

    But I would also like to know, which method is faster, I'm really looking at Performance. I'vev got one app that uses BLOB and it takes eternity to load back the images for display.

    I don't know if you have heard of Pinal Dave before or read his blog but I just did a search and found this post by him. He is a leading expert in sql databases and his post says that filesystem is faster than database:

    In fact, I didn't read his whole article and I just finished it off and it is actually saying filestream - a bit misleading from his initial statement so I did a further search and found this thread which also points in the direction of filestream:

    This is a new feature of sql server 2008 so if you aren't running that then I still stick with storing images as files in the filesystem and storing the path in the database.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 4:22 PM
  • User-952121411 posted

    The only time I reccomend storing files as a BLOB in SQL is when the images or files contain sensitive data (i.e. Medical Images, etc.) where you need the security that SQL Server provides.  Otherwise storing and reading the file from a File Sytem is much faster; you can keep a reference to the file's location in SQL Server for the best method.  Read this as well:

    "performance wise, including an <IMG SRC> tag generated by the database and pointing to a file that already exists is going to be faster than pulling the file out of the database, generating a temp file on the web server, and streaming that to the user. Also, table scans take more resources when there is an image datatype as opposed to a varchar that simply holds a 'pointer' to the file's location." 

     

    Hope this helps! Smile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 9:58 PM