2

I have a table of "encounters" with a unique ID and date, and a table of addendums which contain the same IDs and multiple update dates. Given a set of encounters that I specify, I need to know the last time it was updated.

My current query looks loosely like this:

SELECT
     enc.encounter_id,
     ISNULL(addend.date,enc.date) date
FROM
     encounters enc
     left outer join (select addendums.encounter_id, 
                        max(addendums.update_date) 
                  from addendums 
                  group by addendums.encounter_id) addend on addend.encounter_id=enc.encounter_id
WHERE
     *some criteria available on enc but not addend*

This functions as expected, but I'm concerned that I'm running the derived table addend for all lines. Does the plan pass the join criteria and only run the derived table for each encounter ID? Or is the derived table ran for all lines every time?

Aww_Geez
  • 148
  • 5
  • From a logical standpoint it does not execute once per row. You should be fine. – shawnt00 Feb 05 '20 at 15:45
  • @shawnt00 So the derived table first receives the encounter ID, then runs for only that ID? – Aww_Geez Feb 05 '20 at 15:49
  • it is execution plan dependent. You might get a nested loops `APPLY` which does this row by row and passes in the outer value or you might get a different plan that aggregates the whole thing and joins the aggregated result – Martin Smith Feb 05 '20 at 15:51
  • You can verify this in the execution plan (by right-clicking in the query window background in management studio and toggling "include actual execution plan" before running your query), where it will show the number of executions – Cedersved Feb 05 '20 at 15:55
  • @MartinSmith In this case, there is no outer value passed in. I can see no reason at all why the derived query would run more than once. So the answer to the question in the title would be "no, not in this case". Unless I'm missing something. Of course, an execution plan would confirm this. – bornfromanegg Feb 05 '20 at 15:55
  • yes you are missing something - the concept of outer references in a nested loops join – Martin Smith Feb 05 '20 at 15:57
  • Sorry, query is actually a left outer join, not inner- have updated question. Does this change the answers? – Aww_Geez Feb 05 '20 at 16:03
  • No, a left join shouldn't change anything. If you have an indexes on those ids then you'll probably get something like a merge join which only passes through the tables a single time. – shawnt00 Feb 05 '20 at 17:19
  • I checked the execution plan -- I'd post it here but I cannot disclose anything that can hint at the database schema of my employer. In my case it was only executing once. Thank you @Cedersved for telling me what to look for in the plan. – Aww_Geez Feb 05 '20 at 17:21

0 Answers0