If the query is taking longer than expected, I would do couple of diagnostic tests first.
1. Change the inner query to do a TOP (N) rows and see if that completes quickly.
select top 1 searchname, visit, L_Name, apptstart, ORD_DATE, OB_DATE, apptstop
select TOP (10) row_number() over (partition by pp.searchname order by l.name DESC) AS Seq,
pp.searchname, max(pv.visit)as visit,l.name as L_Name, ap.apptstart, ord.orderdate as ORD_DATE, ob.obsdate as OB_DATE, ap.apptstop
from patientprofile pp
2. See how many rows there are in each of your tables. That should give you a sense of what you are up against. Rather than use the usual count(*) to find the number of rows, use:
WHERE OBJECT_NAME(OBJECT_ID) = 'YourTableNameHere'
3. Focus on the tables with large number of rows. See if they have proper indexes on the join columns and/or columns in the where clauses.
edit: nevermind i found the solution