难道我使用的Azure表的存储或SQL Azure中我们CQRS读取系统？ [英] Do I use Azure Table Storage or SQL Azure for our CQRS Read System?
问题描述
我们即将其目标是，以大大提高我们的读取性能，从而实现公司内部CQRS系统的阅读部分。目前，我们读通过这是违背标准化数据的LINQ到SQL查询的Web服务进行的，涉及从SQL Azure数据库某种程度的反序列化。
We are about to implement the Read portion of our CQRS system in-house with the goal being to vastly improve our read performance. Currently our reads are conducted through a web service which runs a Linq-to-SQL query against normalised data, involving some degree of deserialization from an SQL Azure database.
我们的数据的简化结构是：
The simplified structure of our data is:
- 用户
- 对话（信息复制到同一收件人分组）
- 消息
- 收件人（组用户）
欲这个移动到一个非规范化的状态，这样，当一个用户请求查看它从读出的消息的饲料无论是：
I want to move this into a denormalized state, so that when a user requests to see a feed of messages it reads from EITHER:
在Azure的表的存储举行的非规范化重新presentation
- 用户名作为PartitionKey
- 的conversationId为RowKey
- 容易出现任何挥发性数据而改变存储为实体
- 在实体序列化为JSON的消息
- 的收件人的消息说，在实体序列化为JSON
- 与行的表存储（960KB） 这个规模有限的主要问题
- 此外，因为它们不是关键的组成部分的可变数据栏目有任何疑问将是缓慢
- UserID as the PartitionKey
- ConversationID as the RowKey
- Any volatile data prone to change stored as entities
- The messages serialized as JSON in an entity
- The recipients of said messages serialized as JSON in an entity
- The main problem with this the limited size of a row in Table Storage (960KB)
- Also any queries on the "volatile data" columns will be slow as they aren't part of the key
在Azure的表的存储召开规范化重新presentation
- 不同的表谈话的细节，消息和收件人
- 对于存储在会话表信息和收件人分区键。
- 栏;这遵循与上述相同的结构
- 获取周围的最大行大小问题
- 而将归国家减少非规范化表的性能提升？
或
非标准化的再$ P $在SQL Azure中举行psentation
- 用户名和放大器;的conversationId举行一个复合主键
- 任何挥发性数据容易改变存储在单独的列
- 在列序列化为JSON的消息
- 的收件人的消息说，在列序列化为JSON
- 索引最大的灵活性和非规范化的数据结构
- 比表的存储查询的性能慢得多
什么我问的是，是否有人有实施表的存储或SQL Azure中的非规范化的结构的任何经验，你会选择哪个？还是有更好的方法我已经错过了？
What I'm asking is whether anyone has any experience implementing a denormalized structure in Table Storage or SQL Azure, which would you choose? Or is there a better approach I've missed?
我的直觉说，在表的存储标准化的（至少在一定程度上）的数据将是要走的路;然而，我担心它会降低性能提升，以获取所有数据，用户要进行3查询。
My gut says the normalized (At least to some extent) data in Table Storage would be the way to go; however I am worried it will reduce the performance gains to conduct 3 queries in order to grab all the data for a user.
推荐答案
您的考虑Azure的表主要驱动力是大大提高读取性能，并在方案中使用SQL Azure是慢得多，按照在你的最后点在SQL Azure中举行的非规范化重新presentation。我个人觉得这几个原因非常令人惊讶，也请对这个说法是怎么做详细的分析。我的默认位置将是在大多数情况下，SQL Azure的会快很多。
Your primary driver for considering Azure Tables is to vastly improve read performance, and in your scenario using SQL Azure is "much slower" according to your last point under "A denormalized representation held in SQL Azure". I personally find this very surprising for a few reasons and would ask for detailed analysis on how this claim was made. My default position would be that under most instances, SQL Azure would be much faster.
下面是一些原因，我索赔的怀疑：
Here are some reasons for my skepticism of the claim:
- SQL Azure中使用本地/高效TDS协议返回数据; Azure的表使用JSON格式，这是更详细
- 加入/过滤器在SQL Azure中会非常快，只要你使用的是主键或在SQL Azure中的索引; Azure的表没有索引和联接必须进行客户端
- 在的（每次1000条记录）通过Azure的表返回的记录数的限制意味着你需要实现多次往返获取多条记录
虽然可以在Azure中的表假索引通过创建持有定制指数的附加表，你自己维护该索引，这将减缓你的操作，并可能创造孤儿的情况，如果你不小心的责任。
Although you can fake indexes in Azure Tables by creating additional tables that hold a custom-built index, you own the responsibility of maintaining that index, which will slow your operations and possibly create orphan scenarios if you are not careful.
最后但并非最不重要的，使用Azure的表通常使当你试图减少你的存储成本（它比SQL Azure的便宜），当你需要比SQL Azure中可以提供更多的存储空间（虽然你现在可以使用联邦感打破单一数据库的最大存储限制）。例如，如果你需要来存储一个十亿客户记录，使用Azure的表可能是有意义的。但是，使用Azure的表单独增加的速度是在我的脑海相当可疑。
Last but not least, using Azure Tables usually makes sense when you are trying to reduce your storage costs (it is cheaper than SQL Azure) and when you need more storage than what SQL Azure can offer (although you can now use Federations to break the single database maximum storage limitation). For example, if you need to store 1 billion customer records, using Azure Table may make sense. But using Azure Tables for increase speed alone is rather suspicious in my mind.
如果我是你的话我会质疑这种说法非常努力，并确保你对工作人员的专家SQL开发技能，能够证明完全改变你的架构之前，你已经达到内在的SQL Server / SQL Azure中的性能瓶颈。
If I were in your shoes I would question that claim very hard and make sure you have expert SQL development skills on staff that can demonstrate you are reaching performance bottlenecks inherent of SQL Server/SQL Azure before changing your architecture entirely.
另外，我将定义你的性能目标。你在看快100倍的访问时间？你有没有考虑缓存呢？您是否使用正确的索引在数据库中？
In addition, I would define what your performance objectives are. Are you looking at 100x faster access times? Did you consider caching instead? Are you using indexing properly in your database?
我的2美分...：）
这篇关于难道我使用的Azure表的存储或SQL Azure中我们CQRS读取系统？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
