Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-20-2019, 05:29 AM   #1
ironfelix717
Newly Registered User
 
Join Date: Sep 2019
Posts: 19
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
Old 09-20-2019, 08:03 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,130
Thanks: 13
Thanked 4,079 Times in 4,014 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Sort by MAX Version query - Very slow

See if it works better as two queries:

http://www.baldyweb.com/LastValue.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
ironfelix717 (09-22-2019)
Old 09-20-2019, 08:11 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,464
Thanks: 68
Thanked 2,716 Times in 2,601 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sort by MAX Version query - Very slow

try this:

select max(id) as maxOfID, opid, count("1") As Version from external2 group by opid;

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
ironfelix717 (09-22-2019)
Old 09-20-2019, 08:58 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,352
Thanks: 10
Thanked 2,272 Times in 2,224 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Sort by MAX Version query - Very slow

I would do this with a subquery:

Code:
SELECT OPID, MAX(Version) AS LastVersion
FROM External2
Name that 'sub1'. It's going to find the highest Version for each OPID. Then use it in another query to get the results you want:

Code:
SELECT External2.ID, External2.OPID, Version
FROM External2
INNER JOIN sub1 ON External2.OPID=sub1.OPID AND External2.Version=sub1.Version

3 notes:

1. If you don't need the ID field in the final results then 'sub1' will do what you need in the most effecient manner.

2. Arnelgp's solution will be the most efficient provided you never have a gap in versions. If OPID=1 has 2 records with a gap in versions (Version=1 & Version=5) it will not return accurate results.

3. An index on OPID and Version would help in all cases. Also, if Version only contains numeric data, it should be of a numeric type. A Varchar or other text field for this will cause speed issues.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
ironfelix717 (09-22-2019)
Old 09-22-2019, 01:27 PM   #5
ironfelix717
Newly Registered User
 
Join Date: Sep 2019
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
ironfelix717 is on a distinguished road
Re: Sort by MAX Version query - Very slow

Quote:
Originally Posted by plog View Post
I would do this with a subquery:

3 notes:

1. If you don't need the ID field in the final results then 'sub1' will do what you need in the most effecient manner.

2. Arnelgp's solution will be the most efficient provided you never have a gap in versions. If OPID=1 has 2 records with a gap in versions (Version=1 & Version=5) it will not return accurate results.

3. An index on OPID and Version would help in all cases. Also, if Version only contains numeric data, it should be of a numeric type. A Varchar or other text field for this will cause speed issues.


PLOG,

Great tips and advise. Thanks a lot for your help..

In regards to item #1 of your comments, can i interpret your words as if I do not need the Primary Key (ID) in my data, there is absolutely no reason to
use the JOIN query? I do not need the primary key (ID). Just associated data with the max version. So, with other fields i need data for I was able to get everything i need with:

Code:
SELECT External2.OpID, External2.Type, External2.State, External2.Kind, External2.Number, Max(External2.version) AS LastVersion
FROM External2
GROUP BY External2.OpID, External2.Type, External2.State, External2.Kind, External2.Number;
And this is MUCH faster than the original method.

Thanks best wishes.
ironfelix717 is offline   Reply With Quote
Old 09-22-2019, 02:00 PM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,352
Thanks: 10
Thanked 2,272 Times in 2,224 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Sort by MAX Version query - Very slow

Quote:
In regards to item #1 of your comments, can i interpret your words as if I do not need the Primary Key (ID) in my data, there is absolutely no reason to
use the JOIN query?
You just moved the goalposts with your request. You initially mentioned 3 fields, now you dump 4 new ones into the equation. How GROUP BY queries work are hard for people to get their mind around. Often at first glance they seem to provide what people want, but upon closer inspection, they don't.

I would inspect the return data closer to make sure it truly is giving you what you want in all cases. And/Or write 2 paragraphs for us--the first is a brief overview of what it is your database does within the context of your organization. No databnase jargon, just an explanation of what it is helping you do. The second paragraph you can use a little database jargon and explain what it is you specifically want from this query.
plog is offline   Reply With Quote
Old 09-22-2019, 05:47 PM   #7
ironfelix717
Newly Registered User
 
Join Date: Sep 2019
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
ironfelix717 is on a distinguished road
Re: Sort by MAX Version query - Very slow

Hi PLOG,

Here is the info you request:

The project's current context is a non-commerical personal project that has been in development for about 14 months now. This application is being pre-developed by myself for use in a small manufacturing business that currently does not exist. The anticipated scope of this app is 10 concurrent users (doing various tasks, running extensive reports being one that is probably very uncommon) and that is quite generous. The backend is simply an ACCESS file stored locally on the development workstation -- which will change to SQL Server in further development stages.

The query this thread discusses is not specific to any single table due to the framework that I have developed for how my database works: which is that every record is versioned. Any time an element of the system is saved/modified, a new record is created and versioned. This practice holds true for every key table in the system. The primary reason is to roll back data - which yeah SQL server can do some cool stuff with rolling back data without this architecture, but I am not a database expert and this is the method that works for my skillset.

I have considered another approach to versioning which is dumping the previous version to a history table and a new saved item goes to the master table, which makes querying current records a lot faster but in terms of development, i think this might be more work for me in the long run due to other factors (testing the history table if a specific version exists, etc).


The best assumptions I can make for you generalizing my needs are:

1.) This query is usually only need when a user has to make a selection to load an element. They need a list of the data to load. Other query criteria may exist depending on how they use the form to filter the listbox.

2.) However, the query usually only need the element's ID (opID), and a few other fields (say, 5 max) to put into a listbox for the user to select from.

3.) Primarykey (ID) is always indexed autonumber field - no surprise there.

4.) The element ID (OpID) is always short text

5.) Versions may or may not contain gaps - they are Number datatype with field size long integer.

6.) There will be a realistic limit designed into the app for versions and managing expired/unneeded versions - to keep things from growing exponentially - which i suspect will really not be a problem given the scope of this project.



Thanks for your help!



Last edited by ironfelix717; 09-22-2019 at 05:59 PM.
ironfelix717 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
summarizing query of Date Diff query running really slow weeblebiker Queries 13 05-06-2013 06:08 AM
Optimizing Slow Query (Query Grid Diagram Attached) ions Queries 16 11-30-2009 05:16 AM
Access slow to load [really really slow!] Elfman General 3 12-14-2007 05:32 AM
report sort different from query sort antonyx General 3 04-27-2006 02:29 PM
how do i sort my data by a report field (override query sort)? starfailure Reports 2 01-04-2002 08:10 PM




All times are GMT -8. The time now is 05:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World