Solved

Image storage in Sql

Posted on 2010-11-22
14
703 Views
Last Modified: 2012-05-10
Hi,

I was working with images like storing the images on any location in HDD and mentioning its full path to sql. But this sometimes make trouble when you work on network based software and even location cannot get changed easily.

So I thought storing images in sql, but also thing that it might increase the size of database abnormally and people will not send the sample database to me with ease.

Can you suggest some pros and cons so that I may take the decision.

Thanks & Best Regards

Jay Khatri  
0
Comment
Question by:jaykhatri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 34186337
HI

Basically, it depends on your application ...

Read the following
http://www.databasejournal.com/features/mssql/article.php/3719221/Storing-Images-and-BLOB-files-in-SQL-Server.htm

And if you want a really deep comparison , read that , it worth the effort


http://research.microsoft.com/pubs/64525/tr-2006-45.pdf
0
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 34186365
0
 
LVL 15

Expert Comment

by:Praveen Venu
ID: 34186550
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34186561
Advantages of Storing BLOB Data in the Database

Storing BLOB data in the database offers a number of advantages:

It is easier to keep the BLOB data synchronized with the remaining items in the row.
BLOB data is backed up with the database. Having a single storage system can ease administration.
BLOB data can be accessed through XML support in SQL Server 2005, which can return a base 64–encoded representation of the data in the XML stream.
SQL Server Full Text Search (FTS) operations can be performed against columns that contain fixed or variable-length character (including Unicode) data. You can also perform FTS operations against formatted text-based data contained within image fields—for example, Microsoft Word or Microsoft Excel documents.

Disadvantages of Storing BLOB Data in the Database

Carefully consider what resources might be better stored on the file system rather than in a database. Good examples are images that are typically referenced via HTTP HREF. This is because:

Retrieving an image from a database incurs significant overhead compared to using the file system.
Disk storage on database SANs is typically more expensive than storage on disks used in Web server farms.
The following code shows how to use ADO.NET to write binary data obtained from a file to an image field in SQL Server.

public void StorePicture( string filename )
{
  // Read the file into a byte array
  using(FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
  {
    byte[] imageData = new Byte[fs.Length];
    fs.Read( imageData, 0, (int)fs.Length );
  }

  using( SqlConnection conn = new SqlConnection(connectionString) )
  {
    SqlCommand cmd = new SqlCommand("StorePicture", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@filename", filename );
    cmd.Parameters["@filename"].Direction = ParameterDirection.Input;
    cmd.Parameters.Add("@blobdata", SqlDbType.Image);
    cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
    // Store the byte array within the image field
    cmd.Parameters["@blobdata"].Value = imageData;
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}



Source - http://forums.asp.net/t/1096999.aspx
0
 
LVL 5

Expert Comment

by:SinghAmandeep
ID: 34186591
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34186730
For the performance point, the recommonded method is storing in filesystem as mentioned in below link:

http://blog.sqlauthority.com/2007/12/13/sql-server-do-not-store-images-in-database-store-location-of-images-url/

But if you requirement required to be stored in database then you have no choice.
0
 

Author Comment

by:jaykhatri
ID: 34186878
My application is windows based app that will have maximum 20 clients. I was managing this in ms-access with filesystem (but in filesystem either you have to copy the image to your desired location or you have to put that original path) In both way either we duplicate the file or file will be selected from diff location) since I have migrated to sql server. Thought should try this.
0
 
LVL 19

Expert Comment

by:elimesika
ID: 34187078
1) How many images are you going to store in DB and what is the average size of an image ???

2) Which SQL Server version & type (Express, Standard etc) are you using ???
0
 

Author Comment

by:jaykhatri
ID: 34187097
More than 2000, like employee, student, guardian photos and people can scan docs from my app and store. It can increase to even 5k.

I am using sql server express edn. 2005 (now I will use 2008 express edn.)
0
 

Author Comment

by:jaykhatri
ID: 34187188
And in addition one more question, if I add 512KB of image in the database, how much a database will increase in size. and how much it is related to the file stream.
0
 

Author Comment

by:jaykhatri
ID: 34187556
My idea is to make separate database for images so that people may send me the data only.

By this way I can handle the stuff.
0
 
LVL 19

Expert Comment

by:elimesika
ID: 34204294
HI again 5k * 2000 is about only 10MB , 2005 express can hold up to 4GB , 2008 express extends it to 10GB.
So, I think that you can easily store it in DB without any problem.

Database size increases according to values given in creation , if you want you can alter this by the attached code.

I do not think that you need a separate database for that , it will makes troubles when you want to join information from both databases , according to your requirements , one database can handle both
// The following will set database db_name to increase each time by allocation more 10MB 

alter database db_name
	modify file (name = db_name,  FILEGROWTH  = 10)

Open in new window

0
 

Author Comment

by:jaykhatri
ID: 34205868
I am really sorry for posting confused statement, 5k was meant as 5000 images and each image will take 512KB approx. I don't know the ratio of storing blob : filestream.

Regarding two separate databases its true that I have to make two separate connections which will make trouble in joining. So I dropped the plan and even I am thinking to go with filestreams.

Your Idea please ?
0
 
LVL 19

Accepted Solution

by:
elimesika earned 500 total points
ID: 34236725
HI

Still , you are talking about 2.5 GB at the max , adding to this the fact that you can compress the image before storing and decompress when read with seamless performance pain , I think that you can still go to the DB solution (Prefer to use SQL Server 2008)

Follow this article to learn how to compress BLOB data in SQL Server
http://www.codeproject.com/KB/database/blob_compress.aspx
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question