My F# application has a very well-shaped F# model, taking full advantage of the F# type system (unions, records, tuples and primitive types). I'm trying to figure out the best way to persist these data types to a SQL-Server DB.
Let's make the following assumptions:
The central entity I want to persist is a Discriminated Union, called
Task, which has about 30 different union cases, each case with wildly different properties (which may be other DUs, records or tuples or primitive types), that makes using a rectangular-relational table very tedious to implementI expect to be constantly evolving these models, multiple times a week, with CI set to deploy my app to production immediately upon commit. Again, using a regular table will make
ALTER TABLEstatements slow down my development and deployments and adds a considerable amount of cognitive overload that any new developers ramping up on this system will find challengingUpon model evolution, I should be easily able to upgrade my old models online using a background process, or upon fetch from the DB, with close-to-0 downtime
I should be able to query into these models at arbitrary depth, and I already have close to a million rows to deal with, and this will keep growing. Querying should be fast, in the order of hundred milliseconds at most
I need to use SQL Server, as this application is a small part of a larger system, and I would like any DB operations to participate in any ongoing DB transactions
Serializing Task to JSON
This was my first attempt -- store everything as JSON, identify queryable values, store them in an indexed table, using SQL Server 2016's new JSON functions. JSON functions in SQL Server are extremely fast, however indexing these queries requires that I either use persisted+computed+indexed columns or indexed views.
Pains:
Very hard to evolve models, especially if I want to evolve all instances of type X, which may appear at various depths for different union cases. There is no standardized language to spec out these evolutions
JSON doesn't differentiate between decimal / floats / numbers, and this is sometimes a pain to deal with, and I need custom formatters. Small issue, no big deal.
Query language is somewhat primitive at an arbitrary depth, and these queries aren't indexed, so a new query will almost always require that I create a computed column or alter my indexed view.
Adding a new indexed column to the indexed view is not an ONLINE operation and results in downtime, and is very difficult to automate in CI
Using PERSISTED COLUMNS within the same table sometimes leads to SQL Server not really using these while searching / selecting, and instead re-calculating the values from scratch (because it doesn't accurately calculate the cost of this operation very well in its query planner)
Serializing Task to XML
This is my current implementation.
I've written my own custom XML serializers that make it very easy for me to query into the DB using XQuery and SQL Server's xml datatype columns
Model evolutions are a breeze, using extremely-powerful XSLT
Problems:
- Query, even with all possible XML indices added is slow -- takes about 5 seconds (in Azure P6 SQL instances)
- Couple this with slightly different queries for different persisted model versions, and it makes it even more expensive
- Non-indexed XML Functions are very slow, and it takes forever to build out indexed tables / persisted columns, so I can't really use this.
I'm quite happy with my XML solution -- I just need a way to make my XML queries faster, and I think at this point, I've hit the limits of what SQL Server can offer.
Are there any other approaches that I've missed that the F# community has tried to be able to persist a very rich F# data model?