-1

I'm using VB.net , Entity framework 6 , SQL server 2008R2.

I have a case when on a varchar field I should save a very large text. But the number of characters on this text is not specified so I think that will be very large for that field. ( I know there's a VARCHAR(MAX) but I'm thinking also to have the data size in this field as low as possible ). Also for several reasons I can't use a file to save this text ( keeping only the filename on database ).

So I'm asking if is there any way to keep this large text zipped on database and of course to unzip when reading from database.

But I'm searching for a solution that will work with entity framework. So if now I'm using :

Myobject.mytext="..... My text...."

How can I transform this in order to put the zipped text ( if is possible )

Thank you !

alex
  • 694
  • 3
  • 15
  • 35
  • As a slight aside to your main question, a `VARCHAR(MAX)` stores only the data that it contains, so your data size concern isn't necessarily valid. – Martin Jul 30 '15 at 14:07
  • yes , but if there's a way to compress this data to a lower size it will be better. – alex Jul 30 '15 at 14:09
  • Can you tell me why you think it would be better to compress the data? What size of text are you actually talking about here? Databases are designed to store and rapidly access large volumes of data. Adding a compression and decompression step in your own code would not necessarily be a good thing. – Martin Jul 30 '15 at 14:11
  • Don't even consider this zipping and/or compression of data. You will regret that almost immediately. Consider a query where you need to look at that data. You will have to unzip/decompress the entire table to do this. If the concern is performance for other queries then maybe you can spin this data into its own table. – Sean Lange Jul 30 '15 at 14:19
  • @Sean - Sorry , I want to compress the text before saving to database. And this is for one field only. Why you says that I should decompress the whole table ? My question is : Can a large text be compressed before saving to database ? – alex Jul 30 '15 at 14:22
  • I didn't say you have to compress the whole table. I said that when you want to search for a row where some text is in this column you will have to uncompress the whole thing to search for it. You are overthinking this way too much. Sql server can handle massive amounts of data. When you try to get tricky it will end up causing you performance problems. Just use a varchar(max) and be done with it. I promise it will be much easier in the long run. – Sean Lange Jul 30 '15 at 14:25
  • 1
    [Compress it with .NET](http://stackoverflow.com/questions/2891787/vb-net-compress-decompress-string) and then store the result in a column that support [binaries](https://msdn.microsoft.com/en-CA/library/ms188362.aspx). – the_lotus Jul 30 '15 at 14:26
  • @Sean this case when I need to search for a text in that column is excluded. – alex Jul 30 '15 at 14:28
  • Meaning you will NEVER need to search the values in the column? Then you could compress it but I would ask how much that is going to benefit anything. It will make saving and retrieving slower because you have to compress and decompress the text. Seems to me that you are doing what is known as premature optimization. That is, you are using a nonstandard approach to fix a performance issue that does not yet exist. I would recommend keeping your query as simple as possible and changing it only when there is a performance problem. – Sean Lange Jul 30 '15 at 14:49
  • Re comments above. Compress with .NET and have some extra uncompressed columns containing relevant metadata about what is in the compressed data... – HansLindgren Jul 30 '15 at 15:05

1 Answers1

0

Sql Server does not support compression 'out of the box' of LOBs (VARCHAR(MAX) where Length>8k). So, the best approach would be to handle compression/decompression on the client, before you put it in the network towards SQL Server

So,

When reading:

  • Read column with compressed data (with EF)
  • Decompress your string
Community
  • 1
  • 1
HansLindgren
  • 339
  • 2
  • 9