On Feb 28, 8:51*am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I can only repeat the same as Hugo and Najm, it is hard to say anything
> without seeing the actual table, indexes, sample data, required results, and
> business requirements/rules. Reading all those posts in the link at
> sqlteam.com I can see only sample code provided by Peso (Peter Larsson). The
> problem could be difficult or easy to solve, but hard to say without seeing
> the real requirements.
>
> Perhaps some things to consider are using materialized path or nested sets
> to stored the hierarchy of contacts. Those models provide very efficient
> retrieval of distance between nodes info, but have more difficult methods
> for maintaining data.
>
> Here is just a small sample (using the sample table and data provided by
> Peso) on how materialized path may look:
>
> -- Sample table with data
> CREATE TABLE Contacts (
> *c_from CHAR(1),
> *c_to CHAR(1),
> *PRIMARY KEY (c_from, c_to));
>
> INSERT INTO Contacts
> SELECT 'A', 'B' UNION ALL
> SELECT 'B', 'D' UNION ALL
> SELECT 'C', 'A' UNION ALL
> SELECT 'C', 'E' UNION ALL
> SELECT 'G', 'C' UNION ALL
> SELECT 'B', 'G' UNION ALL
> SELECT 'F', 'D' UNION ALL
> SELECT 'E', 'F';
>
> -- Table to store paths
> CREATE TABLE Paths (c_path VARCHAR(200) PRIMARY KEY);
>
> This is the real hurdle, recalculating all paths
>
> -- Recursive CTE to populate the paths
> WITH PathCTE
> AS
> (SELECT c_from, c_to,
> * * * * * *CAST('.' + CAST(c_from AS VARCHAR(8)) + '.' +
> * * * * * *CAST(c_to AS VARCHAR(8)) + '.' AS VARCHAR(200)) AS c_path
> *FROM Contacts AS C1
> *UNION ALL
> *SELECT C.c_from, C.c_to,
> * * * * * *CAST(P.c_path + C.c_to + '.' AS VARCHAR(200))
> *FROM PathCTE AS P
> *JOIN Contacts AS C
> * *ON P.c_to = C.c_from
> *WHERE P.c_path NOT LIKE '%.' +
> * * * * * * * * * * * * * * * * * * * CAST(C.c_from AS VARCHAR(10)) +
> * * * * * * * * * * * * * * * * * * * '.' +
> * * * * * * * * * * * * * * * * * * * CAST(C.c_to AS VARCHAR(10)) +
> * * * * * * * * * * * * * * * * * * * '.%')
> INSERT INTO Paths
> SELECT c_path FROM PathCTE;
>
> -- Show all paths between B and D
> SELECT c_path
> FROM Paths
> WHERE c_path LIKE '.B.%'
> * *AND c_path LIKE '%.D.';
>
> -- Shortest path distance, longest path distance, and number of paths
> SELECT MIN(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS
> shortest_distance,
> * * * * * MAX(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS
> longest_distance,
> * * * * * COUNT(*) AS paths_cnt
> FROM Paths
> WHERE c_path LIKE '.B.%'
> * *AND c_path LIKE '%.D.';
>
> Looking at the paths found:
> .B.D.
> .B.G.C.A.B.D.
> .B.G.C.E.F.D.
>
> You may notice the second path reused the first path to reach the
> destination. But this could be desired or not, again hard to say with no
> requirements. Easy to handle but did not bother...
>
> As you can see calculating the distance is easy, but maintenance offsets
> that. Based on your needs and model, if data is static this may do.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Sorry for not having to replied earlier.

here is the code.

this is the query that gets fired;

SELECT Users.UserID, Users.FirstName + ' ' + Users.LastName as Name,
UserProfile.PropertyValue as Location,
professionalInfo.headline as Headline, industries.industryName as
Industry, professionalInfo.summary as Summary,
professionalInfo.interests, dbo.GetConnectionsCount(Users.UserID) AS
Connections,
dbo.GetRecommendations(Users.UserID) AS Recommendations,
dbo.fnCommonFriendsStep(Users.UserID, 36) AS Degree FROM Users
INNER JOIN UserProfile ON Users.UserID = UserProfile.UserID
INNER JOIN professionalInfo ON Users.UserID =
professionalInfo.memberId
INNER JOIN industries ON professionalInfo.primaryIndustry =
industries.industryId
WHERE (Users.UserID IN (SELECT DISTINCT UserID FROM vw_search ))
AND UserProfile.PropertyDefinitionID=29 AND
dbo.fnCommonFriendsStep(Users.UserID,36) >=0 ORDER By Degree ASC


fnCommonFriendstep one calculates relationship between the loggedin
member and other members.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[fnCommonFriendsStep]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCommonFriendsStep]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.fnCommonFriendsStep
(
@Want1 INT,
@Want2 INT
)
RETURNS INT
AS

BEGIN
IF @Want1 = @Want2
RETURN 0

DECLARE @Friends TABLE (Generation INT, p INT)
DECLARE @Generation INT

SELECT @Generation = 0

INSERT @Friends
(
Generation,
p
)
SELECT 0,
memberId
FROM network
WHERE friendId = @Want1
UNION
SELECT 0,
friendId
FROM network
WHERE memberId = @Want1

WHILE NOT EXISTS (SELECT 1 FROM @Friends WHERE p = @Want2) AND
@Generation >= 0
BEGIN
SELECT @Generation = @Generation + 1

INSERT @Friends
(
Generation,
p
)
SELECT @Generation,
memberId
FROM network
WHERE friendId IN (SELECT p FROM @Friends WHERE Generation =
@Generation - 1)
AND memberId NOT IN (SELECT p FROM @Friends)
UNION
SELECT @Generation,
friendId
FROM network
WHERE memberId IN (SELECT p FROM @Friends WHERE Generation =
@Generation - 1)
AND friendId NOT IN (SELECT p FROM @Friends)

IF @@ROWCOUNT = 0
SELECT @Generation = -2
END

RETURN @Generation + 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


GetRecommendations get the number of recommendations a member has
received.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[GetRecommendations]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetRecommendations]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.GetRecommendations
(
@user_id as int
)
RETURNS INT
AS
BEGIN
DECLARE @recomm INT
SELECT @recomm=Count(*) FROM endorsements WHERE memberId=@user_id AND
status='Accepted'
RETURN @recomm
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


GetConnectionsCount fetches number of connections a member has


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[GetConnectionsCount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetConnectionsCount]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.GetConnectionsCount
(
@user_id as int
)
RETURNS INT
AS
BEGIN
DECLARE @recCount INT
SELECT @recCount=COUNT(*) FROM network WHERE (memberId=@user_id OR
friendId=@user_id) AND status=1
RETURN @recCount
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Please do let me know if you need any more information.