ironfelix717
Registered User.
- Local time
- Today, 11:53
- Joined
- Sep 20, 2019
- Messages
- 193
Hi,
I am experimenting with a query over the network for an in-development application. FE is access and BE is access.
With a test of 100,000 records, this query is very slow.
Fields of concern are: PK autonumber, OperationID, and version.
OperationID can be duplicative. Each record with a given OperationID has a specific record version.
I need to retrieve each OperationID in the recordset, and return its data
where its version is the MAX for its category.
Ex:
ID------OPID--------Version
1-------ABC1--------1
2-------ABC1--------2
3-------ZXY2--------1
Query should return:
ID------OPID-------Version
2-------ABC1--------2
3-------ZXY2--------1
I've been using this query/subquery:
I am experimenting with a query over the network for an in-development application. FE is access and BE is access.
With a test of 100,000 records, this query is very slow.
Fields of concern are: PK autonumber, OperationID, and version.
OperationID can be duplicative. Each record with a given OperationID has a specific record version.
I need to retrieve each OperationID in the recordset, and return its data
where its version is the MAX for its category.
Ex:
ID------OPID--------Version
1-------ABC1--------1
2-------ABC1--------2
3-------ZXY2--------1
Query should return:
ID------OPID-------Version
2-------ABC1--------2
3-------ZXY2--------1
I've been using this query/subquery:
Code:
sql = "SELECT opID FROM External2 WHERE External2.ID IN (SELECT TOP 1 Dupe.ID FROM External2 AS Dupe WHERE Dupe.OpID=External2.OpID ORDER BY Dupe.Version DESC) ORDER BY OPID;"
/CODE]
But this is way too slow over network. Does not complete with 2 minutes which is far too slow.
Only indexes are on primary key.
Thanks for any help!