Just a day ago I received phone call from my friend in Bangalore. He asked me
What do I think of storing images in database and what kind of datatype he should use?
I have very strong opinion about this issue.
I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Yes, it is very much easier to manage the image’s path than the image. But, how can we secure the image from being not accessible without the usage of database. Is that the responsibility of the administrator to protect those images?
You can ask your Network Administrator to hide them from viewing from folder
There are situations where storing images in the database, alongside the data they concern, is an excellent idea.
For example, in the medical field, it’s very frequent that an image and its metadata are a single logical blob.
You don’t want to have “pointers” to images that can be altered or removed behind your back. You don’t want users to add or delete medical information outside transactions. You don’t want to possibly deal with two different security systems (one for the database, one for the file system). You don’t want to split backup or fall back strategies.
Nothing prevents you to link two separate databases and to keep blobs even on a separate server (DTC is your friend), one for data and one for images. That’s what we do in our application (400GB of data, without images).
As you mentioned yourself, DTC is your friend. You can encompass SQL Server Changes and File/IO changes inside a single transaction.
Hey,
I used to think that storing the binary files in the file system and the links in the DB columns is the way to go, but there are times when you want more reliability, you don’t want someone changing the file names or moving them somewhere else.
Was just wondering on the performance implication of storing the binary files in a DB versus storing them in a file system. you have any clue about this?
Yes its gr8 idea , i was using that technique for single desktop applications. at present m working on a multi-user distributed application ( centrilized system). And m facing problem with this techinique because the Remote Location is password protected ( the pc where databse is installed ) , and if i do provide access to that Machine , it will allow to access the resources of that computer , will u plz suggest me the settings by which i can restrict users to access resources of that computer and and can allow only reading images from a particular picture folder( contains images )………..plz guide me
I am new in this field
You suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using dreamweaver VB scripting with asp.net to developing the sites.
Thanks
Refer these
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;317016
I been trying to get the answer for this for long time. I dont’ know either, how to store the filepath in the database. Did you solve this issue. Pleaes let me know if you know how to store. Atleast I would like to see it displayed in a lable control or something if not in DB. Cuz I assigned a directory path using “~/picture/ samples ” to a string variable, but when I tried to dispaly this string variable in a lable control I dont’ see any value at all. Can anyone answer me why or tell me how to get it displayed?
thanks
Ama
Hello,
the implementation depends on what technology you use for the middle tier, but the logic for the images is very similar: the players in your game are Web server, database server and middle tier (.NET programming language, lets say which commands the database and serves the Web server.)
So, you save the physical images on the web server (well, in an ideal world you have a media server), then in the web.config in the middle tier you save the first part of the path to the image, i.e. the directory path which is common for the images. Then in the database you write only the picture unique path and the picture file name. (And you will need a imageID or something to distinguish it from the other images when you search for it in the database table). Now, when you need an image displayed on your website, you will have a control (some kind of encapsulated logic) to which you will pass the imageID and the control will go to the database, get the image info, concatenate it with the information from the web.config, and voila – you have a image path which works. I am sure that there are plenty of examples on the web.
you can use the image data type instead of string var
for eg. Image img=”src path”
and pass this img to the label
can you please demonstrate on how to do that. I need to store file path to different types of files such as pdf, doc, audio, video, etc. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
emilia
The challenge behind the directory structure is security and if you’re in a SOX environment its hard for the developer to have all of the tools to properly configure the file server, the web server, etc. all in the name of what? Performance?
If performance weren’t the kicker, the database seems like the way to go (assuming its sql 2k5, which argues that its faster than oracle up to the TB). With the DB I know where my files are and I have one less thing I need to do…(assuming your files do not change; such as imaging files).
Therefore, an arguement shouldn’t be made w/o some kind of performance justification…so please would the smart guy in the room tell me how much faster a file server is versus a database server? If its nominal the answer should be what’s the least expense for your organization to maintain? In a SOX cause, I would argue the db approach…
Storing images on the database, consumes the available capacity of the database files.
For example, if you have 2000 medical records, with ten 5MB images for each record (a medical record could have more than 50 images, but I just chose a small number), then 10GB of the database available size is consumed for the images.
For larger databases, that could be a problem.
Correction for the previous post:
It’s 100GB, not 10GB.
can you please demonstrate how to store the location (URL) in table. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
I am trying to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using visual studio 2005 VB.net with asp.net to developing the sites.please guide me how to solve.it’s urgent
Just pass the file path as the column value in the SELECT statement and make sure the files are located in Server’s directory
You might want to create two fields, one for the path, one for the filename. As an example, store ‘patient\images\’ as the path, and ‘Patientimage1.jpg’ as the image name.
Instead, you could store the path in another table if it is the same for all images, or images of a certain type. That way you could save different paths for different types of files, such as .PDF or .GIF.
I like the idea of storing in the database, as this keeps the system from having orphan images, and keeps backups and restores current (keeping records and images together).
I would have to disagree with the author about a one size fits all of storing images on the file system rather than in the database. It all comes down to my favorite answer, “It depends!”
How about this senario, a shop that has over 20 million images stored on a file system, each of the images are under 1 Mb in size. If you look at it, strictly from a DBA’s prespective, then yes, storing files on the file system and having the pointers stored in the DB sounds reasonable. Unfortunatly, we live in the real world here and it you looked at it from a backup and recovery standpoint, how long would it take a backup system to backup over 20 million small files. Many, many times longer than it would take to backup one large file.
Bottom line, is before you make a descion to go one way or the other, make sure you have factored in ALL options.
Marc –
For your situation, it is acutally recommended to store those images in SQL Sever and not on the file system. SQL Server processes files under 1 MB at a rate of 2x’s faster than that of NTFS. So in your case, it would actually be a benefit to place these images on the server. In contrast, it is not recommended to store files over that 1 MB threshold as you then start to get into potential performance issues and fragmentation of the database.
I’m working on a project. Actually, the software is ment for Radiology department. The software suppose to keep all cinical information, and medical images. (e.g. Ultrasound image, CT, MRI )
I’m intend to use C# 2005 with MS SQL server 2008.
Please where do you suggest I should store the medical images of the patient?
in the database or on the file system.
Please I need help.
This is a hard decision to decide. What about maintaining the images. If you store in a file system, how do you delete them when you delete the reference from the DB? I want to store images for classified ads, and don’t want the images lingering around for years after the add has been gone. In my case all images
Also, mainstream sites that handles millions of pictures like cars.com seem to use a files sytem. They also seem to create a lot of new directories. From my understanding you don’t want to store more than 3000 images in any given directory or there will be a significant penalty.
They have a directory structure like so:
http://images.cars.com/thumbnail/DMI/148974/F4777A.jpg
http://images.cars.com/main/DMI/148974/F4777A.jpg
http://images.cars.com/supersize/DMI/148974/F4777A.jpg
There are 3 different size images. The images sizes are 2k, 16k, and 50k respectively. The images load instantly. Do you think they can get that kind of performance from a DB?
sorry the last one was:
http://images.cars.com/supersized/DMI/148974/F4777A.jpg
Also never finish the sentence “In my case all images..”
should say “In my case all images are probably less than 50k each.
I prefer to store in the DB. Space is not an argument. You will either have a larger DB or you will need a file server fo rlarge sites with a lot of photos for example. So its much cheaper tohave it in the DB.
The insert/update/select is by no means “massive” as the author says. You would have a memberid or some other indexed cilumn in which you are using to grab the row.
I think it is much nicer to not have to worry about a file server. The performance hit is marginal it is definately worth doing.
Plus it also allows you to stop using cffile and you cna use cfimage tags and functions to alter image to a blob and pas sit to your stored proc.
Its a win win all the way around really. I own a dating site with over 100k members and store all profile pictures in the DB. Each member can have up to 200 photos. When I display the gallery I do a next n and only show 10 at a time.
it is a wonderful site providing valueble information for my project
I would like to know which drives for the NTFS files system would be preferred.
We have a SAN with combonation SATA and FC-drives.
Can I still store the binary\photo files onto the SATA (15k) drives with HBA(4gb) and get good performance on the reads and writes? Very curious about this one? The system will produce fast transaction from mobile pda’s and about 6000 users will update within about 3hr period. Any other performance recommendation will be greatly appreciated. I have already sepreate all data files on seperat arrrays and give tempdb 6 fix length files.
Please comment at will.
I definitely agree with Pinal regarding the images in a database – it is a bad idea in general. The reason for this is scalability: over time you will eventually come to an IO bottleneck if you keep your images in a database. The you will try to purchase more and faster storage, then you will have to think about partitioning of your database, then about complicated backup strategies. On the other hand, if you keep the images on disk, there are many free tools (Microsoft Resource toolkit, for example) which will give you the opportunity to backup only the changed files, and eventually you will come to a point when you will need a standalone media server.
In SQL 2008 you can look into filestream, which changes things a bit.
Pictures in Database.
1) Is there any reduction in the image quality of the picture?
2) If the database crashes (hopefully there is a backup) do you loose thousands of pictures?
3) Which loads into a web page faster? The DB picture or the file system picture? I would think the DB picture.
4) If the pictures are not in the database you can easily review them, edit them, and update them if you need to without digging into the database. This can be a plus, or as said, a minus for medical or legal pictures. It could be a mess if the relation to the database links is not clearlly understood.
5) If somone needs a copy of the picture, say on flash drive, how easy is it to extract it and provide that?
As I read above, true and must be concern for “It depends of your company strategy and direction”. We lived for almost 8 years with, SQL for main human resource information of “30 GB” size, with 1T of image with average 150k on file system. We hardly face problem. linked with the “path name”, “filenumber”, File Name”. Because some time the ID change to new one, so we must have different number for it in our database, with new path link in his record.
First, we start it fine, but now as I see the same person come back after a while with new picture and information to add in our system. New scan added to last point in our file system. Whish after a while we start to have defragmentation process for file system to keep all the image together.
But after reading here, I Liked the Ida of having the second Database, which it can be index, Linked with first SQL, Cluster, and a lot of easy to recover for DR, redundancy, backup, even in the future if we feel in performance problem, we can easily add more server for load balance, with share hard disk like SAN, or IBM Shark.
Also, I see in important point of a lot of IO in file system, which is most of the time killing the system or wait a lot more second to get in process. But in SQL, in the future you can have a lot of backup for yourself to play with or even to expand, like adding extra server for load balancing, fail over and a lot more, which a lose don’t put you in bad shape as a designer or responsible for the project .
From the application side, once the employee enter ID, automatically he get in the back process to “his PC cash” five to ten image arrived. And as SQL, or DB2, you can have a lot of option to play with as increasing hardware like, cash memory, increase number of server, a lot of tools to have your data also in cash level and so on. With file system, you will see yourself a lot restricted, and not mush to do.
Regard…
I am not disputing about storing images in the filesytem and store rest of the file info in the database. I have seen so many links that teach you from the scratch in different logic than my code. but I am trying to fit only this binaryfile blocks into my code logic. Though my destination is to get to display the directory path in the browser, I think I have to rephrase my question to avoid confusion at this point.
I only want this single question answered please.
Could you store directory path in a string variable and display that value in the browser through any other webcontrols or directly? If yes please show me how did you do it?
Thanks
Ama
Just run a SELECT * on your table with images and then run a select without the blob field and you will see the performance difference.
IMO…
SQL Server 2008 has a option wich alocate phisically (file system) the file stored in a field, this does not stay together the “normal” content row.
The ACID properties of transactions are difficult and complex to maintain if you use a file.
You may want to write to a temporary file and rename the file on successful transaction.
Moreover, optimizations to database design and query style can be more helpful.
As suggested above you can use another table to store the images, and retrieve the image only when necessary. Thereby, reducing the load on database server.
//— You can store the file location into the database using the below code. –//
//– you need to have a FileUpload control in your page to upload the images –//
string myFileName = FileUpload1.PostedFile.FileName;
string copy = Path.GetFileName(myFileName);
try
{
string Upath = @”~\Images\” + copy; //– This is the server path where the images are stored–//
FileUpload1.PostedFile.SaveAs(@”C:\Images\” + copy); //– This is the same server path in file system using file path instead of root directory. This is just for an example –//
string sql = “INSERT INTO Frm_ATAC_Events (Content_Type, File_Path) VALUES (‘” + FileUpload1.PostedFile.ContentType + “‘, ‘” + Upath + “‘);”;
SqlConnection connection = ConnectionManager.GetAetnaForumDBConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
//– You can use the below code to retrieve the images back to a grid view–//
DataSet ds=new DataSet();
string sql = “SELECT FILE_PATH, CONTENT_TYPE FROM Image_Table”;
SqlConnection connection = ConnectionManager.GetAetnaForumDBConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds, “Images”);
GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
//— Now Bind the ImageUrl in the gridview to the File_path field using the code ‘Eavl(“File_Path”)’.–//
This should work very well…
I think the size of a Data Base is not Important anyway. Because today’s DBMS’s are very Powerful (such as SQL Server 2008).
But its better you use two or more File-group For a Data Base.
You can determine each table goes to a different File-Group and even you can determine each File-Group save in a different Drive
example:
Table1 save to File-Group1 And File-Group1 Save in Drive C
Table2 save to File-Group2 And File-Group2 Save in Drive D Of Hard Disc2
Today’s if someone say: ” it’s not correct to save the images to the database because the database will huge ” Like someone say : ” it’s Not correct to save the images to the Hard Disc!”
how can we storage image in database and how to retrieving it in sql command prompt.
The simple and effecient method is to store only path of the file in a column and store actual images in the server’s directory.
Could any one tell me how can I export the IMAGE or any other FILE which is inserted in database?
how to retrieve it in sql command prompt?
I think it depends on how well the indexing system of the DBM works. When you only store the filepath/filename in the database you practically handle the indexing yourself. That at least in theory should be faster unless you cram thousands of small files into one directory — more than 2000-2500 files and you will move at a snail pace — try placing that many files in a directory and then try accessing it – it will take forever. So depending on how you arrange those files it could even be slower.
I work on a daily basis with a 400K+ records IBM DB2 table containing 16kb images and access time is usually under 1s so it’s pretty good. And that’s an old DBM version(7.2) running on an old IBM OS2 operating system inside a virtual machine so there is much room for improvements.
I think every DBM has caching mechanisms that work quite good so we shouldn’t be afraid of using them. As I said storing only the filename should be faster but only if you spend the time on optimizing the directory structure and for me that is not worth it. Just let the DBM handle it.
Of course for databases with tens of millions of records things could be different and performance issues could force someone to reconsider.
Hi Pinal,
Everywhere its said not to use image /BLOB data types. But when the database have those columns and which is unavoidable then what we can do. I need to replicate a table and this table take 5hrs.. to replicate. Can you suggest how to increase the performance for replicate this table.
Thanks in advance.
Good day very cool blog!! Man .. Excellent .. Amazing .. I will bookmark your site and take the feeds also?I’m glad to search out so many helpful information here in the submit, we’d like develop extra techniques on this regard, thanks for sharing. . . . . .
i want to store image database