訪問未正確將 TOP 謂詞轉換為 ODBC/Oracle SQL

我有一個基於連結 ODBC 表 (Oracle) 的 MS Access 查詢。

底層 Oracle 表的 DDL 是：

create table road_insp
(
   insp_id integer,
   road_id integer,
   insp_date date,
   length number(10,2)
);

INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (1, 100, to_date('1/1/2017','MM/DD/YY'), 20);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (2, 101, to_date('2/1/2017','MM/DD/YY'), 40);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (3, 101, to_date('3/1/2017','MM/DD/YY'), 60);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (4, 102, to_date('4/1/2018','MM/DD/YY'), 80);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (5, 102, to_date('5/1/2018','MM/DD/YY'), 100);
INSERT INTO road_insp (insp_id, road_id, insp_date, length)  VALUES (6, 102, to_date('5/1/2018','MM/DD/YY'), 120);

select * from road_insp

  INSP_ID    ROAD_ID   INSP_DAT     LENGTH
---------- ----------   -------- ----------
        1        100   17-01-01         20
        2        101   17-02-01         40
        3        101   17-03-01         60
        4        102   18-04-01         80

        5        102   18-05-01        100
        6        102   18-05-01        120

本地 MS Access 查詢為：

SELECT ri.*
FROM user1_road_insp AS ri
WHERE ri.insp_id = (
                   select 
                       top 1 ri2.insp_id   
                   from 
                       user1_road_insp ri2 
                   where 
                       ri2.road_id = ri.road_id 
                       and year(insp_date) between  [Enter a START year:] and [Enter a END year:]
                   order by 
                       ri2.insp_date desc, 
                       ri2.length desc,
                       ri2.insp_id
                  );

問題：

查詢的性能很差。與範例 DDL 中的表不同，實際表有大約 10,000 條記錄。類似的查詢會立即執行，但此查詢需要幾分鐘才能執行。

我檢查了SQLOut.txt 日誌，問題似乎是它執行了太多的單個語句：

SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri" 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? ) 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? ) 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? ) 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? ) 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? ) 
SQLExecDirect: SELECT "INSP_ID" ,"ROAD_ID" ,"INSP_DATE" ,"LENGTH"  FROM "USER1"."ROAD_INSP" "ri2" WHERE ({fn year("INSP_DATE" )}BETWEEN  ? AND  ? )

題：

與我的其他本地 MS Access 查詢不同，此查詢使用TOP謂詞。MS Access 似乎正在嘗試轉換TOP為 ODBC 驅動程序和/或 Oracle 可以理解的 SQL。但是，它似乎做得很差。

如何提高此查詢的性能？

正如 Mike Walsh 所建議的，這實際上是一個 Access 問題，而不是 ODBC 問題。ODBC 驅動程序確實應該接受與它們旨在連接的數據庫相同的 SQL 方言。顯然，Access 沒有正確翻譯 Top 謂詞，因此它改為為主 SQL 查詢的每一行重新執行子查詢。您連結到的Microsoft 文件的“將查詢處理移動到伺服器”部分簡要提到了這些陷阱。

更新

我之前曾推薦使用聚合函式 First()，但現在在所選列與多個排序欄位不同的情況下取消該建議。至少在 Access 中，在對多個欄位進行排序時，它的行為不正確。詳情見文末。

1. 使用 Oracle SQL 方言使用 Access Pass-through 查詢。這繞過了 Access SQL 解釋器和執行引擎，而是將查詢直接發送到數據庫伺服器。
2. 加入子查詢，而不是在 WHERE 子句子查詢中引用外部表。最好的情況是，這有望轉化為發送到伺服器的完整語句。我認為最壞的情況是向伺服器發送少量、確定數量的查詢，Access 在本地執行連接（可能還有聚合和/或排序），這通常足夠快。在對 road_id 進行分組時，如果不使用 Top 或 First()，替代方法看起來有些難看，但仍可能比原始查詢更有效。

---

SELECT ri4.*
FROM user1_road_insp ri4
INNER JOIN
 (select ri3.road_id, min(ri3.insp_id) as min_insp_id
 from user1_road_insp ri3 INNER JOIN 
   (select ri2.road_id, a1.max_insp_date, Max(ri2.length) As max_length
   from user1_road_insp ri2 INNER JOIN
     (select ri1.road_id, Max(ri1.insp_date) As max_insp_date
     from user1_road_insp ri1
     where year(ri1.insp_date) between [Enter a START year:] and [Enter a END year:]
     group by ri1.road_id) a1
   on ri2.road_id = a1.road_id AND ri2.insp_date = a1.max_insp_date
   where year(ri2.insp_date) between [Enter a START year:] and [Enter a END year:]
   group by ri2.road_id, a1.max_insp_date) a2
 on ri3.road_id = a2.road_id AND ri3.insp_date = a2.max_insp_date and ri3.length = a2.max_length
 where year(ri3.insp_date) between [Enter a START year:] and [Enter a END year:]
 group by ri3.road_id) a3
ON (ri4.road_id = a3.road_id) AND (ri4.insp_id = a3.min_insp_id)

請注意，對於每個子查詢重複應用 WHERE 條件。這在 Access 中是必要的，因為每個聚合查詢必須使用相同的條件從相同的數據中提取，否則結果可能是虛假的。（Access 不支持在單個“查詢”中使用臨時表和多個語句，這可以用於預過濾和重用相同的數據集。）嵌套查詢是匹配原始查詢所必需的，因為必須將最小/最大選擇應用於每列單獨正確模擬排序順序。

最外層的查詢是否也應該重新應用相同的選擇標準？只有原始發布者可以知道這一點，但應該考慮返回的集合可能包含與子查詢條件相矛盾的行。

---

First() 的問題

Access 將 First() 視為任何其他聚合函式，因此它的使用要求 Select 或 Order By 子句中的所有其他欄位也被聚合。考慮到 First() 函式僅在指定 Order By 子句時有用（否則它返回任意值）。但是，只要 Order By 中的所有欄位也包含在聚合語句中，就會為值的唯一組合生成多行，從而有效地使 First() 函式的聚合行為無效。

如果單個排好序的列與 First() 中的相同，則沒有問題。同樣，如果排序順序與基礎表的索引鍵列匹配，它可能表現得足夠好。否則，Access 不一定尊重子查詢的排序順序（請參閱https://stackoverflow.com/questions/13651394/what-order-is-used-by-first-function），因此僅進行排序是不夠的first 並使用 First() 函式將其傳遞到語句中，因為從技術上講，Access 可以按任何順序處理輸入行。從本質上講，Access 使得無法可靠地對應該為此問題應用 First() 或 Last() 函式的行進行排序。

引用自：https://dba.stackexchange.com/questions/189441