Most efficient SQL to select newest related record

[TheSmileyCoder]

I have a table of observations. This table is loaded as a recordset, and processed to be used in a Treeview control.

Part of this process is to determine which icon to display for the record in the treeview. The icon is determined by the status, and the status is determined by the latest related record in tbl_Updates.

Table Date:

tbl_Obs
PK_Obs (Autonumber)
tx_Obs (Text)
... (other fields)

tbl_Status
PK_Status (Autonumber)
tx_Status (Text)

tbl_Updates
PK_Update (Autonumber)
ID_Obs (Foreign key to PK_Obs, Long)
tx_Update
ID_Status (Foreign key to PK_Status)
dt_Date (Date/Time Timestamp of record creation)
... (other fields)

The Program flow:
A observation is created into tbl_Obs. At various points during the lifetime of a observation it will get updated and a new record is created in the update table each time. Update records are new er deleted or overwritten.

An example could be:
Observation (1):
Alignment of tracks is off by 2 cm

Updates:
2012-11-10 Open - Documentation Requested from supplier
2012-11-11 Open - Documentation Received from supplier
2012-11-13 Closed - Documentation evaluated and found satisfying.

The status of the observation is always determined by the latest update. So for the point of loading my information into the treeview I need a query giving me the PK_Obs, tx_Obs and the LATEST (based on date) ID_Status from the observations table.

Sure I can get this done by the use of multiple queries or subqueries, but I wonder what what would be the most efficient way to retrieve this? And why exactly is it the most efficient way?




The (a) subquery way:

SELECT tbl_Obs.PK_Observation, 
       tbl_Obs.mem_Observation, 
       (SELECT TOP 1 U.ID_Status 
            from tbl_ObsUpdate AS U 
            WHERE U.ID_Obs=PK_Observation 
            ORDER BY U.dt_Created DESC) AS Status
       FROM tbl_Obs

Creating an extra query:
I could create an extra query with the syntax:

SELECT   tbl_ObsUpdate.ID_Obs, 
         Last(tbl_ObsUpdate.ID_Status) AS LastOfID_Status
FROM     tbl_ObsUpdate
GROUP BY tbl_ObsUpdate.ID_Obs
ORDER BY Max(tbl_ObsUpdate.dt_Created) DESC;

And then join this to my main query.

[NeoPa]

I would expect the most efficient way to be the use of the subquery.

If my understanding of the question is correct in that it boils down to :
Is the subquery or the separate, pre-optimised, QueryDef the more efficient?

My reasoning for this is that with the subquery, the pre-optimisation of the SQL is all done together. That way, it is aware of the whole requirement and is not making assessments based on a scenario that is not its main requirement. In other words, when the QueryDef object is being optimised it will be done without any appreciation of what it's mainly used for. Does that make sense?

Ultimately, time and usage will tell of course ;-)

[TheSmileyCoder]

I am getting confused now. The Subquery I wrote is giving me trouble. The recordset loads fine, but when I reach the point where I loop through the recordset by using recordset.FindF irst I run into trouble with error 3354 "At Most One Record ca be returned by this Subquery).

I thought my subquery was returning just 1 record...

[NeoPa]

Not necessarily Smiley ;-)

The TOP n predicate actually returns n records in most circumstances, but where there are more than one nth records it will return all nth records as well as all others higher in the order.

[TheSmileyCoder]

Any recommendation on how to modify the subquery in the most efficient way?

[NeoPa]

If you have the possibility of multiple observations for the same date then I would definitely advise you to use a TimeStamp (Date and Time) field rather than a simple Date field. That obviates the problem (Causes it not to be an issue).

[Rabbit]

I have to disagree. I believe the separate query will be quicker. When the query that joins to the MAX query, I believe the engine will then be able to optimize them together.

The issue with the subquery in the SELECT clause is that it references the outer query. This means the subquery must be run multiple times, at least once for each distinct value referenced in the subquery.

Because you can have multiple updates with the same date, you would do something like this:

SELECT MAX(PK) AS MaxPK
FROM someTable a
INNER JOIN (
   SELECT 
      groupField,
      MAX(dateField) AS dt
   FROM someTable
   GROUP BY groupField
) t ON
   a.groupField = t.groupField AND
   a.dateField = t.dt

[NeoPa]

NeoPa:
If my understanding of the question is correct in that it boils down to :
Is the subquery or the separate, pre-optimised, QueryDef the more efficient?

Since my first response I noticed that this is certainly not a correct understanding in that the two different approaches are different in more ways than simply that. It seems Rabbit's correct in this, for the reason he explained, but frankly knowing him on SQL generally I'd take that as read anyway.

[TheSmileyCoder]

Sorry, my bad. The datetime field IS stored as date & time(though formatted to only display date).
I can't test the other replies now, but willl test it when I get back to my PC.

[NeoPa]

If it's already a TimeStamp then I would expect it to be very rare that the subquery would produce duplicates. See if you have any.

Smiley:
I need a query giving me the PK_Obs, tx_Obs and the LATEST (based on date) ID_Status from the observations table.

No wonder I was confused earlier. Can I take this to mean [ID_Status] should be from [tbl_ObsUpdate]?

As the subquery (that one particularly) approach is probably not the most efficient one anyway, I would consider something different :

SELECT   [ID_Obs]
       , Max([dt_Created]) AS [MaxUpdateDate]
FROM     tbl_ObsUpdate
GROUP BY [ID_Obs]

The table and field names might be awry. I checked with your table layouts, but the code you posted didn't match, so I used my best guesses. I mainly went with the code. This is very unlike you Smiley :-D Almost like a noob poster.

Anyway, moving on, with the query above, and I doubt it matters greatly whether it's a subquery or a separately defined QueryDef, you can link up to your main data thus (We'll refer to the above query as [subQ] for now.) :

SELECT tbl_Obs.*
     , tbl_ObsUpdate.*
FROM   ([tbl_Obs]
       INNER JOIN
       [tbl_ObsUpdate]
  ON   tbl_Obs.PK_Observation=tbl_ObsUpdate.ID_Obs)
       LEFT JOIN
       [subQ]
  ON   tbl_ObsUpdate.ID_Obs=subQ.ID_Obs
 AND   tbl_ObsUpdate.dt_Created=subQ.MaxUpdateDate

Hope this helps :-)

[TheSmileyCoder]

I do still believe that my posted code matches my table layout as described.
I admit that the line:

I need a query giving me the PK_Obs, tx_Obs and the LATEST (based on date) ID_Status from the observations table.

might have been confusing, it should have read:

I need a query giving me the PK_Obs, tx_Obs from the observations table and the LATEST (based on date) ID_Status from the updates table.

Maybe I should sign up for a course in SQL. I can usually get what I want, with a bit of trial and error, but I am often left wondering if it could be done smarter and more efficient. Another issue is that while it may seem just fine as I test it, once I get the first 10.000 records what I thought was fine is suddenly a slow drag.

[NeoPa]

Smiley:
I do still believe that my posted code matches my table layout as described.

I'm just busting your chops in a very jocular way my friend (but you might want to check the name of the updates table in the layout - tbl_Updates - and the SQL - tbl_ObsUpdate). Ultimately, you can make mistakes till the cows come home and I'd be happy to do what I can to help. You've more than earned that right. I was only pointing them out for a little fun.

As for the understanding of SQL, I found most of what I know from looking at the SQL produced by Access QueryDefs, then attempting more as I went on. Much of the more involved stuff I got into after seeing what Rabbit came up with on here. Obviously, I also looked things up when I needed to (Finding Jet SQL Help proved pretty helpful there.), but that is not to say that a helpful course wouldn't give you a kick-start. Just know that someone of your undoubted intelligence will certainly develop your skills as you go on as long as you keep using them.

[TheSmileyCoder]

Ah yes, I did make a error there. Dam!
As I am sure you agree, asking the right question with the right information in the right amount, is an art. I love those questions where I as a replier feel I have all the needed information straight from the first post, and can provide a meaningful reply straight away, instead of spending the first 4 back-and-forth posts asking for more information. Sometimes in trying to simplify the problem its possible to end up confusing (Incorrectly typed table names for example).

As for the SQL its more the basic understanding of indexes, grouping and how using these affects the efficiency of the SQL. Granted for 95% of the SQL I have used so far the results are so near instantaneous as to not really matter whether I could save 80% by doing it differently, I would still like to work "Best practice" in as a default rather then an afterthought when some query starts to slow down the system.

I will try a few of the suggestions and see what I decide to go with in the end.

[NeoPa]

Smiley:
As I am sure you agree, ...

Absolutely. Furthermore, I already recognised that errors crept in, largely, due to your attempt to make the question as clear and fully specified as possible. Fear not. Your questions are among the best to work with. Necessarily less simple than most, but always expressed well and fully.

[Rabbit]

The degree of duplication in dt_Created depends on how it's being populated. If it's being populated with something like the Date() function, then duplication would be rare to non-existent. However, if it's being typed in or chosen from a control by the user, then it's unlikely they're typing in a time. In which case, the default time will be used, and hence, duplication becomes a problem.

As far as writing efficient queries goes, my knowledge comes from reading online articles and lots of trial and error. If anything runs for more than 5 minutes, I usually get impatient and rewrite the query using what I've read in those articles until I stumble upon a better query.

This isn't available in Access but in the larger DBMS's, they have the ability to view the query plan and how long it estimates each step will take. I will often use that to identify potential indexes to create.