Assistance Requested Building View Query

The View SQL below takes 1:50 minutes to execute. I am looking for a way to improve processing time and keep the view select statements as simple as possible.

One part of the view that could be restructured is the lookup for the current term (TERM_TBL - bolded). When I hard code the current term my execution time drops to about 13 seconds

There are three possible values for the current term; one for each of our three acedemic careers (UGRD, CONT, & EXED). Could this information be looked up once and then each enrollment selected based upon the appropriate academic career current term value? If so, how would it be restructured? Currently, the lookup is done for every enrollment record.

There are no common fields between the NAMES table and the TERMS_TBL.





Code Snippet

SELECT A.EMPLID, A.LAST_NAME_SRCH, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME

FROM NAMES A

WHERE A.EMPLID IN (


SELECT DISTINCT B.EMPLID

FROM STDNT_ENRL B

WHERE B.INSTITUTION = 'AAAAA'

AND B.STRM >= (

SELECT DISTINCT T.STRM
FROM TERM_TBL T
WHERE T.INSTITUTION = B.INSTITUTION
AND T.ACAD_CAREER = B.ACAD_CAREER
AND T.TERM_BEGIN_DT <= GETDATE()
AND T.TERM_END_DT >= GETDATE())

AND A.EMPLID NOT IN (

SELECT DISTINCT C.EMPLID
FROM SRVC_IND_DATA C
WHERE C.INSTITUTION = 'AAAAA'
AND C.SRVC_IND_CD = 'DPL' )

AND A.NAME_TYPE = 'PRI'

AND A.EFFDT = (

SELECT MAX(D.EFFDT)

FROM NAMES D

WHERE D.EMPLID = A.EMPLID

AND D.NAME_TYPE = 'PRI'

AND D.EFFDT <= GETDATE() )


Any suggestions will be gladly accepted. Keep in mind that the new and much improved must be legal within the context of a view select statement.


Thanks,
Steve