By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,696 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,696 IT Pros & Developers. It's quick & easy.

reduce time for search query

P: n/a
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

Feb 26 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Tue, 26 Feb 2008 04:41:24 -0800 (PST), pa*******@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
Feb 26 '08 #2

P: n/a
On Feb 27, 3:22 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
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.

--
Feb 27 '08 #3

P: n/a
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
Feb 27 '08 #4

P: n/a
On Wed, 27 Feb 2008 02:11:16 -0800 (PST), ja*******@gmail.com wrote:
>On Feb 27, 3:22 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
>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
Feb 27 '08 #5

P: n/a
(ja*******@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, es****@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
Mar 8 '08 #6

P: n/a
http://www.cogitoinc.com/

Use the right tool for the job.

Mar 9 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.