UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Speed Up Top N Per Group, Access 2010    
 
   
armadillo
post Jul 17 2017, 04:27 AM
Post#1



Posts: 141
Joined: 4-August 14



In the last topic I posted on I received some great help in learning how to write a "top n per group" query. I'm back now requesting assistance on getting the information I need a little faster. I want to determine the most recent event each bear was observed in. (These are black bears and events are things like being captured and radio-collared, being hit by cars, etc.)

Attached File  Relationships.JPG ( 21.52K )Number of downloads: 0


The queries I that I was using (which I'll describe under Method 1 below) return the correct records (297 of them) but slowly. It takes ~20 seconds. To try and speed things up, I did some googling and came up with Method 2. This works much faster (less than a second) but only returns 291 records. I cannot figure out why 6 records are dropping out, but I'm guessing it has something to do with "ties" in which the same bear is involved in multiple events on the same day.

I hope I'm not stuck with using Method 1 because things start to get really slow when I start combining the final query from that method with others for report recordsources.

Method 1
Step 1: Create a saved query called “qryEventsObsKnownBears”

SQL
SELECT
tblEvents.EventID,
tblEvents.EventDate,
tblEvents.LocationDetails,
tblObservations.BearID,
tblObservations.WeightEstimated
FROM tblEvents INNER JOIN tblObservations ON tblEvents.EventID = tblObservations.EventID
WHERE (((tblObservations.BearID) IS NOT NULL));


Step 2: Use the previously saved query in the next on called "qryLastEventsPerBear" to obtained the desired results.

SQL
SELECT
qryEventsObsKnownBears.EventID,
qryEventsObsKnownBears.EventDate,
qryEventsObsKnownBears.LocationDetails,
qryEventsObsKnownBears.BearID,
qryEventsObsKnownBears.WeightEstimated
FROM qryEventsObsKnownBears WHERE (((qryEventsObsKnownBears.EventID) IN (SELECT TOP 1
EventID
FROM qryEventsObsKnownBears AS Dupe
WHERE Dupe.BearID = qryEventsObsKnownBears.BearID
ORDER BY Dupe.EventDate DESC, Dupe.EventID DESC)
))
ORDER BY qryEventsObsKnownBears.BearID;


Method 2:

Step 1: Use "qryEventsObsKnownBears" to create a saved query called "qryBearEventRankingLast"

SQL
SELECT
qryEventsObsKnownBears.EventID,
qryEventsObsKnownBears.EventDate,
qryEventsObsKnownBears.LocationDetails,
qryEventsObsKnownBears.BearID,
qryEventsObsKnownBears.WeightEstimated,
COUNT(*) AS Rank
FROM qryEventsObsKnownBears INNER JOIN qryEventsObsKnownBears AS dupe ON (qryEventsObsKnownBears.EventDate <= dupe.EventDate) AND (qryEventsObsKnownBears.BearID = dupe.BearID)
GROUP BY qryEventsObsKnownBears.EventID,
qryEventsObsKnownBears.EventDate,
qryEventsObsKnownBears.LocationDetails,
qryEventsObsKnownBears.BearID,
qryEventsObsKnownBears.WeightEstimated;


Step 2: Use the previously saved query in the next on called "qryLastEventsPerBear2" to obtained the desired results.

SQL
SELECT
qryBearEventRankingLast.EventID,
qryBearEventRankingLast.EventDate,
qryBearEventRankingLast.LocationDetails,
qryBearEventRankingLast.BearID,
qryBearEventRankingLast.WeightEstimated
FROM qryBearEventRankingLast WHERE ((([qryBearEventRankingLast].[Rank]) = 1))
ORDER BY qryBearEventRankingLast.BearID;



Attached File(s)
Attached File  TopN__3_.zip ( 618.14K )Number of downloads: 5
 
Go to the top of the page
 
MadPiet
post Jul 17 2017, 01:35 PM
Post#2



Posts: 2,148
Joined: 27-February 09



WARNING: TANGENT.
I tried this in Access and it's slower than Christmas.
Did same thing in SQL Server - on the same computer with a windowing function and it was less than one second.

CODE
SELECT *
FROM (
    SELECT          b.BearID,
                    ROW_NUMBER() OVER (PARTITION BY b.BearID ORDER BY e.EventDate) AS TopEventNum,
                    e.EventID,
                    e.EventDate,
                    e.LocationDetails AS Loc,
                    o.ObservationID,
                    o.WeightEstimated,  
                    b.BearNo,
                    b.[censored]
    FROM          Observations  o INNER JOIN
                             Bears b ON o.BearID = b.BearID INNER JOIN
                             [Events] e ON o.EventID = e.EventID
    ) x
WHERE x.TopEventNum = 1;


duration was less than a millisecond on the data you posted. =(

Conclusion... SQL Server is harder to learn, but TOTALLY worth it...
This post has been edited by MadPiet: Jul 17 2017, 01:43 PM
Go to the top of the page
 
armadillo
post Jul 17 2017, 03:29 PM
Post#3



Posts: 141
Joined: 4-August 14



MadPiet,

Thanks for the response. Just to clarify, are you suggesting that having a query that runs slower than Christmas is the only way to go unless I use SQL server?
Go to the top of the page
 
MadPiet
post Jul 17 2017, 03:45 PM
Post#4



Posts: 2,148
Joined: 27-February 09



I was going to suggest looking at how Allen Browne does TOP N per Group in Access, but when I tried to go to his website, it's down.

I was just pointing out that this works REALLY well in SQL Server, so if you have that at your disposal, that would work really well.
Go to the top of the page
 
armadillo
post Jul 17 2017, 03:50 PM
Post#5



Posts: 141
Joined: 4-August 14



I based the query in Method 1, Step 2 off of the example on Allen Browne's website. (Having that site down is tough...I used it all the time!)
Go to the top of the page
 
MadPiet
post Jul 17 2017, 04:39 PM
Post#6



Posts: 2,148
Joined: 27-February 09



If you based your query on Allen Browne's example, then I'd say that's probably the best way to do it. I was going to say index everything, but you already had.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 09:37 AM