数据库性能解决方案-“视图缓存” - 这是个好主意吗？
首先介绍一点背景信息：
我想说我有良好的 SQL Server 经验，但我是一名开发人员，而不是 DBA。我当前的任务是提高数据库的性能，该数据库的构建非常依赖视图。该应用程序充满了内联 sql，并且使用 Tuning Advisor 仅建议几个缺失的索引，这看起来很合理。
我觉得重新设计数据库设计，以便保留这些特定视图（很多 CASE WHENS）创建的数据，因为考虑到这里的预算和时间范围，硬数据的工作量太大了。完全重写这些巨大的视图以及依赖它们的所有代码似乎也是不可能的。
我提出的解决方案：
测试表明，如果我对视图数据执行 SELECT INTO 以将其保留在永久表中，然后用此表替换对视图的引用，则查询时间会下降到 44%使用视图时它们是什么。
由于数据是由蜘蛛进程在一夜之间刷新的，我想我可以每天删除并重新创建该表，然后对查询进行少量修改以使用该视图。
有良好 DBA 经验的人可以给我一个意见，看看这是否是一个好的 / *&?!!糟糕的主意。如果是后者，是否有更好的方法来解决这个问题？
谢谢。
A little context first:
I would say I have good SQL server experience, but am a developer, not a DBA. My current task is to improve on the performance of a database, which has been built with a very heavy reliance on views. The application is peppered with inline sql, and using Tuning Advisor only suggests a couple of missing indexes, which look reasonable.
I feel that reworking the database design so that the data created by these particular views (lots of CASE WHENS) is persisted as hard data is too much work given the budget and time scales here. A full rewrite of these enormous views and all of the code that relies on them also appears to be out of the question.
My proposed solution:
Testing has shown that if I do a SELECT INTO with the view data to persist it in permenant table, and then replace references to the view with this table, query times go down to 44% of what they were when using the view.
Since the data is refreshed by a spider process overnight, I think I can just drop and recreate this table on a daily basis, and then make minor modifications to the queries to use this view.
Can someone with good DBA experience give me an opinion on whether that is a good / *&?!! awful idea. If the latter, is there a better way to approach this?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您说：“...重新设计数据库设计，以便保留这些特定视图创建的数据...因为考虑到此处的预算和时间范围，硬数据的工作量太大。”但这正是您所建议做的。只是您使用视图本身，而不是使代码成为函数或存储过程。
无论如何，我的观点是，如果您投入一些精力来使其稳健（即，您确保如果蜘蛛运行，持久数据始终会刷新，并且您永远不会在蜘蛛结束之前运行 select-into）解决方案就可以了。
让我担心的是，这是一个黑客 - 无论多么聪明 - 因此无论谁继承你的解决方案都可能会发现很难理解原因和方式。看看您是否可以通过评论或单独的文档提供良好的解释。
You say: "...reworking the database design so that the data created by these particular views ... is persisted as hard data is too much work given the budget and time scales here." and yet this is exactly what you are proposing to do. It's just that you use the views themselves instead of making the code a function or a stored procedure.
Anyway, my opinion is that if you invest a bit of effort in making this robust (i.e. you ensure that if the spider runs, the persisted data always get refreshed, and you never run the select-into before the end of the spidering) your solution is ok.
What would concern me is that this is a hack - however brilliant - so whoever inherits your solution may find it difficult to understand the why and how. See if you can provide a good explanation either by comments or a seperate document.