I've got MS Access database with linked tables, whenever each table is linked to a table in the same SQL Server database. I have a query inside Access that joins two tables (in particular I'm updating a table based on another using a join).
每当每个表链接到同一SQL Server数据库中的表时,我都有带链接表的MS Access数据库。我在Access中有一个连接两个表的查询(特别是我正在使用连接更新基于另一个表的表)。
The question is does Access "download" all the table data before doing a join? Or is smart and joining it on the SQL Server?
问题是在进行连接之前Access是否“下载”了所有表格数据?或者是聪明的并加入SQL Server?
The query is:
查询是:
UPDATE TBL_INVOICE_CHARGES INNER JOIN TBL_ANI
ON (TBL_INVOICE_CHARGES.CH_CUST_ID = TBL_ANI.ANI_CUST_ID)
AND (TBL_INVOICE_CHARGES.CH_ANI = TBL_ANI.ANI_NZ_ANI)
SET TBL_INVOICE_CHARGES.ANI_NOTES = TBL_ANI.ANI_NOTES;
3 个解决方案
#1
2
According to Albert D. Kallal (Access MVP), Access does not pull the whole tables, except for complex views, where things can get messed up. However, it is best to use views on the server and link to the view, or to use pass-though queries.
根据Albert D. Kallal(Access MVP)的说法,除了复杂的视图之外,Access不会拉动整个表格。但是,最好使用服务器上的视图并链接到视图,或使用pass-though查询。
Further information: How to create an SQL pass-through query in Access
更多信息:如何在Access中创建SQL传递查询
#2
2
Likely Jet will hand off the whole thing to SQL Server, which will do an index join and then perform the updates. In other words, for a simple query like your example, it's all being done on the server, with not one byte pulled across the wire for local processing.
可能Jet会将整个事情交给SQL Server,后者将执行索引连接,然后执行更新。换句话说,对于像您的示例这样的简单查询,它都是在服务器上完成的,没有一个字节被拉过线进行本地处理。
It's pretty easy to make Jet pull the whole table. The easiest way is to put an Access expression in your WHERE clause. Here's an example that would cause it to happen:
让Jet拉开整个桌子非常容易。最简单的方法是在WHERE子句中放入Access表达式。这是一个会导致它发生的例子:
WHERE Format(MyDate,"YYYY") = 2008
The whole table is going to have to be pulled so that Access can run the Format() function on all the dates in your table. Also, it won't be able to use any indexes and will thus be very slow. It would be slow with a Jet back end, too, simply because it's so inefficient. The proper way to write this WHERE clause is:
必须拉出整个表,以便Access可以在表中的所有日期运行Format()函数。此外,它将无法使用任何索引,因此将非常慢。 Jet后端也会很慢,仅仅是因为效率太低。编写此WHERE子句的正确方法是:
WHERE MyDate Between #1/1/2008# And #12/31/2008#
If you write that in a saved Access query, it will be handed off to the SQL Server for processing (and ODBC will send appropriate delimiters if your back end db engine uses different ones than what Jet SQL uses).
如果您在保存的Access查询中写入它,它将被传递到SQL Server进行处理(如果您的后端数据库引擎使用的不同于Jet SQL使用的,则ODBC将发送适当的分隔符)。
But if you're not doing that kind of thing, you're unlikely to encounter the problem of pulling too much data across the wire. Jet is, in fact, quite smart, and does a remarkably good job of sending as much of a query across the wire for processing as possible. For example, if you call Access functions in your SELECT statement, the underlying select without the Access functions will be sent to the server, and the functions will then be performed in Access on the result set. For this Access query:
但是,如果你没有做那种事情,你就不太可能遇到通过网络吸收过多数据的问题。事实上,Jet非常聪明,并且尽可能地通过线路发送尽可能多的查询。例如,如果在SELECT语句中调用Access函数,则不会将具有Access函数的基础选择发送到服务器,然后将在结果集的Access中执行这些函数。对于此Access查询:
SELECT Format(MyDate,"MM-DD")
FROM MyTable
WHERE MyDate Between #1/1/2008# And #12/31/2008#
Jet will send this to the server:
Jet会将此信息发送到服务器:
SELECT MyDate
FROM MyTable
WHERE MyDate Between #1/1/2008# And #12/31/2008#
Once Jet has received from the server only the rows that match the criteria, it will only then format the date field using the Access Format() function. This also works with JOINs, especially joins on indexed fields (though non-indexed field joins will probably also be handed off to the server).
Jet一旦从服务器收到符合条件的行,它就会使用Access Format()函数格式化日期字段。这也适用于JOIN,尤其是索引字段上的连接(尽管非索引字段连接也可能会传递给服务器)。
Now, sometimes Jet does guess wrong and ends up being incredibly inefficient. In those cases, you can set up views and stored procedures on the server and use passthrough queries to make sure that Jet's wrong guesses will be avoided.
现在,有时Jet确实猜错了,最终效率极低。在这些情况下,您可以在服务器上设置视图和存储过程,并使用直通查询来确保避免Jet错误的猜测。
#3
0
I've looked at SQL Profiler (unfortunately i don't have the actual log handy) and here's what i found:
我看过SQL Profiler(遗憾的是我没有实际的日志),这就是我发现的:
- it selects all records from both tables
- for each record in one table calls an UPDATE So this will be slow.
它从两个表中选择所有记录
对于一个表中的每个记录调用UPDATE所以这将是缓慢的。
#1
2
According to Albert D. Kallal (Access MVP), Access does not pull the whole tables, except for complex views, where things can get messed up. However, it is best to use views on the server and link to the view, or to use pass-though queries.
根据Albert D. Kallal(Access MVP)的说法,除了复杂的视图之外,Access不会拉动整个表格。但是,最好使用服务器上的视图并链接到视图,或使用pass-though查询。
Further information: How to create an SQL pass-through query in Access
更多信息:如何在Access中创建SQL传递查询
#2
2
Likely Jet will hand off the whole thing to SQL Server, which will do an index join and then perform the updates. In other words, for a simple query like your example, it's all being done on the server, with not one byte pulled across the wire for local processing.
可能Jet会将整个事情交给SQL Server,后者将执行索引连接,然后执行更新。换句话说,对于像您的示例这样的简单查询,它都是在服务器上完成的,没有一个字节被拉过线进行本地处理。
It's pretty easy to make Jet pull the whole table. The easiest way is to put an Access expression in your WHERE clause. Here's an example that would cause it to happen:
让Jet拉开整个桌子非常容易。最简单的方法是在WHERE子句中放入Access表达式。这是一个会导致它发生的例子:
WHERE Format(MyDate,"YYYY") = 2008
The whole table is going to have to be pulled so that Access can run the Format() function on all the dates in your table. Also, it won't be able to use any indexes and will thus be very slow. It would be slow with a Jet back end, too, simply because it's so inefficient. The proper way to write this WHERE clause is:
必须拉出整个表,以便Access可以在表中的所有日期运行Format()函数。此外,它将无法使用任何索引,因此将非常慢。 Jet后端也会很慢,仅仅是因为效率太低。编写此WHERE子句的正确方法是:
WHERE MyDate Between #1/1/2008# And #12/31/2008#
If you write that in a saved Access query, it will be handed off to the SQL Server for processing (and ODBC will send appropriate delimiters if your back end db engine uses different ones than what Jet SQL uses).
如果您在保存的Access查询中写入它,它将被传递到SQL Server进行处理(如果您的后端数据库引擎使用的不同于Jet SQL使用的,则ODBC将发送适当的分隔符)。
But if you're not doing that kind of thing, you're unlikely to encounter the problem of pulling too much data across the wire. Jet is, in fact, quite smart, and does a remarkably good job of sending as much of a query across the wire for processing as possible. For example, if you call Access functions in your SELECT statement, the underlying select without the Access functions will be sent to the server, and the functions will then be performed in Access on the result set. For this Access query:
但是,如果你没有做那种事情,你就不太可能遇到通过网络吸收过多数据的问题。事实上,Jet非常聪明,并且尽可能地通过线路发送尽可能多的查询。例如,如果在SELECT语句中调用Access函数,则不会将具有Access函数的基础选择发送到服务器,然后将在结果集的Access中执行这些函数。对于此Access查询:
SELECT Format(MyDate,"MM-DD")
FROM MyTable
WHERE MyDate Between #1/1/2008# And #12/31/2008#
Jet will send this to the server:
Jet会将此信息发送到服务器:
SELECT MyDate
FROM MyTable
WHERE MyDate Between #1/1/2008# And #12/31/2008#
Once Jet has received from the server only the rows that match the criteria, it will only then format the date field using the Access Format() function. This also works with JOINs, especially joins on indexed fields (though non-indexed field joins will probably also be handed off to the server).
Jet一旦从服务器收到符合条件的行,它就会使用Access Format()函数格式化日期字段。这也适用于JOIN,尤其是索引字段上的连接(尽管非索引字段连接也可能会传递给服务器)。
Now, sometimes Jet does guess wrong and ends up being incredibly inefficient. In those cases, you can set up views and stored procedures on the server and use passthrough queries to make sure that Jet's wrong guesses will be avoided.
现在,有时Jet确实猜错了,最终效率极低。在这些情况下,您可以在服务器上设置视图和存储过程,并使用直通查询来确保避免Jet错误的猜测。
#3
0
I've looked at SQL Profiler (unfortunately i don't have the actual log handy) and here's what i found:
我看过SQL Profiler(遗憾的是我没有实际的日志),这就是我发现的:
- it selects all records from both tables
- for each record in one table calls an UPDATE So this will be slow.
它从两个表中选择所有记录
对于一个表中的每个记录调用UPDATE所以这将是缓慢的。