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

Hi Edhy,

I also have the need for saving files (XML files) related to a record. I use a related table and before saving I compress the file using

MicroFour.StrataFrame.IO.Compression.PackageFile

Then on my maintenanceform I have a button that will extract the the file, transform the XML to html via a Stylesheet and then show it in a form with a Webbrowser control through a ChildFormDialog.

I know your files are not XML but just to give some inspiration.

/Teddy

Do you have to keep the large text file in the table at all? 

Does the text file change frequently or is it static? 

Russ Brown (Using C#2010/SQL Server 2008)

Teddy's answer is what I was going to suggest. Try compressing the file and saving it to a VarBinary field instead, see if it gives you better reliability.

If needs be, you also have the option of saving using blobs, which is what we use inthe My Account area for the bigger files (basically sends a larger file as bite-sized chunks to prevent the connection from crapping out in the middle of a huge transfer.)

Edhy,



You are aware that you can update a single field as a blob or chunk with events that notify you of the progress, right? In our CRM, when we save a download file, it could be really large, and not always save correctly. Same goes for our medical application where we allow media to be attached to the patient. You ca set the blob size and then even show a progress bar of the save. If this rings a bell, let me know. Otherwise, I can try and post a small sample later (today is going to be crazy so it might be tomorrow before I get it posted).



I remember posting this on the forum before....I will search.

FYI, the method name on the BO is PushBlobField or PushBlobFieldAsync.

File is part of an import routine, it could change once or twice during the import process if any error is detected on the columns format of the file, but once imported it is preserved on the database so it can be used from any workstation instead of a file location.



Since it is a CSV file I simply read the file to string and save the string, again, this worked just fine until things started to get slow

Edhy Rijo

Thanks Teddy, sure this may also works in my case, will consider it.

Edhy Rijo

Trent, Dustin,



I was not aware of the Blob field, while working with VFP in the past we always tried to stay away of Blob fields for inestability reasons and it did not cross my mind to even think about them in this case. Since these CSV files are really big, I like the idea of compressing them and be able to show a progress bar during this process, so I will take you on your offer for the sample in VB if possible (I know you are switching to C# )

Edhy Rijo

I will try to get you a sample...I kind of think I have posted one out here before for Kenneth Langley. Might do a quick search to see if anything comes up. Also, we just called this method blob as some databases (i.e. Oracle) call it blob. But in essence you could do this for any field type. But in SQL Server, I would use a VarBinary(MAX) field to store the data. So you would just convert your file into a byte array and then save that into a VarBinary field.



I will try and allocate some time to write this sample...sorry for the delay. I am getting somewhat hammered this morning (and I am not referring to beer drinking contests! )