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

Of course the question is also is the goal speed or size? Does the trade-off of serializing and deserializing the whitespace - resulting in a large piece of data - payoff in faster in and out time vs the time it takes to compress/uncompress.



I don't know the answer, obviously, but it seems to be part of the question.

In my case I would say it is both, since these large text file can be compressed a great deal, and the end user does not need to see the file all the time, so once the file is imported it is just there in case it is needed for future reference so the less space used the better.



Also I am planning on moving this field to a separate table with a one to one relationship so when using the Browse Dialog this field is not included and only when it is needed by the internal process or the end user wants to take a pick at the file it will be shown in a childform.



Greg, thanks for the links and the comment, I will check them out.

Edhy Rijo

Edhy - You are normally very fast in posting your results, so I would like to know if You got it working ?

/Teddy

Hmmmm, that must be a bad habit just kidding



Seriously, I am still working on this but I went a different approach based on the application requirements which are as follows:

1.- The CSV data saved in the varbinary field is used once or twice. The data is imported into a table and once the records are imported this CSV data is not used anymore unless there is a problem with the imported records and the CSV needs to be imported again.



2.- Based on number 1, I don't need this big field value being loaded in any way during normal operations unless I need to read the CSV again to re-import the records.



So this is what I did:



1.- Create a new table related to the main table. This new table will have the varbinary field.

2.- In the main table BO, create a new property to read the varbinary field from the related table. This will free the main table from this field and speed up the loading process of forms using the main table.

3.- I added a new child form dialog to manage the request for getting the varbinary field via the BO property.



During this process, I am also playing with compressing the CSV data once it is converted to a byte array using System.Text.Encoding.UTF8.GetBytes(value), the compression looks pretty good since one of the CSV file has 9.3 million bytes and when compressed will have 2.6 million bytes. I am not using the .Net compression classes since I have a 3rd party library to handle many things, but I am sure the plain .Net class could do the same.



I am still in the process of refining the custom field property to properly handle getting the varbinary field, decompress it and convert it back to text to be shown in the child form dialog so when I am done I could post some code.



Trent's suggestion here is pretty good and would work for me if I would have to show the CSV data all the time when opening the form, but that is not my case for now

Edhy Rijo

OK - thanks, was just curious on what route You choose.

I will probably later try to implement Trents technique to see if I can improve my form with the XML files.

/Teddy

That technique it will give you the impression of loading the data faster and in fact it will do that because all other fields will be loaded first and then it will go get the varbinary field's data.



What worried me more was the fact that my main table is being used in several other forms as a parent table and all those form where suffering from slow loading and searching for records because when using the Browse Dialog all fields would be included in the search including the varbinary even though this field was not shown in the browse dialog, by removing this field from main table and putting it in a child table fixed my problem.

Edhy Rijo

This is all good stuff. One other thing I thought I would mention is well is that you can also pull from a VarBinary field asynchronously as well. So this isn't just for saving, but retrieving also. This is also what we do in our medical app for images. Most times you don't even seen the progress bar come up. But when a large file is being retrieved over a slow connection, then the end-user will see the progress bar and know that it is being downloaded. Once we added this functionality a year or two ago in the app, it made a huge difference to our users. Just another item for thought here.

OK - i tried to get this to work, but am apparently missing something, because i get this error:

BusinessLayerException
  The field could not be truncated.

Source     : MicroFour StrataFrame Business

Stack Trace:
   ved MicroFour.StrataFrame.Business.BusinessLayer.TruncateBlobField(PrimaryKeyValue pk, String fieldName)
   ved MicroFour.StrataFrame.Business.BusinessLayer.PushBlobFieldThread(Object parameters)

Any hints?

Teddy, are you referring to the PullBlob or PushBlob here?

It was the PushBlob, but I have it working now.

I had a null value issue on my test BO.

It's pretty cool.

Thx.

/Teddy

Great....glad you got it going! Yeah, it is really nice when you want to save or retrieve data asynchronously. Especially when dealing with larger data saves.