从不同服务器中的链接表进行 JOIN 时 MS Access 的性能？
如果我有一个 MS Access 数据库，其中包含来自两个不同数据库服务器的链接表（例如一个来自 SQL Server 数据库的表和一个来自 Oracle 数据库的表），并且我编写了一个连接这两个表的查询，Access（或 Jet 引擎）将如何，我猜？）处理这个查询？ 它会首先在每个表上发出一些 SELECT 来获取我要加入的字段，找出匹配的行，然后为这些行发出更多 SELECT 吗？
If I have an MS Access database with linked tables from two different database servers (say one table from an SQL Server db and one from an Oracle db) and I write a query which JOINs those two tables, how will Access (or the Jet engine, I guess?) handle this query? Will it issue some SELECTs on each table first to get the fields I'm JOINing on, figurre out which rows match, then issue more SELECTs for those rows?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，您可能会遇到一些严重的性能问题。 我做这种事已经很多年了。 Oracle、Sql 和 DB2 - 呃。 有时我必须在凌晨 5:00 设定计时器，这样当我在 7:00 到达时一切就完成了。
如果您的数据集足够重要，则在本地构建表然后链接数据通常会更快。 对于远程数据集，还要研究传递。
例如，假设您要从 oracle 数据库中提取昨天的所有客户，并从 sql 数据库中提取所有客户购买的商品。 假设您每天平均有 100 个客户，但列表有 30,000 个，并且假设您的产品有一个 500,000 个列表。 您可以在 Oracle 数据库中查询 100 个客户的列表，然后将其写入 SQL 数据库的直通查询中的
IN语句中。 您几乎会立即获得数据。或者，如果您的记录集很大，请构建两个 ID 的本地表，在本地比较它们，然后提取必要的匹配项。
它很丑陋，但你确实可以节省时间。
Yep, you can have some serious performance issues. I have done this type of thing for years. Oracle, Sql, and DB2 - ugh. Sometimes I have had to set it up on a timer at 5:00am so when I get in at 7:00 it's done.
If your dataset is significant enough, it is often faster to build a table locally and then link the data. For remote datasets, also look into passthroughs.
For example, lets say you are pulling all of yesterday's customers from the oracle db and all of the customer purchases from the sql db. Let's say you have an average of 100 customers daily but a list of 30,000 and lets say your products have a list of 500,000. You could query the oracle db for your list of 100 customers, then write it as in
INstatement in a passthrough query to the sql db. You'll get your data almost instantly.Or if your recordsets are huge, build local tables of the two IDs, compare them locally and then just pull the necessary matches.
It's ugly but you can save yourself hours literally.
这是我的猜测。 如果连接两侧都有索引，这会有所帮助，但由于两个服务器都无法完全控制查询，因此不可能进一步优化查询。
That would be my guess. It helps if there are indexes on both sides of the join but, as neither server has full control over the query, further query optimization is not possible.
我没有连接两个不同数据系统中的表的实际经验。 但是，根据要求等，您可能会发现仅使用 Access 表中所需的记录和字段运行 SELECT 查询并在 Access 中执行最终联接和查询会更快。
I have no practical experience joining tables from two different data systems. However, depending on the requirements, etc, etc, you may find it faster to run SELECT queries with only the records and fields required into Access tables and do the final join and query in Access.
需要理解的关键是：
您是否在问 Access/Jet 在将请求发送到两个服务器数据库之前可以优化的问题？ 如果您要连接整个两个表，Jet 将必须请求两个表，这将是丑陋的。
另一方面，如果您可以提供限制连接一侧或两侧的条件，则 Access/Jet 会更加高效，并请求筛选的结果集而不是完整的表。
The key thing to understand is this:
Are you asking a question that Access/Jet can optimize before it sends its request to the two server databases? If you're joining the entirety of both tables, Jet will have to request both tables, which would be ugly.
If, on the other hand, you can provide criteria that limit one or both sides of the join, Access/Jet can be more efficient and request the filtered resultset instead of the full table.