View Single Post
Old 09-20-2019, 05:29 AM   #1
ironfelix717
Newly Registered User
 
Join Date: Sep 2019
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
ironfelix717 is on a distinguished road
Sort by MAX Version query - Very slow

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:
[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!

ironfelix717 is offline   Reply With Quote