In my project I have two separate DBs on the same server. The DBs are self-sufficient except for three columns in DB "B" that need to be accessed in DB "A".
在我的项目中，我在同一个服务器上有两个独立的DBs。除了需要在DB“A”中访问的DB“B”中的三列之外，DBs是自给自足的。
Are there performance considerations if I were to have a stored proc in A that accessed three columns from B directly?
如果要在a中存储一个直接从B访问三列的proc，是否需要考虑性能问题?
Currently, we run a nightly job to import the needed data from table B to table A, so that the stored proc isn't going out of the scope of A.
目前，我们运行一个夜间作业，从表B导入所需的数据到表a，这样存储的proc就不会超出a的范围。
Is that the best method?
这是最好的方法吗?
Are cross DB stored procs within best practices?
跨DB存储的procs是否在最佳实践中?
5 个解决方案
#1
4
To clarify the other posters comments.
澄清其他海报的评论。
There is no "direct" negative performance impact when using cross database access via stored procedures. Performance will be determined by the underlying architecture of the individual databases, i.e. indexes available, physical storage locations etc.
当通过存储过程使用跨数据库访问时，不存在“直接”的负面性能影响。性能将取决于各个数据库的基础架构，即可用索引、物理存储位置等。
This is actually quite a common practice and so long as you follow standard query tuning principals you will be just fine.
这实际上是一个非常常见的实践，只要您遵循标准的查询调优原则，您就会做得很好。
#2
5
There should be no problem since the databases are on the same server. Usually problems occur when you do this with linked servers and you could run into network latency
由于数据库在同一台服务器上，所以应该没有问题。当您使用链接服务器执行此操作时，通常会出现问题，您可能会遇到网络延迟
#3
2
It all depends on the data you are referencing and whether or not indexes are set up to support this direct access. The best thing I can tell you is to create the query, run it, and see if the performance is good enough.
这完全取决于您正在引用的数据，以及是否设置索引来支持这种直接访问。我能告诉您的最好的事情是创建查询，运行它，看看性能是否足够好。
If the performance is not satisfactory then run the query again and have your management tool generate a execution plan so you can identify the bottleneck.
如果性能不满意，那么再次运行查询，并让您的管理工具生成执行计划，以便您能够识别瓶颈。
#4
2
Yes, what you're currently doing - i.e. replication - is the "correct" thing to do.
是的，你现在正在做的——也就是复制——是正确的。
When referencing data in another database, you can't use referential integrity, data constraints and lots of the other good stuff that make an RDBMS a good tool to use.
当在另一个数据库中引用数据时，您不能使用引用完整性、数据约束以及使RDBMS成为良好工具的许多其他好东西。
Accessing the other database direcly ties the databases together - they MUST exist on the same server, for all time. You may run into badwidth issues using linked servers and executing the proc on demand.
直接访问其他数据库将数据库连接在一起——它们必须一直存在于同一台服务器上。您可能会在使用链接服务器和按需执行proc时遇到badwidth问题。
Replication gives you far more flexibility.
复制为您提供了更大的灵活性。
#5
1
We do this all the time. As long as they are on the same server there is no problem. If you have a requirement that the data must be in table a on database a before it can go into table B on database b, you will need to write a trigger to check as foreign key relationships can't be set across databases.
我们一直这么做。只要它们在同一台服务器上，就没有问题。如果您有一个需求，即数据必须在数据库a上的表a中，然后才能进入数据库B的表B，那么您将需要编写一个触发器来检查外键关系不能跨数据库设置。
#1
4
To clarify the other posters comments.
澄清其他海报的评论。
There is no "direct" negative performance impact when using cross database access via stored procedures. Performance will be determined by the underlying architecture of the individual databases, i.e. indexes available, physical storage locations etc.
当通过存储过程使用跨数据库访问时，不存在“直接”的负面性能影响。性能将取决于各个数据库的基础架构，即可用索引、物理存储位置等。
This is actually quite a common practice and so long as you follow standard query tuning principals you will be just fine.
这实际上是一个非常常见的实践，只要您遵循标准的查询调优原则，您就会做得很好。
#2
5
There should be no problem since the databases are on the same server. Usually problems occur when you do this with linked servers and you could run into network latency
由于数据库在同一台服务器上，所以应该没有问题。当您使用链接服务器执行此操作时，通常会出现问题，您可能会遇到网络延迟
#3
2
It all depends on the data you are referencing and whether or not indexes are set up to support this direct access. The best thing I can tell you is to create the query, run it, and see if the performance is good enough.
这完全取决于您正在引用的数据，以及是否设置索引来支持这种直接访问。我能告诉您的最好的事情是创建查询，运行它，看看性能是否足够好。
If the performance is not satisfactory then run the query again and have your management tool generate a execution plan so you can identify the bottleneck.
如果性能不满意，那么再次运行查询，并让您的管理工具生成执行计划，以便您能够识别瓶颈。
#4
2
Yes, what you're currently doing - i.e. replication - is the "correct" thing to do.
是的，你现在正在做的——也就是复制——是正确的。
When referencing data in another database, you can't use referential integrity, data constraints and lots of the other good stuff that make an RDBMS a good tool to use.
当在另一个数据库中引用数据时，您不能使用引用完整性、数据约束以及使RDBMS成为良好工具的许多其他好东西。
Accessing the other database direcly ties the databases together - they MUST exist on the same server, for all time. You may run into badwidth issues using linked servers and executing the proc on demand.
直接访问其他数据库将数据库连接在一起——它们必须一直存在于同一台服务器上。您可能会在使用链接服务器和按需执行proc时遇到badwidth问题。
Replication gives you far more flexibility.
复制为您提供了更大的灵活性。
#5
1
We do this all the time. As long as they are on the same server there is no problem. If you have a requirement that the data must be in table a on database a before it can go into table B on database b, you will need to write a trigger to check as foreign key relationships can't be set across databases.
我们一直这么做。只要它们在同一台服务器上，就没有问题。如果您有一个需求，即数据必须在数据库a上的表a中，然后才能进入数据库B的表B，那么您将需要编写一个触发器来检查外键关系不能跨数据库设置。