Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wgpubs
Yak Posting Veteran
67 Posts |
 Posted - 2005-03-29 : 14:19:45
|
| I'm create an ASP.NET application that needs to be able to load a variety of data (text, html, word documents, pdfs, etc...) into an column that is of type Image.Anyways, never worked with the Image datatype ... had the following questions:1. How to load/fetch the data in an Image column for the different types of data (e.g. text/html chunks, word docs, pdf docs, excel, etc...)?2. Any good links/resources for working with the Image datatype like this in .NET?Thanks much - wg |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 14:40:36
|
| I would suggest that you avoid using image data, and store them as files instead. Books Online has some information, and the .Net documentation will have more, but it will still be easier to manage and display these document types online if they are served up as files. |
 |
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2005-03-29 : 14:52:35
|
| I'm getting all kinds of confusing advice on this topic now. From what I understand, with SQL2K ... storing docs in Image columns is pretty close to storing them on the filesystem in terms of performance. Also, I wanted to use FTS against the docs and I thought the benefits of storing them in an Image column (e.g. backup, FTS, etc...) outweighed the negatives.Any other thoughts? Are the benefits of using Image columns inaccurate?thanks - wg |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 16:00:59
|
| They're harder to program, especially for ASP.Net. If you're going to display a Word or PDF document on the web, you have to have your .Net code return a byte stream from the database with the proper content-type, or the browser will just render gibberish. Using files that map to hyperlinks or frames, this is handled for you more-or-less automatically. The performance is probably about the same, but it's less overhead on the database server to serve documents as files. SQL Server image data storage is also space-inefficient compared to filesystem storage.The advantages for FTS can also be gotten from using Indexing Service against files, which can be queried using linked servers. I don't know if I'd call it "easier" than using the image datatype with the filters, but it's possible.As to whether to use image data or files, it all comes down to experience. Some people have gotten it to work well. I haven't, and I don't recommend it to developers who aren't experienced with it. I'm also not convinced that it's a good application for web apps because of the way images and other non-HTML documents are handled via HTTP, which handles files very easily and naturally. Most people point to TerraServer as an example of how well image data works in SQL Server, but negelect to mention that TerraServer is an esoteric application, NOT a typical app that most people try to deploy. .Net makes it fairly easy to move image data into and out of the database, but this support is severely limited in other languages. Developers love this, naturally, but don't have to deal with the excess space usage and other issues that DBA's have. |
|
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2005-03-29 : 17:08:16
|
| Like everything I guess their are pros/cons on each side! I'm definitely a strong developer than I am DBA ... and based on what I have gathered so far and the size of the site, etc... I think I'm going to prototype something that uses SQL Server to hold the actual document data. I've used the 'pointer to file system' approach in the past and to be honest ... it's caused me plenty of headaches as well. Given this ...1. Are there any good examples to working with Image fields via C# that you are aware of? 2. Any database specific things I can do to make the handling of Image columns more scalable, efficent and better performing?Thanks again for your input Rob.- wg |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 20:01:00
|
| 1. No, I don't really research them because I don't plan on doing it. Someone posted a good link on MSDN about the pros and cons, but you'll have to search the forums for it. I've already tried without any luck, but you can seach for "image" and you'll find tons of debate.2. Create your tables with TEXTIMAGE_ON and use a separate filegroup for the text/image data. Preferably on a completely separate disk(s) from the rest of the data and/or indexes. See Books Online under "CREATE TABLE" for details. Watch your database recovery model, if it is set to full and you do a lot of file manipulations your transaction log may grow faster than you planned and may need to be backed up more frequently. Using bulk-logged or simple recovery may help here. Again, check Books Online under "recovery model" for more info.Here's some full-text articles too, in case you're interested:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_fullad_55mb.asphttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/indexsrv/html/ixufilt_912d.asp |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 20:24:33
|
| I should have added this earlier, regarding backups: look VERY closely at how much image data you plan to put into the database, now and within the next year or two. Keep in mind that the data is stored in 8K increments, so you can get a lot of fragmentation and substantial wasted space if you store relatively small documents (less than 75K) Taken together, this can make your database bloat very quickly, and make full backups harder to do. You can mitigate the problem by using filegroup backups, but when you get to the tens of GB range it can become difficult to organize. This is something that can really bite you in the ass hard if you don't plan for the future. Storing documents as files lets you separate the image and database backups so that they do not impact one another negatively, and it's easier to do incremental backups on files than it is to do a large differential backup on SQL Server.I can tell you that I have a 3rd party app at work that stores something like 3.5 million bill image files. It is a bear to manage on the filesystem, but I calculated that simply moving these files into SQL Server would increase the space used by 60-70%, just because of the 8K boundary. So 540 GB of files would grow to at least 1 TB, and will only increase. |
|
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2005-03-30 : 12:51:41
|
| "The advantages for FTS can also be gotten from using Indexing Service against files, which can be queried using linked servers. I don't know if I'd call it "easier" than using the image datatype with the filters, but it's possible."Any resources on the web for how this is done?How are others making their files on the filesystem (e.g. pdfs, word docs, etc...) searchable along with data stored in their SQL DB (e.g. text or html chunks). I need to marry the two ... and I'm not sure what the best approach to doing so would be if you split the content.Thanks - wg |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-30 : 20:00:08
|
Here's the Indexing Service home page:http://msdn.microsoft.com/library/en-us/dnanchor/html/indexserv.aspRead all the sections in the left-hand panel, there are examples on how to query it. Here's how to set up IS as a linked server in SQL Server:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_0h0l.aspHere's an article on optimizing FTS too:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04f9.aspThe author shares the view that image data is best served as a file from a web server, rather than a BLOB in a database. He also links to this site that has more pros and cons and even MORE links that discuss them:http://www.aspfaq.com/show.asp?id=2149And there used to be an absolutely PERFECT MSDN article on how to use Indexing Service with SQL Server, but I can't find it anymore. Setup, query techniques, examples, optimizations, everything all on one page. Naturally MS decided to fuck it all up into 20 different articles.  Don't get me wrong, they're all pretty good and informative, but they ALWAYS pull out the best one-stop shopping articles and replace them with entire dictionaries' worth of stuff. Feel free to search for it, if it's still there you'll know it right away, and could you drop the link back here thanks? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2005-03-31 : 17:10:57
|
| I searched and nada.I guess a way to merge FTS search results with those as a result of Indexing Services on filesystem data is the corrolate the data based on 'rank' and what not, no?Any other ideas?gravitas - wg |
|
|
|
|
|
|
|
|
how to save/fetch text, html, etc.. in Image cols?