两个不同数据库中的两个表之间的联接有什么问题？ [英] What are the problems with a join between two tables in two different databases?

问题描述

我对您对连接来自不同数据库的两个或多个表的缺陷感到感兴趣。

假设表 Table1 位于 DatabaseA 数据库和表2 位于 DatabaseB 。假设我有一个视图，在 DatabaseA 中，从 Table1 中取出一些数据， DatabaseA '中的表。

这个视图用于将数据推送到另一个数据库，让我们称之为一个数据库，unimaginatevely， DatabaseC p>

如果我需要来自 Table2 的一些数据，我的直觉是直接加入 Table2 在这个视图中，排序如下 table1 inner join DatabaseB..table2 on [some columns]

这样做相当简单快捷，但我在头上有一个。叨的声音，不断告诉我不要这样做。我的担心是不能跟踪所有的对象，取决于 Table2 ，所以如果我改变的东西，我必须非常小心，记得无处不在，我使用这个表。因此，这种视图（和两个数据库）打破SRP，因为此视图可以从两个不同的操作（在两个不同的数据库上执行：更改 Table1 或更改 Table2 ）

我对你的意见很感兴趣。这是一个好主意还是坏主意？这种方法的问题是什么（性能明智，维护性明智等），如果你有一个真实的世界经验，这种方法是一个大错误，或者是一个救生员。

PS：我在google和SO上搜索过这个主题，但找不到任何与此相关的内容。我将很乐意从SO用户那里获得减票，重复的问题和其他谴责，只是对这个问题有不同的看法。

PPS：我使用SQL Server

谢谢，希望我清楚：）

解决方案

p>如果他们在同一台服务器上，从单独的数据库中拉出没有真正的问题。事实上，你可能想要分开他们有很好的理由。例如，如果您有从文件导入的事务表和查找表的组合。事务数据需要完全恢复和频繁的事务日志备份才能正确恢复，查找数据不会，并且可以从简单恢复模式下的数据库中受益。

我们有许多不同的数据库，我们的应用程序使用，我们在查询中交叉数据库。只要正确完成索引，就没有明显的性能差异。最大的潜在问题是数据完整性，因为您无法在数据库之间设置外键。如果需要，可以在触发器中处理。

现在，当数据库在不同的服务器上时，可能会出现性能问题，并且获得数据更复杂。 / p>

I am interested in your thoughts about the the pitfalls of joining two or more tables from different databases. I'll try to give an example.

Suppose table Table1 is located in DatabaseA database and Table2 is located in DatabaseB . Let's say i have a view, in DatabaseA that pulls out some data from Table1, and some other tables in DatabaseA'.

This view is used to push data to another database, let's call this one, unimaginatevely, DatabaseC.

If i need some data from Table2, my instinct is to join directly Table2 in this view, sort of like this table1 inner join DatabaseB..table2 on [some columns]

Doing this is pretty simple and quick, but i have a nagging voice in my head that keeps telling me not to do this. My worries are about not being able to track down all the objects depending on Table2, so if I change something there, I have to be very carefull and remember everywhere i use this table. So, sort of like breaking SRP for this view (and two databases), because this view can change from two different actions (performed on two different databases: Changing Table1 or changing Table2)

I am interested in your opinions. Is this a good or bad idea? What would be the problems with this approach (performance wise, maintainence wise and so on) and if you have a real world experience where this approach either was a big mistake or was a life saver for you.

P.S: I've searched this topic on google and SO, but could not find anything related to this. I will gladly take the minus votes, duplicate questions and other 'reprimands' from SO users just to have a different view on this problem.

P.P.S: I am using SQL Server 2005.

Thank you and hope i made myself clear:)

解决方案

If they are on the same server, there is no real problem pulling from separate database. In fact, you may want to separate them for good reasons. For instance if you have a combination of transactional tables and lookup tables that are imported from files. The transactional data needs full recovery and frequent transactional log backups to be able to properly restore, the lookup data does not and can benefit from being in a database in simple recovery mode.

We have many different databases our applications use and we cross databases in queries all the time. As long as the indexing is done properly, there has been no noticable performance difference. The biggest potential issue is for data integrity as you can't set up foreign keys across databases. This can be handled in triggers if need be though.

Now when the databases are on different servers, there can be a performance problem and getting the data is more complicated.

这篇关于两个不同数据库中的两个表之间的联接有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！