SQL Server Performance

storing the images

Discussion in 'T-SQL Performance Tuning for Developers' started by hirnani77, Jan 1, 2003.

  1. hirnani77 New Member

    Any suggestion about how to store the images into database using image data typa
  2. satya Moderator

    Refer to this link http://www.sqlteam.com/item.asp?ItemID=986] which explaines the scenario.
    But you need to asses the database size if need more images in the database, from this aspect its suggested to store image files (.jpg or .gif) on a share directory and link from SQL server to store the URL or path for that image file.

    HTH


    Satya SKJ
  3. bradmcgehee New Member

    Although storing images in SQL Server is possible, it is often not practical. For more information, besides what satya has provided, check the Books Online. But as satya has said, most people store images on disk, and only refer to the image (path or url) in the database, because it is much faster and easier to work with.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. royv New Member

    The biggest advantage for you storing the path instead of the picture itself in the database, is that it will be much easier to obtain the image. I guess I could understand storing the image in the database IF IT WAS EASY to get out, but that is NOT the case.


    "How do you expect to beat me when I am forever?"
  5. satya Moderator

    And the other concern (for DBA) is database space, size does matter <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Satya SKJ<br />
  6. hirnani77 New Member

    I have to create a system using sql and retrieve image file from database. Hopefully someone can give some good suggestion. I know it is not practical but have to do it because of my thesis.
  7. Chappy New Member

    What development language are you using ?
    Most languages will have sample code on the net somewhere for storing images.
  8. satya Moderator

  9. shanaaz New Member

    Yes!! Storing images directly in the SQL Server is not practical. Have faced enough problems with storing images in DB. Its better to store the image as files in the server and store the path in the DB.
    Maintaining these images is one of the major issues.

    Shanaaz
  10. satya Moderator

    Anyway its for your thesis which is one time dealing you can go ahead with specified links.

    Satya SKJ
  11. tkelley New Member

    Yep, have to agree with everyone else. I inherited a database where images are stored in the database and it is major problems. We use .asp pages to update the database and it causes headaches frequently, not to mention the massive size of the database. geez..



    ----------
    T Kelley
    MS, MCDBA, OCA, CIW

  12. pdensmore New Member

    I am curious as to why the image datatype in SQLServer is considered to be a potential performance issue. I am currently an Oracle DBA and we are considering moving an application to SQLServer. In the existing app we store over 200,000 images (avg size 24k) in our database. Approximately 200 new images are inserted each day and then they are used mainly for read-only transactions. We prefer this method because it greatly simplifies our backup and recovery procedures and the DB security model encompases the images as well. If we implemented the method of simply storing the url or path in the DB and the associated file in the file system, then we would have to develop procedures to insure the file system is in sync with the DB in the event of a recovery and also have to develop a similar security scenario in our file system. In addition would it not cause performance problems storing several thousand files in one directory, or is recomended to use numerous directories? This is an important issue in our decision and any input or feedback would be appreciated. Thank You.
  13. satya Moderator

    In SQL Server IMAGE datatype variable-length binary data from 0 through 231-1 (2,147,483,647) bytes which is multiplied accordingly when any new record inserted.

    Most of the information is listed above and its always suggested to manage images from file path rather storing directly in the database. It causes more issues when the DBCC and any maintenance checks running against that table particularly.

    HTH

    Satya SKJ
  14. bradmcgehee New Member

    pdensmore, the point you make storing the image in the database because of the convenience is a valid point. It does make somethings easier, and it your case, it might be the correct decision. But if you do this, you will have to live with the problems associated with it. I know its a pain, but I suggest you perform some tests with storing data in SQL Server and see if you can live with this option.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  15. camja New Member

    Does anyone have codesamples on storing path to an image in sql, and displaying the images on a web site.
  16. pavankan New Member

  17. gaurav_bindlish New Member

    pdensmore, one of the reasons why it is discouraged to store image files in SQL Server is because the data pages containing image data are stored saperatly than data pages containing rest of the data for the table unless specified otherwise. Hence there is a performance hit. I am not sure if this is the case with Oracle or not but any insight would be appreciated.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  18. FrankKalis Moderator

    Ok, just for the records [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Here is the most comprehensive link on this topic, IMHO <br /<a target="_blank" href=http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp>http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp</a><br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>

Share This Page