使用复杂的 SQL Server 数据库模式
我有一个数据库，我必须保持与 SQL Server 2005 的兼容性，并且我一直在考虑降低复杂性和处理性能问题的方法。
我的数据库和大多数其他数据库一样，充满了数据，数据很多，里面有很多查询。我有许多存储过程，它们一直在不断发展（一段时间以来）以满足业务需求。这基本上没问题，但我遇到了性能问题，而且我的查询管理变得越来越复杂。
乍一看，我不认为我的数据模型有什么问题，它没有荒谬地规范化（我们已经对某些东西进行了非规范化），但我发现自己无法编写和运行那些极快的查询来为我的 Web 界面提供支持AJAX 查询是因为所有的约束似乎都随意地存在于各处。
所以，我考虑过，我想我想以环形方式组织我的数据库。让我解释一下。
基本上，在最内环，您会发现最专业的 一组数据。这些表是完全非规范化的并且已被 通过聚合外环的数据来构建，以确保特定的 查询运行得非常快。
最外层的环理想情况下是“哑”的，基本上只是一个真正的环 放东西的地方不好。
外部和内部之间基本上是你的概念模型，这些 从其他环拉或推到内环，这是 您可以在其中清理数据并确保其正确。
数据只能从外环流向内环。
我不想使用触发器来保持不同环的一致性，而是我有一个服务和作业，它们定期监听、轮询和运行，以确保最终的一致性，全面。
现在，这是我寻求建议的地方，并希望从经验丰富的数据库人员那里得到一些意见。我相信通过这种方式我可以从数据库中获得更多信息。它将使我能够解决不同阶段的复杂性和性能问题。也许我正在做的事情有一个通用的名称，或者也许这就是 NoSQL 运动的全部内容，但我真的不知道，这个想法对我有一些吸引力，但如果我在那里，我'我想在我犯错误之前听到它......
I have a database, I have to maintain compatibility with SQL Server 2005 and I've been thinking about ways to reduce complexity and deal with performance issues.
My database is like most other's and filled with data, it's a lot of data and there's a lot of queries in there to. I have many stored procedures that have been evolving (for some time now) to meet business demands. And this is mostly fine, but I'm running into performance problems and my queries are becoming increasingly complex to manage.
At a first glance, I don't think there's anything wrong with my data model, it's not absurdly normalized (we already denormalize some things), yet I find myself not being able to write and run those blazing fast queries for powering my web interface AJAX queries because all the constraints that seems to somewhat haphazardly exist here and there.
So, I've thought about it, and I think I want to organize my database in rings. Let me explain.
Basically, in the inner most ring, you'll find the most specialized
set of data. These tables are completely denormalized and have been
built by aggregating data from outer rings to make sure specific
queries run really fast.The outer most ring is ideally "dumb" and is basically just a really
bad place to put things.Between outer and inner is basically your conceptual model, these
pull from the other rings or push to the inner rings and this is
where you clean your data and make sure that it's correct.Data can only flow from an outer ring to an inner ring.
I don't want to use triggers to keep the different rings consistent, instead I have a services and jobs that, listen, poll and run at regular intervals to ensure eventual consistency, cross the board.
Now, this is where I ask for advice and hope to get some input from experienced database people. It's my belief that I could get more out of my database this way. And it will allow me to address both complexity and performance issues at different stages. Maybe there's a common name for what I'm doing or maybe this is what the NoSQL movement is all about, but I don't know really, the idea has some appeal to me, but if I'm way out there, I'd like to hear about it before I make a mistake...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
虽然我基本上同意@TomTom的回答，但我会用不同的措辞：您基本上是自己开发了数据仓库（或具体而言，数据集市）的概念。买一本关于数据仓库的书是个好主意；参加有关该主题的研讨会或系列课程效果更好。显然，您已经对此进行了一些认真的思考，当您了解最佳实践和已开发的不同方法时，这将对您很有帮助。
Although I basically agree with @TomTom's answer, I would phrase it differently: you have essentially developed the concept of the data warehouse (or data mart, to be specific) on your own. Buying a book on data warehousing is a great idea; attending a seminar or series of classes on the topic is even better. You've obviously done some serious thinking about this already and that will serve you well when you learn about best practices and the different approaches that have been developed.
您需要一本数据库入门书。严重地。将其拆分为 OLTP 和 OLAP 部分 - 数据仓库就这样了。摆脱存储过程。然后意识到你的“大量 odata”可能是其他人的“数据笑话” - 我工作的系统应该扩展到大约 60 tb 数据（即 60.000） - 我们最初的硬件有 21.000 GB。
您的系统听起来就像将普通数据库 (OLTP) 与数据仓库混合在一起。把它们分开——这是行不通的。也在硬件中将它们分开。这是关于数据仓库的完全标准的书。
You need a beginner b ook into databases. Seriously. Split it into an OLTP and an OLAP part - a data warehouse is in order. Get rid of the stored procedures. Then realize that your "lot of odata" is likely other peoples "jokes of data" - I work on a sysstem supposed to scale to around 60tb of data (that is 60.000) - our initial hardware has 21.000 gigabyte.
Your system sonds like you mix up a normal database (OLTP) with a data warehouse. Split them - this wont work. Split them ALSO IN HARDWARE. This is total standard - geta book about data warehouses.