none
Save pictures as blobs in the database, good idea?

    คำถาม

  • Hi,

    My application will have to save images in the server, and the clientes will retrieve them on demand. I'll give to each picture a code, so that code will be pointing to entry in a special table where I keep the information about the pictures, my doubt come when I think about where should I store the picture itself.... BLOB or files system?

    I don't know what would be better o how can affect that to the database or to the performance. Keep it in the database makes the things easy :P

    What do you think?

    Cheers.
    .: Valeriano Tórtola MCTS WPF :.: http://www.vtortola.net :.
    7 มกราคม 2553 23:19

คำตอบ

  • From SQL 2005,it is possible to use OPENROWSET() function with new BULK OLE DB provider to read a file and load it as a rowset. It can then be assigned to varbinary(max) column in a single Update statement:

    CREATE TABLE dbo.employee(
     EmpID int NOT NULL,
     Name varchar(50) NOT NULL,
     Image varbinary(MAX) NULL
    ) ON PRIMARY
    GO

    INSERT INTO dbo.employee (EmpID,Name)
    VALUES (1,'John')
    go
    UPDATE dbo.employee
    SET Image = (SELECT *
                   FROM OPENROWSET(BULK 'C:\My Pictures\john.bmp',
                                   SINGLE_BLOB) AS a )
    WHERE EpmID = 1


    Thanks, Leks
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:19
    7 มกราคม 2553 23:40
  • Here is a great white paper on the topic from Microsoft Research:

    To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem
    http://research.microsoft.com/apps/pubs/default.aspx?id=64525

    Extract from there:

    "As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors."

    If you are using SQL Server 2008 then a good option is FILESTREAM. It provides the best of both worlds: keeping file objects in the file system where they belong and maintaining transactional consistency with the rest of the data.
    http://msdn.microsoft.com/en-us/library/cc949109.aspx
    Plamen Ratchev
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 3:30
  • Yes, you can store the Image in the database and also it is a good idea. But as Plamen Ratchev told it depends on the size of your images. As one of our application we have used to store images in SQL Database and its working fine. But once more I can say you if it profile display (small size picture) kind of picture then you can use database and also you can create a full text index for getting image faster from database.

     

    Thanks,

    Sandeep

    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 5:36
  • Bad idea.  I bloats your database an means that you have to worry about storage, i/o and backups for data that may not need the features of a SQL database.  If you feel the urge, rather put data in the FILESTREAM of SQL 2008
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 10:41

ตอบทั้งหมด

  • From SQL 2005,it is possible to use OPENROWSET() function with new BULK OLE DB provider to read a file and load it as a rowset. It can then be assigned to varbinary(max) column in a single Update statement:

    CREATE TABLE dbo.employee(
     EmpID int NOT NULL,
     Name varchar(50) NOT NULL,
     Image varbinary(MAX) NULL
    ) ON PRIMARY
    GO

    INSERT INTO dbo.employee (EmpID,Name)
    VALUES (1,'John')
    go
    UPDATE dbo.employee
    SET Image = (SELECT *
                   FROM OPENROWSET(BULK 'C:\My Pictures\john.bmp',
                                   SINGLE_BLOB) AS a )
    WHERE EpmID = 1


    Thanks, Leks
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:19
    7 มกราคม 2553 23:40
  • Here is a great white paper on the topic from Microsoft Research:

    To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem
    http://research.microsoft.com/apps/pubs/default.aspx?id=64525

    Extract from there:

    "As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors."

    If you are using SQL Server 2008 then a good option is FILESTREAM. It provides the best of both worlds: keeping file objects in the file system where they belong and maintaining transactional consistency with the rest of the data.
    http://msdn.microsoft.com/en-us/library/cc949109.aspx
    Plamen Ratchev
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 3:30
  • Yes, you can store the Image in the database and also it is a good idea. But as Plamen Ratchev told it depends on the size of your images. As one of our application we have used to store images in SQL Database and its working fine. But once more I can say you if it profile display (small size picture) kind of picture then you can use database and also you can create a full text index for getting image faster from database.

     

    Thanks,

    Sandeep

    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 5:36
  • Bad idea.  I bloats your database an means that you have to worry about storage, i/o and backups for data that may not need the features of a SQL database.  If you feel the urge, rather put data in the FILESTREAM of SQL 2008
    • ทำเครื่องหมายเป็นคำตอบโดย vtortola 8 มกราคม 2553 12:18
    8 มกราคม 2553 10:41
  • Thanks all.

    I pretend that my application runs with the principal database engines (SQL Server, MySQL and Oracle), so use concrete advanced features from each one is not possible.

    I was thinking yesterday night the same than Simon, that download a stream from the database will keep a connection busy for longer, it'll increase the database time usage and it'll make the database grow too much.

    In the other hand the pictures are part of the user data, so I'd like to have them in the backups when I do it, and also move them when I move the database to other place... Besides, 1Mb is far enough for a .png or .jpeg picture... I have even wallpapers with less than that size. The read/write ratio... is very high, usually only one admin user upload pictures when create a content, and that's in time to time, and the rest of the time that content is consumed by the end users. I'll keep a look to the article Plamen, thanks a lot.

    Cheers.
    .: Valeriano Tórtola MCTS WPF :.: http://www.vtortola.net :.
    8 มกราคม 2553 12:18