Compressing Data for storage in SQL 2008 Database

[justinthehansen]

hello,

Im new to the forum but not new to LV. My current project requires me to test a product and post data to the SQL database. Earlier this week an engineer asked if I could just save the test data taken during the test interval and post it to the database I told him Id look into it.

basically this is my solution, take a data array and convert it to ASCII strings and port it to the database. I have my example code below just writing to a text file and reading to check my losses.

my question comes down to "How do I save a large data set to an SQL server without having to create a column for each data point?"

I currently am taking the data and creating coefficients which is great for assuming the curve over time but it doesn't actually capture the data.

my second question is " Am I reinventing the wheel by compressing this data? Has this been done before in LV?"

the basic problem is that I have an array of numbers in double format with theoretically an undetermined length. and an SQL server 2008 r2 which can take a 4000 character string in each cell, so using my method I have a compression ratio of about 2.12:1 so i can write 8000 digits of data. the resulting will only be stings of 8 significant digits so some loss but I overcome that by pushing the decimal point out.

I would love feedback and if anyone has done anything like this please share.

Cheers!

[wiebe@CARYA]

I compress my data all the time. I use flate (from zlib) to inflate and deflate data.

At first I used OpenG's zip file's VI's to deflate\inflate (simply download it and have a look), but by now it's just the call to the zlib dll.

I'd assume decent sql databases (has options to) do the compression automatically. Not sure though. You might want to check, it would save you the trouble.

[Yamaeda]

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-...
Correction, a varchar can be either max 8000 or (max) which set it to approx 2GB.

Apart from that, if space is an issue you could convert to an array of Singles (7 digits precision) and simply Flatten to string for storage, or possibly FXP depending on value range.

/Y

[wiebe@CARYA]

As long as you're not going to send the data as SQL (e.g. send the values as a string, not as values) ASCII is indeed not very efficient.

[justinthehansen]

I dont think you understand, the SQL cant accept a zip file.

I have an array of numbers i need to save somehow on the SQL server. its a large array, (unknown at this point) but large enough I don't want to create hundreds of columns for each entry.

has anyone compressed an array of numbers like this so that they could save them in the fashion I have? I'm looking for examples and experienced SQL people.

thanks,

[justinthehansen]

not really, I'musing nvarchar so that i have the full 0-255 ascii range.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-serve...

so 4000 char max.

sorry for the confusion

yes, I could flatten to single, in the example VI i broke my 32 into 4 x 8byte words that i converted to ascii.

i dont know if my compression ratio would be any better if i just stripped the number to single before i converted to ascii..

[wiebe@CARYA]

---

@justinthehansen wrote:

I dont think you understand, the SQL cant accept a zip file.

---

SQL Server accepts files. But all SQL databases accept strings, and a .zip file is a string. So you can store deflated data (I never mentioned .zip as a solution, it would just be overhead) in an SQL string.

---

@justinthehansen wrote:

I have an array of numbers i need to save somehow on the SQL server. its a large array, (unknown at this point) but large enough I don't want to create hundreds of columns for each entry.

---

So convert and optionally compress the array to string and store the string.

---

@justinthehansen wrote:

has anyone compressed an array of numbers like this so that they could save them in the fashion I have?

---

Again, yes.

---

@justinthehansen wrote:

I'm looking for examples and experienced SQL people.

---

...

 

[wiebe@CARYA]

---

@justinthehansen wrote:

i dont know if my compression ratio would be any better if i just stripped the number to single before i converted to ascii..

---

Of coarse that won't help!

What was suggested is that you store binary data. Compressed or not, singles being 4 bytes, doubles 8 bytes will likely result in half the data (exactly half when uncompressed).

The point was not to convert to ASCII.

[justinthehansen]

ok, so I have an SQL Database, not a server. I have columns in a table I can put data in.

my data is an array of unknown length. could be greater than 4000 characters.

What is the best method for saving the data without creating a new column for each index?

ex:

Headers:  Test No.; Data 1, Data 2, .... Data N

my thought was to make a string out of the number but you are telling me to compress it to Binary? when i view it in the database, wont I just see a string of Binary numbers? that would be more space than just the original number, right? I cant manipulate the binary data in MS access so why would i store it that way? I view it as ASCII so how would i save it as Binary?

again, Im not 100% sure how to use this database, Im just trying to find a solution.

[Zwired1]

In my mind this is analogous to the "saving images to a database" question that comes up periodically.   Two schools of thought in that context: save the image (zip in your case) as a blob or save a link to the file.  In my experience, the vast majority of the opinions favor the latter option.   Is that (save a link to the data rather than the data) an option in your case?