Hi,

I have a task at hand to reduce the time taken for search query to
execute. The query fetches records which will have to sorted by
degrees away from the logged in user. I have a function which
calculates the degrees, but using this in the search query slows the
execution and takes about 10 secs to complete which is unacceptable.

Please advice. Your help is much appreciated

For more details plz see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021


Thanks
Isfaar

On Tue, 26 Feb 2008 04:41:24 -0800 (PST), paankhate@googlemail.com
wrote:

> Hi,
>
>I have a task at hand to reduce the time taken for search query to
>execute. The query fetches records which will have to sorted by
>degrees away from the logged in user. I have a function which
>calculates the degrees, but using this in the search query slows the
>execution and takes about 10 secs to complete which is unacceptable.
>
>Please advice. Your help is much appreciated
>
>For more details plz see:
>
>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021

Hi Isfaar,

I see lots of useful (and some less useful) idea posted to that topic
over at sqlteam.com. Did you already try them? Did they help?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Feb 27, 3:22 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Tue, 26 Feb 2008 04:41:24 -0800 (PST), paankh...@googlemail.com
> wrote:
>
> > Hi,
>
> >I have a task at hand to reduce the time taken for search query to
> >execute. The query fetches records which will have to sorted by
> >degrees away from the logged in user. I have a function which
> >calculates the degrees, but using this in the search query slows the
> >execution and takes about 10 secs to complete which is unacceptable.
>
> >Please advice. Your help is much appreciated
>
> >For more details plz see:
>
> >http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021
>
> Hi Isfaar,
>
> I see lots of useful (and some less useful) idea posted to that topic
> over at sqlteam.com. Did you already try them? Did they help?
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Yes we have tried most of them, however, we did not get the required
results.

We are at present trying out to have a separate table to store all
relationships between the members, then query this table to get
results.

Will update our findings.

--

On Feb 26, 7:41*am, paankh...@googlemail.com wrote:
> *Hi,
>
> I have a task at hand to reduce the time taken for search query to
> execute. The query fetches records which will have to sorted by
> degrees away from the logged in user. I have a function which
> calculates the degrees, but using this in the search query slows the
> execution and takes about 10 secs to complete which is unacceptable.
>
> Please advice. Your help is much appreciated
>
> For more details plz see:
>
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021
>
> Thanks
> Isfaar

Hi Isfaar,
Would it be possible for you to post your query here along with udf
definition. When a scalar UDF is invoked for each row in a query, it
can degrade the performance i.e. for each row you incur UDF invocation
cost. An inline expression might be faster than UDF in this case.

Depending on your SQL Server version, you might have to adopt
different approach. If you are on SQL Server 2005, then cross apply
operator in conjuction with inline table function will do the trick
for you. Inline table function behaves differently than scalar
function.

If you are on SQL Server 2000, a precisely written subquery might do
the trick.

HTH,

Najm

On Wed, 27 Feb 2008 02:11:16 -0800 (PST), jan.afzal@gmail.com wrote:

>On Feb 27, 3:22 am, Hugo Kornelis
><h...@perFact.REMOVETHIS.info.INVALID> wrote:
>> I see lots of useful (and some less useful) idea posted to that topic
>> over at sqlteam.com. Did you already try them? Did they help?
(...)
>Yes we have tried most of them, however, we did not get the required
>results.

Hi jan.afzal,

There is at least one you did not try yet. Posted by Peso:

"Yes. Why don't you give us the present code for review?
Or do you want us to guess?"

Najm already posted a similar request here. I really can't add anything
to what is suggested in that thread without knowing the actual code.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

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 Ratchev
http://www.SQLStudio.com

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.

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

(jan.afzal@gmail.com) writes:
> 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@sommarskog.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

http://www.cogitoinc.com/

Use the right tool for the job.