SQL Server 2000 is actually quite efficient about storing binary data. Microsoft did a lot of work to make it that way. Earlier versions weren't as efficient, and the myth continues that it isn't a good thing to do.
That said, you'll almost certainly have an easier time developing and maintaining the entire system by using the database to store file locations. That way you can use SQL Server to search and sort images, but use the file system to actually store them. Besides, adding and retrieving images from SQL Server is a bit of a pain, but you only have to code it once.
Don|||::That said, you'll almost certainly have an easier time developing and maintaining the entire
::system by using the database to store file locations.
I want to counter this. Storing file locations in the database has a plenthora of negative consequences:
* Editing: your editros needs access to the file location. Oh, no problem from the same website. What if you want to add a rich client application for image editing?
* Scaling: add more web servers, and - well - your problems are starting. How do you synchronize the file stores?
* Backup: two backups, instead of one.
* Integrity. How do you make sure you dont have images left over in case of a program fault while uploading? Tip: it is possible, using Seviced Components and using such a serviced component for file manipulation, together with the quarantees provided by a Compensating Ressource Manager I am just dure 99% of the developers have no clue what a CRM is.
When the images belong to data in a database, they should be stored in a database - everything else is a hack. And hacks come back nad make you pay for using them, normally pretty fast.|||for the net working, I decide to use three computer in the beginning, Web server, sql server, and storage server for each computer.
sinerio 1
If I only store file locations in SQL server and store file in file system, that will reduce lots of workload in SQL server because web server only get the path from SQL server, then it will retrieve the file from the file server. is this more efficent than sinerio 2 because it transfer the work load to web server from SQL server?
sinerio 2
However, if I put database in storage server, and I put file in image field in SQL server , the web server has to go through sql server then go to storage server. the SQL server has to process the file and send it to web server. will this have more workload on SQL server than sinerio 1?|||Hi,
I'm not sure I understand scenario 2. Do you mean putting the SQL Server database on the storage server?
Your assesment of scenario 1 is correct, in terms of less workload for the SQL Server. I would guess that scenario 1 would be, because the presumably larger image data would go directly from the storage server to the Web server. Scenario 2 would have it go from storage to database server to Web server.
Thomas will no doubt have an opinion about this as well. ;-)
Don|||Yes! donkiely, I mean putting the SQL Server database on the storage server.
one more question:
If I write a VB application installed in my computer (client), and the server setup is using scenario 2, how do I write this VB program to pass the fire wall and get the file from harddrive of storage server to my local computer?|||sorry! the previous post is WRONG. it should be "sinerio 1" NOT sinerio 2.
how do I get file from storage server to VB application in sinerio 1.|||::Thomas will no doubt have an opinion about this as well.
Avsolutly. Your assessment is correct: the workload for the SQL server is higher when the images come from the SQL Server.
BUT:
* Is this relevant? You should hardly ever get images from the SQL Server - ASP.NET would actually take them from output cache most of the time :-) Caching is anyway the best way to handle this (images are static).
* I this turns into a problem, you could come up with a federated database, still keeping it all in the db, and making the storage server a sql server, too. This can be a pricing problem, though.
The real question is whether this additional load makes a difference. SQL Server is pretty fast in handling binary data today.
And I can not repeat oit over and over again: caching can and should kill this overhead nearly to nothing.|||
Hello, this is my first post...EVER. :)
I've read many of the arguments that you should or should not store a digital image in a binary or image field in SQL server. Whether the debate continues or not, I believe someone (actually many new developers) are looking to you who are MCP's and MCD's and all the others to actually SHOW them HOW to do this. Only through the experience will they understand and be able to evaluate performance within their environments.
So, this is something I haven't done since my old VB 6.0 days where I had to use the GETCHUNK method to chop up a binary file and store it in a database. So, I'm curious what the NEW method(s) would be. I am in the precarious predicament of working at an international bank that actually WANTS to store images in a database.
I thank you and await your replies.
Bill (non-MCP)
|||
Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim connAs New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn.Open()Dim cmdAs New SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn) cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = Request.QueryString("ID")Dim drAs SqlDataReader dr = cmd.ExecuteReader dr.Read() Response.Clear() Response.AddHeader("Content-type", dr("MimeType")) Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()As Byte = dr("Data")Dim blenAs Integer =CType(dr("Data"),Byte()).Length Response.OutputStream.Write(buffer, 0, blen) Response.End()End Sub|||Create a page called ViewPicture.aspx.
Insert the above code.
Anytime you need an image, call ViewPicture.aspx?ID=123 where 123 is the image number.
Set the page with the appropriate caching options, and/or add SqlDependancies if your images can change.
The above assumes you have a table named "Attachments" with columns ID, MimeType, Filename, and Data.
ID would be an int, Mimetype would be a varchar or nvarchar of appropriate length (I use 50, but I use a very limited list of MimeTypes), Filename would be a nvarchar(253), and Data would be type image.
|||Thank you very much. I appreciate the help. I'm pretty confident that I can translate your code to C# since I've been programming in VB for about 15 years.
Would you mind indulging me and tell me if the MimeType is required? If so, why?
Thanks again.
Bill
|||This is great for viewing the stored images. But, would you just use a fileupload control to store the image to the table?
|||
Yes, infact I've posted a few times code on how to use the file upload control in order to store images in the database. I'm sure a quick search on this forum will turn up at least one of them.
As for MimeType, it depends on what you are storing. If it will always be say a jpeg then you already should know the mimetype, but if you want to be able to store say jpeg, bmp, tiff, png, etc then you need to know what type of file it is, and I've just chosen to store that information in the MimeType field since ultimately that is what will need to be sent to the client, and it allows me to store any type of file that the client's system is aware of (Excel spreadsheets, PDF's, Zip files, etc).
If you don't supply the MimeType, then the client's browser will most likely treat whatever you are sending as a blob and will force you to store it to disk rather than possibly opening it in an appropriate application (Winzip for zip files, excel for excel files, or displaying the picture in the browser for jpeg's, etc).
sql
No comments:
Post a Comment