Selecting Only One Record with Most Recent Date Using Table Join

datejoin;teradata

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD.

I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the table name is jjenkins_carriers.

The BILL_DT is in a database named edw_ar_fl and table named ARTBASE. I wrote the query below in hopes of returning a single row of data including CARRIER_CD, BILL_DT, and BILL_AMT for the most recent BILL_DT associated with each of the unique CARRIER_CD. However, the query returns 408+ million rows. Any help would be appreciated.

SELECT edw_ar_fl.ARTBASE.CARRIER_CD,
    edw_ar_fl.ARTBASE.BILL_DT,
    edw_ar_fl.ARTBASE.BILL_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_carriers
         ON edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD
AND edw_ar_fl.ARTBASE.BILL_DT = 
     (select max(edw_ar_fl.ARTBASE.BILL_DT)
      where edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD)

Best Answer

The simplest way to select the last row (by bill_dt) in each group of rows with the same carrier_cd in Teradata would probably be using QUALIFY:

QUALIFY 1 = ROW_NUMBER() OVER (PARTITION BY carrier_cd ORDER BY bill_dt DESC)

If you're sure there are no ties (i.e. there's only one row with bill_dt = MAX(bill_dt) for each carrier_cd), you could calculate the max date for each carrier you're interested in:

sel carrier_cd, max(bill_dt) as max_bill_dt
from ARTBASE join CARRIERS on ARTBASE.carrier_cd = CARRIERS.carrier_cd
group by 1

...and then join it back to the original table:

sel ARTBASE.carrier_cd, ARTBASE.bill_dt, ARTBASE.bill_amt
from ARTBASE
join ( the above select here ) CARRIERS_STAT
  on ARTBASE.bill_dt = CARRIERS_STAT.max_bill_dt

In general I try to avoid the correlated subqueries, especially in a join condition. I'm not even sure how it works at all in the example you provided.

Query Plan Analysis

The query you have now is:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n;

This generates the rather inefficient plan:

Default plan

The main problems in this plan are the hash join and sort. Both require a memory grant (the hash join needs to build a hash table, and the sort needs room to store the rows while sorting progresses). Plan Explorer shows this query was granted 765 MB:

Memory Grant

This is quite a lot of server memory to dedicate to one query! More to the point, this memory grant is fixed before execution starts based on row count and size estimates.

If the memory turns out to be insufficient at execution time, at least some data for the hash and/or sort will be written to physical tempdb disk. This is known as a 'spill' and it can be a very slow operation. You can trace these spills (in SQL Server 2008) using the Profiler events Hash Warnings and Sort Warnings.

The estimate for the hash table's build input is very good:

Hash Input

The estimate for the sort input is less accurate:

Sort Input

You would have to use Profiler to check, but I suspect the sort will spill to tempdb in this case. It is also possible that the hash table spills too, but that is less clear-cut.

Note that the memory reserved for this query is split between the hash table and sort, because they run concurrently. The Memory Fractions plan property shows the relative amount of the memory grant expected to be used by each operation.

Why Sort and Hash?

The sort is introduced by the query optimizer to ensure that rows arrive at the Clustered Index Update operator in clustered key order. This promotes sequential access to the table, which is often much more efficient than random access.

The hash join is a less obvious choice, because it's inputs are similar sizes (to a first approximation, anyway). Hash join is best where one input (the one that builds the hash table) is relatively small.

In this case, the optimizer's costing model determines that hash join is the cheaper of the three options (hash, merge, nested loops).

Improving Performance

The cost model does not always get it right. It tends to over-estimate the cost of parallel merge join, especially as the number of threads increases. We can force a merge join with a query hint:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (MERGE JOIN);

This produces a plan that does not require as much memory (because merge join does not need a hash table):

Merge Plan

The problematic sort is still there, because merge join only preserves the order of its join keys (tempId, n) but the clustered keys are (tempId, n, sporder). You may find the merge join plan performs no better than the hash join plan.

Nested Loops Join

We can also try a nested loops join:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (LOOP JOIN);

The plan for this query is:

Serial Nested Loops Plan

This query plan is considered the worst by the optimizer's costing model, but it does have some very desirable features. First, nested loops join does not require a memory grant. Second, it can preserve the key order from the Persons table so that an explicit sort is not needed. You may find this plan performs relatively well, perhaps even good enough.

Parallel Nested Loops

The big drawback with the nested loops plan is that it runs on a single thread. It is likely this query benefits from parallelism, but the optimizer decides there is no advantage in doing that here. This is not necessarily correct either. Unfortunately, there is no built-in query hint to get a parallel plan, but there is an undocumented way:

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n
OPTION (LOOP JOIN, QUERYTRACEON 8649);

Enabling trace flag 8649 with the QUERYTRACEON hint produces this plan:

Parallel Nested Loops Plan

Now we have a plan that avoids the sort, requires no extra memory for the join, and uses parallelism effectively. You should find this query performs much better than the alternatives.

More information on parallelism in my article Forcing a Parallel Query Execution Plan:

Mysql – Selecting most recent date when dates are stored in another table

Please try the following.

If you need most recent date for every song over all meeting instances:

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
group by song_id

If you're interested only in particular meeting (meeting_id = 4):

select song_id, max(meeting_date)
from song_instances si
    join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id
where meeting_id = 4
group by song_id