Data Table Serialization
I have found a need to save the results of a query as an ADO.Net DataTable. Granted, the results can easily be saved into a database table with a foreign key to select the specific rows of a set. But I have a need where the requirement is to retrieve the whole set, or nothing from it. Moreover, I don’t need to filter or search based on the contents of the DataTable. In fact, you could consider the entire set of rows in the DataTable as being the Data. It would be the equivalent of saving or opening a spreadsheet. You don’t worry about the contents when you are retrieving the spreadsheet…you simply open the whole thing.
Trying to scale and get performance on this I can do a number of things.
- Serialize the DataTable as XML
- Serialize the DataTable as JSON
- Serialize the DataTable as binary
- Serialize the DataTable as Comma Separated Values (CSV)
The size of the DataTable varies with each type. They are from largest file size to smallest file size, XML, JSON, CSV, Binary. CSV is much smaller than XML or JSON.
I had to write my own serialization routine for a data table to convert it into CSV form. I used reflection to get the column names and place that into the first row. Again, using reflection, I create a second row with the data types. Finally, I create a CSV entry for each row separating each value with “,”.
Deserialization is the opposite. I first generate an empty data table defined with the values in the first two rows. Then I add rows for each row in the CSV list.
Using simple GZip compression built into Dot Net you can take a DataTable serialized as CSV and shrink it down really small. So small, in fact, that it fits nicely into many current NoSQL data storage platforms. Some don’t require compression, and work fine if the storage is local. Others gain performance if going over a slower network connection.
Do you have serialization techniques you prefer for Data Tables, or other object types for that matter? Share you experience below, or drop me an Email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Learning SSAS Part VII – Attribute Relationships
In this article I am going to talk about potentially the single most important thing for SSAS cubes and their performance. Attribute relationships tend to be the critical piece and unfortunately also one of the most ignored aspects of any cube implementation. We will talk about how to implement attribute relationships correctly and we will see what are the things at stake if you do not want any attribute relationships in your cubes.
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
Backup and GZip Databases
This SP will backup and then compress all databases except for the ones listed specifically in the Admin..SkipBackup table. T… (read more)