Hi guys,



I am currently saving big text files (450,000+ lines) in a Varchar(Max) field, for a while the performance of getting a record with such data decreased and it is noticeable in the browser dialogs and reports.



I am wonder is there is a better way to deal with so much data in a single field, so would it be possible to kind of compress the data in the field somehow?



I know that I may have this field in another table to separate it from the main table and do some magic to make it look as part of the record in the form, but if that is no the best way I am open to any suggestions.



Please keep in mind that currently I am showing the data in the form in a readonly textbox, but I can change the design to not show it up when the form loads and put a button so the user can get the data and then show it, but at least that will not slow down the browser dialog.



Thanks!

Edhy Rijo

The technique your using Charles wouldn't do any compression. I.e. a space is ASCII 32, data just like anything else. Now if the file were using some sort of compression algorithm, then you'd get that. I.e. JPG, PNG etc have compression built into them. So, if the PDF file format includes some compression, then you'd get that compression. I think it's a pretty simple call to use the zip tools in the framework to compress the resulting byte stream. I'd check out the tools area, if memory is correct. I check into this later today, as I'm a curious guy.

Well Charles, Greg,



Now that you mentioned compression, yes that is something I would like, since my files are just basically text files I am sure any kind of compression will help.



Since I have not tried any of the techniques, I believe I would have to do the following:



1.- Read the text file to stream

2.- Compress that stream

3.- Save it to the Varbinary(max) field using Trent's technique.



Am I missing something here?

Edhy Rijo

That's what I'd try.



BTW, I took a look and my memory failed me. There is some compression stuff in the StrataFrame.IO.Compression namespace, but it is all related to creating packages (very handy for ziping multiple files into one file) and not what we are talking about (compressing/decompressing a stream).



It turns out that it is the .NET library in the IO.Compression namespace. Here are some links related to this:



MS info on this namespace. We're interested in the DeflateStream and GZipStream classes:

http://msdn.microsoft.com/en-us/library/system.io.compression.aspx



This one explains a bit and then provides a utility to handle zip files:

http://blogs.msdn.com/dotnetinterop/archive/2006/04/05/.NET-System.IO.Compression-and-zip-files.aspx