Improve the performance of a SQL Query via SQL Server

Question

I'm looking for improve the performance of the Last Year Attended query. Right now, its taking 20+ minutes to run this block.

The LYA take the most recent year attended for a particular event and finds the year they attended prior to the max. For example if they attended in 2018 for an event, the query will look for the last year attended prior to 2018.

LYA for 2018 should return a Null

The data should return the following:

CompanyID   MarketID    Industry    LAST YEAR ATTENDED
-------------------------------------------------------
123456      1234        GIFT        2018
123457      1234        HOME        2017
123458      1234        GIFT        2018
123459      1234        HOME        2018
123460      1234        APPAREL     2018
123461      1234        HOME        2018
123462      1234        HOME        2017
123463      1234        APPAREL     2018

Can anyone assist?

SELECT DISTINCT 
    COMPANYID, MARKETID, INDUSTRY,
    [LAST YEAR ATTENDED] = (SELECT MAX(YEAR(attdate))
                            FROM v_marketatt va
                            WHERE va.companyid = vm.companyid
                              AND YEAR(attdate) <> (SELECT MAX(YEAR(attdate))
                                                    FROM v_marketatt vb
                                                    WHERE vb.companyid = vm.companyid)
                              AND MARKETCODE LIKE 'SM1%')
FROM
    v_marketatt vm
WHERE 
    MARKETID IN (835, 1032, 1101)

UPDATE:

Found that is version is more efficient than the rest. Run time down to 7 minutes on a clone. Instead of allowing the subquery to dip into my view twice, had it dip once.

select
DISTINCT COMPANYID,
MARKETID,
INDUSTRY,
CSTATUS,
[LAST YEAR ATTENDED] = (select max(year(attdate)) from v_marketatt va where year(attdate) <> (select max(year(attdate)) from v_marketatt) AND MARKETCODE LIKE 'SM1%' AND va.COMPANYID = vm.COMPANYID)
from v_marketatt vm
WHERE MARKETID IN (835,1032,1101)
;

Thanks to all who responded.

Answer 1

The field [LAST YEAR ATTENDED] has a subquery that computes the max year on each iteration.You can try moving this piece of query to a join something like

    select DISTINCT COMPANYID, MARKETID, INDUSTRY,
    [LAST YEAR ATTENDED] 
    from v_marketatt vm
    inner join 
    ( select max(year(attdate)) as [LAST YEAR ATTENDED]
                    from v_marketatt ivm 
           where year(ivm.attdate) <> (select max(year(attdate))
                                            from v_marketatt vb
                                            where vb.companyid = 
                 ivm.companyid)
       AND MARKETCODE LIKE 'SM1%')va on va.companyid = vm.companyid
          --where companyid not in (select distinct companyid from 
      v_marketatt where marketid in (602))
       WHERE MARKETID IN (835,1032,1101)

I have not run this query , there could be some minor corrections on syntax , but if you get the concept it should be easy to pick and fix.