如何从一个完整的SQL查询来像一个NoSQL的去吗？ [英] How to go from a full SQL querying to something like a NoSQL?

问题描述

在我的过程中我有这个SQL查询需要的总执行时间的10％-20％。该SQL查询确实在我的数据库中的一个过滤器，并加载PricingGrid对象的列表。 所以我想，以提高这些性能。 到目前为止，我猜2解决方案：

使用一个NoSQL的解决方案，AFAIK这些都为提高阅读过程中具有良好的解决方案。

• 但是迁移似乎很难，需要大量的工作（如导入从SQL Server中的数据定期的NoSQL）
• 在我没有任何知识，我甚至不知道我应该使用哪一个（第一个我用的是Ravendb，因为我跟ayende并且它由.NET社区完成）。
• 我可能有一些东西在我的模型改变，使我的目标确定为一个NoSQL数据库

加载在内存中的所有我的PricingGrid对象（在一个静态的IEnumerable）

• 这可能是一个问题，当我的服务器将没有足够的内存来加载的一切
• 我会推倒重来（索引...）由NoSQL的供应商创造

我觉得我不是第一个知道这一点，所以这将是最好的解决办法？是否有任何工具，可以帮助我？

.NET 3.5，SQL Server 2005中，视窗Server 2005的

解决方案

阿萨夫提供了巨大的信息在问候的NoSQL的使用，当它是最合适的。鉴于你主要关心的是性能，我倾向于同意他的观点 - 它会占用你太多的时间和精力，采取全新的（和非常不同的）数据持久化平台会比欺骗你的SQL Server群集。这就是说，我的回答是主要是为了解决你的问题如何做的部分。

寻址的误解：

1. 非规范化的数据 - 你并不需要手动非规范化现有数据。这会为你做当它被移植过来。最重要的是，你需要简单地想想你的数据以不同的方式 - 根聚集，实体和值类型，等等。

2. 并发/交易 - 交易是可能的两个蒙戈和掠夺，他们只是以不同的方式进行。一个固有的方式乌鸦做到这一点是通过使用工作单位，其 RavenSession 目标模式的ORM一样。是的，你的数据验证需要在code做，但你已经应该无论如何做它。根据我的经验，这是一个被过分夸大CON。

如何：

1. 在主服务器上安装乌鸦或蒙戈，运行它作为一项服务。

2. 创建或扩展使用您打算端口数据库中的现有应用程序。此应用程序需要你的SQL数据库提供了持久性的所有模型类/库。

   一个。在你的数据层，你可能有一个仓库类的地方。提取接口构成本，并用它来建立另一个仓库类的乌鸦/蒙戈持久性。这两个数据库的拥有充足良好的文档使用他们的API来推动在文档图/拉/更新的变化。这是pretty的该死的简单。

   乙。载入你的SQL数据到C＃对象在内存中。回力的顶级对象（只是实体）并装载自己内心的藏品和相关数据在内存中。你的资料库可能已经这样做（例如取一个订单对象时，不仅确保其性能，而且像产品关联集合被加载到内存中。

   ℃。实例化你的乌鸦/蒙戈存储库和数据推给它。主要实体成为顶级文档或根集合连载JSON和嵌套在自己的藏品的数据。保存更改并关闭该存储库。注：为您的数据认为必要时您可以向下打破这种一步进入尽可能多的小片

3. 在您的数据迁移，玩弄它，保证你满意。您可能需要修改应用程序模型稍微调整它们保存到乌鸦/蒙戈的方式 - 例如，您可能希望使双方订单和产品顶层文件，只需使用参考值（很像RDBMS系统的关系）。当心虽然这里，因为这样做排序的违背委托人和业绩背后的NoSQL和现在一样，你必须点击两次数据库获取订单和项目。

4. 如果满意，碎片/在你的剩余可用服务器框中复制您的蒙戈/乌黑的服务器。

显然有吨的小细节我没有解释，但就是一般的过程，大部分是依赖于已经消耗了数据库，并可能会非常棘手，如果超过一个应用程序/系统会谈它的应用程序。

最后，只是为了重申阿萨夫说......学习尽可能多地了解NoSQL的和最佳的使用情况。这是一个了不起的工具，而不是金色的解决方案对所有数据的持久化。你的情况尝试真正找到在当前的解决方案中的瓶颈，看看他们都是可以解决的。由于我的系统的人之一说，为技术而技术的缘故是胡说八道

In one of my process I have this SQL query that take 10-20% of the total execution time. This SQL query does a filter on my Database, and load a list of PricingGrid object. So I want to improve these performance. So far I guessed 2 solutions :

Use a NoSQL solution, AFAIK these are good solutions for improving reading process.

• But the migration seems hard and needs a lot of work (like import the data from sql server to nosql in a regular basis)
• I don't have any knowledge , I even don't know which one I should use (the first I'd use is Ravendb because I follow ayende and it's done by the .net community).
• I might have some stuff to change in my model to make my object ok for a nosql database

Load all my PricingGrid object in memory (in a static IEnumerable)

• This might be a problem when my server won't have enough memory to load everything
• I might reinvent the wheel (indexes...) invented by the NoSQL providers

I think I'm not the first one wondering this, so what would be the best solution ? Is there any tools that could help me ?

.net 3.5, SQL Server 2005, windows server 2005

解决方案

Asaf has provided great information in regards to the usage of NoSQL and when it is most appropriate. Given that your main concern was performance, I would tend to agree with his opinion - it would take you much more time and effort to adopt a completely new (and very different) data persistence platform than it would to trick out your SQL Server cluster. That said, my answer is mainly to address the "how" part of your question.

Addressing misunderstandings:

1. Denormalizing Data - You do not need to manually denormalize your existing data. This will be done for you when it is migrated over. More than anything you need to simply think about your data in a different fashion - root aggregates, entity and value types, etc.

2. Concurrency/Transactions - Transactions are possible in both Mongo and Raven, they are simply done in a different fashion. One of the inherent ways Raven does this is by using an ORM-like "unit of work" pattern with its RavenSession objects. Yes, your data validation needs to be done in code, but you already should be doing it there anyway. In my experience this is an over-hyped con.

How:

1. Install Raven or Mongo on a primary server, run it as a service.

2. Create or extend an existing application that uses the database you intend to port. This application needs all the model classes/libraries that your SQL database provides persistence for.

   a. In your "data layer" you likely have a repository class somewhere. Extract an interface form this, and use it to build another repository class for your Raven/Mongo persistence. Both DB's have plenty good documentation for using their APIs to push/pull/update changes in the document graphs. It's pretty damn simple.

   b. Load your SQL data into C# objects in memory. Pull back your top-level objects (just the entities) and load their inner collections and related data in memory. Your repository is probably already doing this (ex. when fetching an Order object, ensure not only its properties but associated collections like Items are loaded in memory.

   c. Instantiate your Raven/Mongo repository and push the data to it. Primary entities become "top level documents" or "root aggregates" serialized in JSON, and their collections' data nested within. Save changes and close the repository. Note: You may break this step down into as many little pieces as your data deems necessary.

3. Once your data is migrated, play around with it and ensure you are satisfied. You may want to modify your application Models a little to adjust the way they are persisted to Raven/Mongo - for instance you may want to make both Orders and Items top-level documents and simply use reference values (much like relationships in RDBMS systems). Watch out here though, as doing so sort-of goes against the principal and performance behind NoSQL as now you have to tap the DB twice to get the Order and the Items.

4. If satisfied, shard/replicate your mongo/raven servers across your remaining available server boxes.

Obviously there are tons of little details I did not explain, but that is the general process, and much of it depends on the applications already consuming the database and may be tricky if more than one app/system talks to it.

Lastly, just to reiterate what Asaf said... learn as much as you can about NoSQL and its best use-cases. It is an amazing tool, but not golden solution for all data persistence. In your case try to really find the bottlenecks in your current solution and see if they are solvable. As one of my systems guys says, "technology for technology's sake is bullshit"

这篇关于如何从一个完整的SQL查询来像一个NoSQL的去吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！