Wow. I'm amazed that runs in 54 seconds.
Without reading every single line, the one thing I'm noticing is, you don't really have the concept of JOINS down. Take this:
SELECT ID FROM PORTAL_USER WHERE ID IN
(SELECT DESCENDANT_USER_ID FROM PORTAL_USER_HIERARCHY WHERE
(SELECT TO_USER_ID FROM PORTAL_USER_RELATIONSHIPS
WHERE FROM_USER_ID IN
(SELECT DESCENDANT_USER_ID FROM PORTAL_USER_HIERARCHY WHERE ANCESTOR_USER_ID IN
(SELECT ID FROM PORTAL_USER WHERE [USER_NAME] = @USER_NAME)) --AND RELATION_TYPE_ID= 2
) )--AND IS_OUTSTANDING = 0 changed because sales user could not see saved requests defect id 968
You'd be better off with something like this, which is much more readable as well as more likely to get good indexes
FROM PORTAL_USER pu
JOIN PORTAL_USER_HIERARCHY puh
ON pu.ID = puh.DESCENDANT_USER_ID
JOIN PORTAL_USER_RELATIONSHIPS pur
ON puh.ANCESTOR_USER_ID = pur.TO_USER_ID
The same thing for all those sub-SELECTS within the WHERE clause.
Do you have an actual execution plan for the query? That will show you where you're getting table or index scans instead of seeks. This can be caused by the code (entirely possible) or by improper indexes.
Also, since you say it has degraded over time, have you updated statistics on these tables and defragmented the indexes?
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore RooseveltThe Scary DBA
Author of: SQL Server Query Performance Tuning
and SQL Server Execution Plans
Product Evangelist for Red Gate Software