Menu
We are developing a customized caching solution that will use a SQL Server database to store cached objects. The hosting environment of the application does not provide an "in-memory" cache such as memcached or app fabric so we must use a SQL Server database.
While most of the cached objects will be simple types (int, string, dates, etc) we will need to also store more complex types such as DataSets, DataTables, generic collections and custom classes.
I have very little experience with the .NET's native serialization and deserialization but I figure we will have to serialize the objects into some form (binary, xml, JSON, etc) to store it in the database and then deserialize it when we pull it out of the database. I would like to have some expert opinions on what the the "some form" should be.
We are using JSON.NET to serialize data into JSON for various AJAX requests. My initial thought was to serialize the cached data into JSON to store it in the database. However, I wanted to get a few opinions as to what would be best for performance and data integrity.
676
A nice approach to do this would be to use JSON.NET to serialize and deserialize objects to and from a json string and store them them in an nvarchar(MAX) column in the database. You can also serialize the object to xml and then store in an xml column of sql server.
The only available serializable-SQL commands are INSERT INTO x () , DELETE FROM x WHERE id=y . Where we can only change x and y . At first create all necessary tables on the server once. Keep a hash table on the server that maps each table to a number.
All three of the serialization options you mentioned (binary, json or XML) are valid choices for a serialization format. There are many other serialization formats but the three you mentioned are the most common. As to choosing between the three, here are some of the considerations:
If you store your data in a binary format in the database, it is not human readable if if you ever want to look at it via using Sql Server Management Studio or via a text editor. You would have to write some sort of deserialization tool if you wanted to manually peruse the data.
Binary format will likely result in serialize objects have the smallest size, followed by json, with XML being the largest. As far as the actual size differences, that will vary with your data structures.
As far as performance, binary serialization may be faster than json or XML. However, you would have to benchmark this with your data to see what the differences are.
I think there are excellent .net libraries and BCL support for all three of the format types, so any choice should be doable.
So your choice would depend upon which factors are most important to you: CPU utilization, disk storage space, human readability, and/or personal preference.
We have used json extensively for serialization of our objects for storage in a database , using JSON.Net and we like it a lot. It is handy sometimes to manually view the data via SSMS, and json is significantly more compact for our data than XML.
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
