>This part bogs down our entire system. Somehow if needs to limit to matching Table a part no only instead of looking at 200,000 from Table b at every iteration of Table a.
>
>

>
>(select PartNo, max(date) as MaxDate from TableB group by PartNo)
>
>

Is this particular query too expensive?

Try another variation then

select A.*, B.RevisionLevel, B.Date from
TableA A inner join (select top 1 PartNo, Date, RevisionLevel from TableB order by Date DESC) B
 on A.PartNo = B.PartNo
where A.RevisionLevel <> B.RevisionLevel