具有键/对表与XML字段和XPath的SQL Server性能 [英] SQL Server Performance with Key/Pair Table vs XML Field and XPath

问题描述

我已经看过这个主题的几个问题，但我想了解这两种技巧之间的效果差异。

I've seen a few questions on this topic already but I'm looking for some insight on the performance differences between these two techniques.

例如，假设我正在记录一系列事件的日志，这些事件将使用特定事件的键/值对的字典集合进入系统。我将使用基本数据在事件表中记录条目，但是我需要一种方法来链接附加的键/值数据。我永远不会知道什么类型的键或值将进来，所以任何类型的预定义枚举表似乎是问题。

For example, lets say I am recording a log of events which will come into the system with a dictionary set of key/value pairs for the specific event. I will record an entry in an Events table with the base data but then I need a way to also link the additional key/value data. I will never know what kinds of Keys or Values will come in so any sort of predefined enum table seems out of the question.

此事件数据将不断流入，因此

This event data will be constantly streaming in so insert times is just as important as query times.

当我查询特定事件时，我将使用Event上的一些字段以及键/值数据中的数据。对于XML方式，我只需使用Attributes.exists（'xpath'）语句作为where子句的一部分来过滤记录。

When I query for specific events I will be using some fields on the Event as well as data from the key/value data. For the XML way I would simply use a Attributes.exists('xpath') statement as part of the where clause to filter the records.

标准化方式是请使用基本上具有Key和Value字段的表，并带有指向事件记录的外部链接。这似乎干净，简单，但我担心涉及的数据量。

The normalized way would be to use a Table with basically Key and Value fields with a foreign link to the Event record. This seems clean and simple but I worry about the amount of data that is involved.

推荐答案

表方法是关于数据类型 - 如果值可以是datetime，或字符串或unicode字符串或整数，那么如何定义列？这个困境意味着值列必须是可以包含其中所有不同类型的数据的数据类型，然后提出了查询效率/易用性的问题。或者，您有多列特定数据类型，但我认为这有点笨重。

The problem I think the key/value table approach is regarding the datatypes - if a value could be a datetime, or a string or a unicode string or an integer, then how do you define the column? This dilemma means the value column has to be a datatype which can contain all the different types of data in it which then begs the question of efficiency/ease of querying. Alternatively, you have multiple columns of specific datatypes, but I think this is a bit clunky.

对于一个真正灵活的模式，我不能想到一个更好的选择比XML。您可以索引XML列。

For a true flexible schema, I can't think of a better option than XML. You can index XML columns.

这篇文章关于MSDN 更详细地讨论了XML存储。

This article off MSDN discusses XML storage in more detail.

这篇关于具有键/对表与XML字段和XPath的SQL Server性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！