Re: reduce time for search query

I do not see tables structure, indexes, the view definition, sample data, 
expected results...

Here is a quick attempt to improve this. Check the you have correct
indexes 
on join and filter columns where applicable.

In the main query below I removed two of the functions and used
sub-queries. 
Not sure that will help but was easier for me to see the logic. Changed
the 
main function to be table valued (you can go a step further here to make
it 
table valued for all users and join by user, I did not want to dig into
the 
logic there) so there will be no two calls in SELECT and WHERE, and some 
other cosmetic changes. Note that this is untested.

SELECT U.UserID,
          U.FirstName + ' ' + U.LastName AS 'Name',
          P.PropertyValue AS Location,
          N.headline AS Headline,
          I.industryName AS Industry,
          professionalInfo.summary AS Summary,
          professionalInfo.interests,
         (SELECT COUNT(*)
          FROM network AS N
          WHERE (N.memberId = U.UserID
               OR N.friendId = U.UserID)
             AND status = 1) AS Connections,
         (SELECT COUNT(*)
          FROM endorsements AS E
          WHERE E.memberId = U.UserID
             AND status = 'Accepted') AS Recommendations,
          F.generation AS Degree
FROM Users AS U
JOIN UserProfile AS P
  ON U.UserID = P.UserID
JOIN professionalInfo AS N
  ON U.UserID = N.memberId
JOIN industries AS I
  ON N.primaryIndustry = I.industryId
CROSS APPLY dbo.fnCommonFriendsStep(U.UserID, 36) AS F
WHERE EXISTS (SELECT *
                     FROM vw_search AS S
                     WHERE S.UserID = U.UserID)
  AND P.PropertyDefinitionID = 29
  AND F.generation >= 0
ORDER By Degree ASC



And here is the changed function:

CREATE FUNCTION dbo.fnCommonFriendsStep
 (@[EMAIL PROTECTED]
 INT, @[EMAIL PROTECTED]
 INT)
RETURNS @[EMAIL PROTECTED]
 (generation INT DEFAULT 0)
AS
BEGIN

INSERT INTO @[EMAIL PROTECTED]
 DEFAULT VALUES

IF @[EMAIL PROTECTED]
 = @[EMAIL PROTECTED]
 RETURN

DECLARE @[EMAIL PROTECTED]
 TABLE (generation INT, p INT)
DECLARE @[EMAIL PROTECTED]
 INT

SET @[EMAIL PROTECTED]
 = 0

INSERT @[EMAIL PROTECTED]
 (generation, p)
SELECT 0, memberId
FROM Network
WHERE friendId = @[EMAIL PROTECTED]
 0, friendId
FROM Network
WHERE memberId = @[EMAIL PROTECTED]
 NOT EXISTS (SELECT 1 FROM @[EMAIL PROTECTED]
 WHERE p = @[EMAIL PROTECTED]
)
   AND @[EMAIL PROTECTED]
 >= 0
BEGIN
SELECT @[EMAIL PROTECTED]
 = @[EMAIL PROTECTED]
 + 1

INSERT @[EMAIL PROTECTED]
 (generation, p)
SELECT @[EMAIL PROTECTED]
 memberId
FROM Network AS N
WHERE EXISTS (SELECT *
                     FROM @[EMAIL PROTECTED]
 AS F
                     WHERE N.friendid = F.p
                        AND F.generation = @[EMAIL PROTECTED]
 - 1)
    AND NOT EXISTS (SELECT *
                            FROM @[EMAIL PROTECTED]
 AS F
                            WHERE N.memberid = F.p)
UNION
SELECT @[EMAIL PROTECTED]
 friendId
FROM Network AS N
WHERE EXISTS (SELECT *
                     FROM @[EMAIL PROTECTED]
 AS F
                     WHERE N.memberid = F.p
                        AND F.generation = @[EMAIL PROTECTED]
 - 1)
   AND NOT EXISTS (SELECT *
                           FROM @[EMAIL PROTECTED]
 AS F
                           WHERE N.friendid = F.p)

IF @[EMAIL PROTECTED]
 = 0
SELECT @[EMAIL PROTECTED]
 = -2
END

UPDATE @[EMAIL PROTECTED]
 generation = @[EMAIL PROTECTED]
 + 1

RETURN
END



HTH,

Plamen Ratchev
http://www.SQLStudio.com