将视图添加到供应商提供的数据库和性能
我们有一个供应商提供的数据库，到目前为止我已经能够避免对其进行任何数据库结构更改。我们很快将直接查询相关项目的表。为了提取我们需要的所有数据，我们需要创建一个包含多个联合的大 SQL 语句。
select ... from table1
union
select ... from table2
...
select ... from tableN
速度是该项目的首要任务。创建一个视图来执行连接然后只查询该视图（从而对供应商数据库进行更改）或者只从我们的应用程序运行 union 语句会更快吗？
我知道更改供应商数据库涉及的潜在问题，因此我正在寻找反馈。
We have a vendor delivered database that to this point I have been able to avoid making any database structure changes to. We will be soon directly querying the tables directly for a related project. In order to pull all of the data we need, we will need to create a big SQL statement with multiple unions.
select ... from table1
union
select ... from table2
...
select ... from tableN
Speed is the utmost priority for this project. Would it be quicker to create a view to do the joins then just query this view (and thus making changes to the vendor database) or just run the union statement from our application?
I know the potential problems involved in making changes to the vendor database so thus why I am looking for feedback.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当涉及到供应商数据库时，我会非常犹豫是否要更改任何现有表。添加诸如视图之类的东西似乎更安全一些，因为您是唯一会使用它的人。我对视图最大的担忧是，如果您从供应商那里获得了对数据库进行更改的更新，那么您可能会丢失视图。
我认为使用该视图不会带来任何性能提升，除非您使用索引视图。遗憾的是，您无法在使用联合创建的视图上创建索引（至少在 SQL Server 中）。
就我个人而言，当谈到将逻辑放入应用程序与将其放入数据库时，我倾向于数据库。根据我的经验，这些类型的更改更容易部署和维护。
如果我处于您的情况，我会继续创建视图，如果它会让您的生活更轻松。但同样，不要指望性能提升。
When it comes to vendor databases I would be very hesitant to make changes to any existing tables. Adding something like a view seems a little safer since you are the only one that will be using it. My biggest concern with the view would be if you ever got an update from your vendor that made changes to your DB and you might lose the view.
I don't think you'd see any performance gains from using the view, unless you used an indexed view. Unfortunately you can't create indexes on a view that is created using a union (at least in SQL Server).
Personally when it comes to putting logic in application vs putting it in the DB I lean towards DB. In my experience these types of changes are easier to deploy and maintain.
If i were in your situation I would go ahead create the view if it will make your life easier. But again, don't expect performance gains.
没有视图不会更快（开发时间除外）。
如果可行的话，使用 UNION ALL 会更快。 UNION 查找重复的记录并将其从最终结果中删除。如果您知道设计上的记录（例如每个表用于不同的客户端或每个表具有不同的日期范围）不能重复，则 UNION ALL 不会尝试区分结果集，因此运行速度更快。
No a view will not be quicker (except for development time).
What would be quicker is to use UNION ALL if it will work. UNION looks for duplicated records and removes them from the final result. If you know the records by design (such as each table is for a differnt clinet or each table has a differnt date range) cannot be duplicated, UNION ALL not try to DISTINCT the resultset and thus runs much faster.