I have seen a couple of questions about this subject already but I am searching for some insight around the performance variations between both of these techniques.

For instance, allows say I'm recording a log of occasions that will enter into the machine having a dictionary group of key/value pairs for that specific event. I'll record an entry within an Occasions table using the base data however I want a method to also link the extra key/value data. I'll don't know what types of Secrets or Values will be so any kind of predefined enum table appears unthinkable.

The wedding data is going to be constantly streaming in so place occasions is equally as essential as query occasions.

After I query for specific occasions I'll be with a couple fields around the Event in addition to data in the key/value data. For that XML way I'd simply employ a Characteristics.is available('xpath') statement included in the where clause to filter the records.

The stabilized way is always to make use of a Table with essentially Key and cost fields having a foreign connect to the big event record. This appears neat and simple however i be worried about the quantity of data that's involved.

You have three major choices for a 'flexible' storage mechanism.

  • XML fields are flexible but place you in the arena of blob storage, that is slow to question. I have seen queries against small data teams of 30,000 rows take a few minutes if this was digging stuff from the blobs with Xpath queries. This is actually the slowest option undoubtedly but it's flexible.

  • Key/value pairs are much faster, specifically if you put a clustered index around the event key. Which means that all characteristics for any single event is going to be physically saved together within the database, that will minimise the I/O. The approach is stiffer than XML but substantially faster. The best queries to report against it might involve pivoting the information (i.e. a table scan to create medium difficulty flattened result) joining to obtain individual fields is going to be much reduced.

  • The quickest approach is to possess a flat table with some user defined fields (Field1 - Field50) and hold some metadata concerning the items in the fields. This is actually the quickest to place and quickest and simplest to question, however the contents on the table are opaque to anything without accessibility metadata.

The issue I believe the important thingOrworth table approach is concerning the datatypes - if your value might be a datetime, or perhaps a string or perhaps a unicode string or perhaps an integer, then how can you define the column? This dilemma means the worthiness column needs to be considered a datatype which could contain all of the different kinds of data inside it which in turn begs the question of efficiency/easy querying. Alternatively, you've multiple posts of specific datatypes, however i think this really is a little cumbersome.

For any true flexible schema, I can not think about a much better option than XML. You are able to index XML posts.

This article off MSDN talks about XML storage in greater detail.

I'd assume the stabilized way could be faster for Place and Choose procedures, if perhaps because that is what any RDBMS could be enhanced for. The "quantity of data involved" part may be an problem too, but a far more solvable one - how lengthy do you want that data immediately available, are you able to archive it following a day, or perhaps a couple days, or 3 several weeks, etc? SQL Server are designed for a great deal.

The wedding data is going to be constantly streaming in so place occasions is equally as essential as query occasions.

Option 3: Should you genuinely have lots of data constantly streaming - produce a separate queue in shared memory, in-process sqlite, separate db table, as well as it's own server, to keep the incoming raw event &lifier characteristics, and also have another process (scheduled task, home windows service, etc) parse that queue into whatever preferred format updated for fast Chooses. Optimal input, optimal output, prepared to scale either in direction, everyone's happy.