reduce time for search query

#11

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
(@member INT, @friend INT)
RETURNS @FriendsDegree
TABLE (generation INT DEFAULT 0)
AS
BEGIN

INSERT INTO @FriendsDegree DEFAULT VALUES

IF @member = @friend
RETURN

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

SET @generation = 0

INSERT @Friends (generation, p)
SELECT 0, memberId
FROM Network
WHERE friendId = @member
UNION
SELECT 0, friendId
FROM Network
WHERE memberId = @member

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

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

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

UPDATE @FriendsDegree
SET generation = @generation + 1

RETURN
END

HTH,

Plamen Ratchev
http://www.SQLStudio.com

#12

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
(@member INT, @friend INT)
RETURNS @FriendsDegree
TABLE (generation INT DEFAULT 0)
AS
BEGIN

INSERT INTO @FriendsDegree DEFAULT VALUES

IF @member = @friend
RETURN

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

SET @generation = 0

INSERT @Friends (generation, p)
SELECT 0, memberId
FROM Network
WHERE friendId = @member
UNION
SELECT 0, friendId
FROM Network
WHERE memberId = @member

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

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

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

UPDATE @FriendsDegree
SET generation = @generation + 1

RETURN
END

HTH,

Plamen Ratchev
http://www.SQLStudio.com

#13

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
(@member INT, @friend INT)
RETURNS @FriendsDegree
TABLE (generation INT DEFAULT 0)
AS
BEGIN

INSERT INTO @FriendsDegree DEFAULT VALUES

IF @member = @friend
RETURN

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

SET @generation = 0

INSERT @Friends (generation, p)
SELECT 0, memberId
FROM Network
WHERE friendId = @member
UNION
SELECT 0, friendId
FROM Network
WHERE memberId = @member

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

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

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

UPDATE @FriendsDegree
SET generation = @generation + 1

RETURN
END

HTH,

Plamen Ratchev
http://www.SQLStudio.com

#14

(jan.afzal (AT) gmail (DOT) com) writes:

Quote:

Sorry for not having to replied earlier.

here is the code.

this is the query that gets fired;

Most likely what is killing you is all the calls to
fnCommonFriendsStep. (But if the vw_search view is complex, the reason
may be found there.)

I see that Plamen has offered a rewrite where he
has turned the function into a table-valued function, but will I have
to admit that I am skeptical that this will have any particular effect.

If you are lucky that these conditions:

WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

filter away a major share of the rows, say 90%, it may be suffcient
to do:

INSERT #temp(UserID, ....)
SELECT U.UserID, U.FirstName + ' ' + U.LastName as Name,
UP.PropertyValue as Location,
pI.headline as Headline,
i.industryName as Industry,
pI.summary as Summary,
pI.interests
FROM Users U
JOIN UserProfile ON U.UserID = UP.UserID
JOIN professionalInfo pI ON U.UserID = pI.memberId
JOIN industries i ON pI.primaryIndustry = i.industryId
WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

SELECT UserID, Name, Location, Headline, Industry, Summary, interests,
dbo.GetConnectionsCount(U.UserID) AS Connections,
dbo.GetRecommendations(U.UserID) AS Recommendations,
dbo.fnCommonFriendsStep(U.UserID, 36) AS Degree
FROM #temp
WHERE dbo.fnCommonFriendsStep(U.UserID, 36) >= 0

But if the call to dbo.fnCommonFriendsStep is the major filter, the above
is useless.

It is possible that you could replace the function with a recursive CTE.
No, I am not go to give you a sample, because I don't know your tables,
I don't know your business rules, and I don't have any sample data to
test with. And there are some unfortuate restrictions with recursive
CTEs which makes me uncertain that they can actually do the job.

If that does not help, the only remaining option is to materialise the
result of fnCommonFriendsStep to a table with the columns (User1, User2,
Degrees). How to maintain that table when a row is added, deleted or
update in the network table would be a new headache.

In summary, while we are some people out here that knows SQL Server well,
our expertise in the product as such is not sufficient to solve a
performance problem like this. We also need specific problem about
the problem at hand:

o What is the purpose of this query? More generally what is the context
for it?
o How often does a query of this type run?
o How common are updates? Partiularly, how common are updates to the
network table?
o CREATE VIEW for all views involved and CREATE TABLE and CREATE INDEX
for the tables involved, including those referred to by views and
function.
o Rowcounts for all involved tables.
o The query-plan for the query.
o Sample data to test solutions for correctness. (To test for performance
we would need more data that is practical to include a news post.)

Yes, it would take you some effort to compile this information, but
you are asking us to make a community to help you. If you are not
prepared to make that effort, should you really expect us to make any
effort?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#15

(jan.afzal (AT) gmail (DOT) com) writes:

Quote:

Sorry for not having to replied earlier.

here is the code.

this is the query that gets fired;

Most likely what is killing you is all the calls to
fnCommonFriendsStep. (But if the vw_search view is complex, the reason
may be found there.)

I see that Plamen has offered a rewrite where he
has turned the function into a table-valued function, but will I have
to admit that I am skeptical that this will have any particular effect.

If you are lucky that these conditions:

WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

filter away a major share of the rows, say 90%, it may be suffcient
to do:

INSERT #temp(UserID, ....)
SELECT U.UserID, U.FirstName + ' ' + U.LastName as Name,
UP.PropertyValue as Location,
pI.headline as Headline,
i.industryName as Industry,
pI.summary as Summary,
pI.interests
FROM Users U
JOIN UserProfile ON U.UserID = UP.UserID
JOIN professionalInfo pI ON U.UserID = pI.memberId
JOIN industries i ON pI.primaryIndustry = i.industryId
WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

SELECT UserID, Name, Location, Headline, Industry, Summary, interests,
dbo.GetConnectionsCount(U.UserID) AS Connections,
dbo.GetRecommendations(U.UserID) AS Recommendations,
dbo.fnCommonFriendsStep(U.UserID, 36) AS Degree
FROM #temp
WHERE dbo.fnCommonFriendsStep(U.UserID, 36) >= 0

But if the call to dbo.fnCommonFriendsStep is the major filter, the above
is useless.

It is possible that you could replace the function with a recursive CTE.
No, I am not go to give you a sample, because I don't know your tables,
I don't know your business rules, and I don't have any sample data to
test with. And there are some unfortuate restrictions with recursive
CTEs which makes me uncertain that they can actually do the job.

If that does not help, the only remaining option is to materialise the
result of fnCommonFriendsStep to a table with the columns (User1, User2,
Degrees). How to maintain that table when a row is added, deleted or
update in the network table would be a new headache.

In summary, while we are some people out here that knows SQL Server well,
our expertise in the product as such is not sufficient to solve a
performance problem like this. We also need specific problem about
the problem at hand:

o What is the purpose of this query? More generally what is the context
for it?
o How often does a query of this type run?
o How common are updates? Partiularly, how common are updates to the
network table?
o CREATE VIEW for all views involved and CREATE TABLE and CREATE INDEX
for the tables involved, including those referred to by views and
function.
o Rowcounts for all involved tables.
o The query-plan for the query.
o Sample data to test solutions for correctness. (To test for performance
we would need more data that is practical to include a news post.)

Yes, it would take you some effort to compile this information, but
you are asking us to make a community to help you. If you are not
prepared to make that effort, should you really expect us to make any
effort?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#16

(jan.afzal (AT) gmail (DOT) com) writes:

Quote:

Sorry for not having to replied earlier.

here is the code.

this is the query that gets fired;

Most likely what is killing you is all the calls to
fnCommonFriendsStep. (But if the vw_search view is complex, the reason
may be found there.)

I see that Plamen has offered a rewrite where he
has turned the function into a table-valued function, but will I have
to admit that I am skeptical that this will have any particular effect.

If you are lucky that these conditions:

WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

filter away a major share of the rows, say 90%, it may be suffcient
to do:

INSERT #temp(UserID, ....)
SELECT U.UserID, U.FirstName + ' ' + U.LastName as Name,
UP.PropertyValue as Location,
pI.headline as Headline,
i.industryName as Industry,
pI.summary as Summary,
pI.interests
FROM Users U
JOIN UserProfile ON U.UserID = UP.UserID
JOIN professionalInfo pI ON U.UserID = pI.memberId
JOIN industries i ON pI.primaryIndustry = i.industryId
WHERE U.UserID IN (SELECT DISTINCT UserID FROM vw_search)
AND UP.PropertyDefinitionID = 29

SELECT UserID, Name, Location, Headline, Industry, Summary, interests,
dbo.GetConnectionsCount(U.UserID) AS Connections,
dbo.GetRecommendations(U.UserID) AS Recommendations,
dbo.fnCommonFriendsStep(U.UserID, 36) AS Degree
FROM #temp
WHERE dbo.fnCommonFriendsStep(U.UserID, 36) >= 0

But if the call to dbo.fnCommonFriendsStep is the major filter, the above
is useless.

It is possible that you could replace the function with a recursive CTE.
No, I am not go to give you a sample, because I don't know your tables,
I don't know your business rules, and I don't have any sample data to
test with. And there are some unfortuate restrictions with recursive
CTEs which makes me uncertain that they can actually do the job.

If that does not help, the only remaining option is to materialise the
result of fnCommonFriendsStep to a table with the columns (User1, User2,
Degrees). How to maintain that table when a row is added, deleted or
update in the network table would be a new headache.

In summary, while we are some people out here that knows SQL Server well,
our expertise in the product as such is not sufficient to solve a
performance problem like this. We also need specific problem about
the problem at hand:

o What is the purpose of this query? More generally what is the context
for it?
o How often does a query of this type run?
o How common are updates? Partiularly, how common are updates to the
network table?
o CREATE VIEW for all views involved and CREATE TABLE and CREATE INDEX
for the tables involved, including those referred to by views and
function.
o Rowcounts for all involved tables.
o The query-plan for the query.
o Sample data to test solutions for correctness. (To test for performance
we would need more data that is practical to include a news post.)

Yes, it would take you some effort to compile this information, but
you are asking us to make a community to help you. If you are not
prepared to make that effort, should you really expect us to make any
effort?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx