The major issue here is that computing the set pta_recent is very inefficient, as the query is currently written. The rest of the query is largely irrelevant from a performance perspective.
If you are unable to change the current indexing, consider the following query in place of pta_recent:
SELECT
D.trc_number,
-- pivot columns:
first_pta = MAX(CASE WHEN CA.rn = 1 THEN CA.lph_id ELSE NULL END),
second_pta = MAX(CASE WHEN CA.rn = 2 THEN CA.lph_id ELSE NULL END),
third_pta = MAX(CASE WHEN CA.rn = 3 THEN CA.lph_id ELSE NULL END)
FROM
(
-- Find the distinct trc_number values using the index
SELECT DISTINCT
LH.trc_number
FROM dbo.legpta_history AS LH
WHERE
LH.trc_number > ''
) AS D
CROSS APPLY
(
-- For each trc_number, find up to 3 highest lph_id values
SELECT TOP (3)
rn = ROW_NUMBER() OVER (ORDER BY LH2.lph_id DESC),
LH2.lph_id
FROM dbo.legpta_history AS LH2
WHERE
LH2.trc_number = D.trc_number
ORDER BY
LH2.lph_id DESC
) AS CA
GROUP BY
D.trc_number;
This can use the existing indexing to obtain the set needed without any sorting:
This will be especially efficient if there are many lph_id per trc_number on average.
If you can change the indexing, consider replacing the existing index on trc_number with one on the same column, but sorted descending:
CREATE INDEX i ON dbo.legpta_history (trc_number DESC)
Then use this query:
SELECT
N.trc_number,
first_pta = MAX(CASE WHEN N.rn = 1 THEN N.lph_id ELSE NULL END),
second_pta = MAX(CASE WHEN N.rn = 2 THEN N.lph_id ELSE NULL END),
third_pta = MAX(CASE WHEN N.rn = 3 THEN N.lph_id ELSE NULL END)
FROM
(
SELECT
LH.lph_id,
LH.trc_number,
rn = ROW_NUMBER() OVER (PARTITION BY LH.trc_number ORDER BY LH.lph_id DESC)
FROM dbo.legpta_history AS LH
WHERE LH.trc_number > ''
) AS N
WHERE
N.rn <= 3
GROUP BY
N.trc_number;
Expected plan:
There are other refinements possible, including a filtered index, and/or keyed on (trc_number ASC, lph_id DESC) (to potentially allow a parallel plan).
