My F # application has a very well-formed F # model, making full use of the F # type system (unions, records, tuples and primitive types). I am trying to find a better way to store these data types in a SQL server database.
Let them make the following assumptions:
The central object that I want to preserve is the Discrimination Union, called Task , which has about 30 different cases of joining, each case with completely different properties (which can be different DUs, records or tuples or primitive types), which makes use of a rectangular relational table very tiring to implement
I plan to constantly develop these models several times a week, while CI will configure the deployment of my application to production immediately after fixing. Again, using a regular table will make ALTER TABLE instructions slow down my development and deployment and add a significant portion of the cognitive overload that any new developers growing on this system will find difficult
After the evolution of the model, I should easily update my old models on the Internet using the background process or when retrieving from a database with simple downtime
I should be able to query these models at any depth, and I already have about a million lines, and this will continue to grow. The request must be fast, not more than one hundred milliseconds
I need to use SQL Server, since this application is a small part of a larger system, and I would like any database operations to take part in any current database transactions
Serialization of Task in JSON
This was my first attempt - to store everything as JSON, identify the requested values, store them in an indexed table using the new JSON functions for SQL Server 2016. The JSON functions in SQL Server are extremely fast, however, to index these queries, I either use constants + calculated + indexed columns or indexed views.
Pains:
It is very difficult to deploy models, especially if I want to develop all instances of type X that can appear at different depths for different cases of combining. There is no standardized language to describe these evolutions.
JSON makes no distinction between decimals / floats / numbers, and sometimes this is a problem to handle, and I need special formatting. A small problem, it doesn’t matter.
The query language is somewhat primitive at an arbitrary depth, and these queries are not indexed, so a new query almost always requires me to create a computed column or change my index view.
Adding a new indexed column to the indexed view is not an ONLINE operation and makes it simple and very difficult to automate in CI
Using PERSISTED COLUMNS in the same table sometimes leads to the fact that SQL Server does not use them when searching / selecting, but instead recalculates the values from scratch (because it does not accurately calculate the cost of this operation very well in the query planner)
Serialization of Task in XML
This is my current implementation.
I wrote my own custom XML serializers that make it easy for me to query the database using the Datatype XQuery and SQL Server xml columns
Evolution modeling is a breeze using the extremely powerful XSLT
Problems:
- Query even when adding all possible XML indexes is slow - takes about 5 seconds (in SQL Azure P6 instances)
- Combine this with slightly different queries for different versions of stored models, and this makes it even more expensive.
- Non-indexed XML functions are very slow, and building indexed tables / persistent columns is required forever, so I cannot use this.
I am very pleased with my XML solution - I just need to speed up the execution of my XML queries, and I think that at the moment I have come within the scope of what SQL Server has to offer.
Are there any other approaches that I skipped that the F # community was trying to maintain a very rich F # data model?
source share