I have a MS Access query that is based on a linked ODBC table (Oracle).
The DDL for the underlying Oracle table is:
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
And the local MS Access Query is:
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
);
The Problem:
The performance of the query is quite poor. Unlike the table in the sample DDL, the real table has about 10,000 records. Similar queries execute instantly, but this query takes several minutes to run.
I've checked the SQLOut.txt log, and the problem seems to be that it is executing way too many individual statements:
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 ? )
Question:
Unlike my other local MS Access queries, this query uses the TOP
predicate. MS Access seems to be attempting to translate TOP
to SQL that the ODBC driver and/or Oracle can understand. However, it seems to be doing a very poor job of it.
How can I improve the performance of this query?
ri.*
This can also affect how Access optimizes a query. Try selecting specific columns in the second query, even if it means list out all columns in sourceri
– C Perkins Oct 27 '17 at 2:59