强制MS Access将完整查询发送到SQL Server [英] Force MS Access to send full query to SQL server

问题描述

tl;博士
我正在使用MS Access通过ODBC从MS SQL Server DB查询数据.我使用Access编辑器构建了一个查询，这花了很多时间.在运行时，CPU，RAM和I/O值极低.如果单击通过"按钮并将查询重写为SQL，则只需几分钟.在运行MS SQL服务器时，会占用大量CPU，RAM和I/O(根据需要和期望).我的结论是，Access不会为MSSQL服务器提供完整的查询，但可能由itselt提供. 如何强制Access发送完整的查询并让MSSQL承担繁重的工作?

tl;dr
I am using MS Access to query data from a MS SQL Server DB via ODBC. I built a query using the Access editor and it takes forever. While running, the CPU, RAM and I/O values are extremley low. If I click on the Pass-Through button and rewrite the query to be SQL, then it takes only a few minutes. While running the MS SQL server uses a lot of CPU, RAM and I/O (as desired and expected). My conclusion is, that Access does not provide the MSSQL server with the full query but probably does the parts by itselt. How can I force Access to send the full query and let MSSQL do the heavy lifting?

长版
我使用MS Access作为通过ODBC连接到大型(〜100 GB)大型MS SQL Server DB的前端.我有几个查询确实很慢.我为这个问题选择了一个特定的问题，因为我认为问题出在Access和MSSQL之间的通信中.

Long version
I am using MS Access as a frontend to connect via ODBC to a large (~100 GB) MS SQL Server DB. I have several queries which are really slow. I picked a specific one for this question since I believe the problem lies within the communication between Access and MSSQL.

Access-specifi-SQL中的查询如下:

The query in Access-specifi-SQL looks like this:

SELECT DISTINCTROW dbo_A.IDA, dbo_A.MNr, Max(dbo_APK.G) AS MaxG, dbo_AP.IDAPB
FROM ((dbo_A LEFT JOIN dbo_AP ON dbo_A.IDA = dbo_AP.IDA) LEFT JOIN dbo_APK ON dbo_A.IDA = dbo_APK.IDA) INNER JOIN dbo_L ON dbo_A.IDL = dbo_L.IDL
WHERE (((dbo_L.M) Like [LC Dialog]) AND ((dbo_AP.G)<=[<= G Dialog]))
GROUP BY dbo_A.IDA, dbo_A.MNr, dbo_AP.IDAPB
HAVING (((dbo_AP.IDAPB) Like [IDAPB Dialog]));

如您所见，没有什么太花哨的:三个对话框询问用户输入用于WHERE和HAVING子句作为过滤器的值.其余只是基本命令:SELECT，LEFT/RIGHT/INNER JOIN，WHERE，GROUP BY，HAVING

As you can see, nothing too fancy: three dialogs asking the user for values which are used for the WHERE and HAVING clauses as filter. The rest is just basic commands: SELECT, LEFT/RIGHT/INNER JOIN, WHERE, GROUP BY, HAVING

运行它时，Access使用约100MB RAM和5％CPU. MSSQL的CPU大约为10％.两者都几乎没有I/O.查询将永远进行.

While running it, Access uses ~ 100MB RAM and 5 % CPU. MSSQL is at around 10 % CPU. Both do close to no I/O. The query takes forever.

将其转换为真实的SQL的原因在于，将表名中的下划线替换为句点，将参数对话框替换为值，并将DISTINCTROW更改为DISTINCT.全部完成.

Converting this to real SQL comes down to replacing the underscore from the table name with a period, replacing the parameter dialogs with values and changing DISTINCTROW to DISTINCT. all done.

SELECT DISTINCT dbo.A.IDA, dbo.A.MNr, Max(dbo.APK.G) AS MaxG, dbo.AP.IDAPB
FROM ((dbo.A LEFT JOIN dbo.AP ON dbo.A.IDA = dbo.AP.IDA) LEFT JOIN dbo.APK ON dbo.A.IDA = dbo.APK.IDA) INNER JOIN dbo.L ON dbo.A.IDL = dbo.L.IDL
WHERE (((dbo.L.M) Like 'abc') AND ((dbo.AP.G)<='01.01.2020'))
GROUP BY dbo.A.IDA, dbo.A.MNr, dbo.AP.IDAPB
HAVING (((dbo.AP.IDAPB) Like 1));

此查询运行非常快. MSSQL使用约90％的CPU，并且I/O大约是底层SSD的最大值.

This query runs very fast. MSSQL uses ~90 % CPU and the I/O is roughly at the maximum of the underlying SSD.

问题是，对于传递查询，我需要将值输入查询本身，并且不再使用对话框.我该怎么办?如何强制Access从用户(通过对话框)获取值，构建查询并将所有内容发送到MSSQL进行处理?

The problem is, that with the pass-through query, I would need to input the values into the query itself and could no longer use the dialogs. What can I do about it? How can I force Access to get the values from the user (with the dialog), build the query and send everything to MSSQL for processing?

推荐答案

您可以随时修改传递查询.并将您的传递视为一种视图.

You can modify your pass-through query on the fly. And treat your pass-through as a view.

例如:

CurrentDb.QueryDefs("YourPassThrough").sql = 
"SELECT DISTINCT dbo.A.IDA, dbo.A.MNr, Max(dbo.APK.G) AS MaxG, dbo.AP.IDAPB
FROM ((dbo.A LEFT JOIN dbo.AP ON dbo.A.IDA = dbo.AP.IDA) LEFT JOIN dbo.APK ON dbo.A.IDA = dbo.APK.IDA) INNER JOIN dbo.L ON dbo.A.IDL = dbo.L.IDL

WHERE (((dbo.L.M) Like ' "& abcTextString & "') AND ((dbo.AP.G)<='" & YourDate & "'))
GROUP BY dbo.A.IDA, dbo.A.MNr, dbo.AP.IDAPB
HAVING (((dbo.AP.IDAPB) Like " & YourNumber & "));

那么您就可以:

Select * FROM YourPassThrough;

这篇关于强制MS Access将完整查询发送到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！