SQL Server Performance Forum – Threads Archive

Need some new ideas for my SP…

Hi! I have this SP:
declare @t1 table(some columns) INSERT INTO @t1
SELECT
columns
FROM
users t1
LEFT JOIN userTexts t2 ON t1.userID = t2.userID AND t2.category = 4
LEFT JOIN blocked t3 ON t1.userID = t3.blockedUserID AND t3.userID = @userID
LEFT JOIN pictures t4 ON t4.userid=t1.userID
WHERE
t1.sex = case @sex when ” then t1.sex else @sex end
AND t1.anv_namn like @username+’%’
AND (t1.age>= case @age1 when 0 then 0 else @age1 end) AND (t1.age <= case @age2 when 0 then 100 else @age2 end)
AND isnull(t4.userID,0) = case @pp when 1 then t4.userID else isnull(t4.userID,0) end
SET NOCOUNT OFF if len(@searchString)>4
begin
if @style = 1
select SomeColumns FROM @t1 t1 INNER JOIN users t2 ON t1.userID=t2.userID WHERE FreeText(t2.presentation, @searchString) ORDER BY t1.username
else
select SomeColumns FROM @t1 t1 INNER JOIN userTexts t2 ON t1.userID=t2.userID WHERE FreeText(rum_text, @searchString) ORDER BY t1.username
end
else
begin
if @random = 1
select SomeColumns FROM @t1 ORDER BY newID()
else
select SomeColumns FROM @t1 ORDER BY anv_namn
end When running this query:
@userID = 123
@searchString = ‘someText’
@age1 = 0
@age2 = 0
@style = 2
@pp = 0
@random = 0
@username = ” I get these results..

quote:Table ‘#06DE8A94’. Scan count 0, logical reads 22266, physical reads 0, read-ahead reads 0.
Table ‘blocked’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 7.
Table ‘userTexts’. Scan count 22265, logical reads 70979, physical reads 905, read-ahead reads 133.
Table ‘pictures’. Scan count 22265, logical reads 44530, physical reads 2, read-ahead reads 0.
Table ‘users’. Scan count 1, logical reads 4555, physical reads 2, read-ahead reads 4567. Table ‘userTexts’. Scan count 22265, logical reads 68037, physical reads 0, read-ahead reads 0.
Table ‘#06DE8A94’. Scan count 1, logical reads 160, physical reads 0, read-ahead reads 0.

Some information about the tables: users has 48769 rows
userTexts has 186168 rows
pictures has 16138 rows
blocked has 9816 rows So as you can see the tables are rather small, but just to big for the query to run smooth. I think my indexes are as good as they can get since all the joins are on userID. I am running out of ideas of how to tune this query. I think I need to do some radical change to improve the performance. I hope that you can help me with some brainstorming on this. If you got some idea while reading this, please share! Thanks!

First guess: you need an index on UserID in the @t1 table, or a PK with UserID as the first column. Sorry, not sure how to implement that with a table variable.

Have you traced proc step by step? Which step takes the most time?

as mmarovic mentioned tracing the proc step by step to check each statement is useful. Use SQLProfielr to do this. also your proc will suffer from a classic problem in SQL which is that the parameters passed to a proc are used to build the optimal plan. If subsequent parameter values change what the optimal plan would be then SQL will now use a suboptimal plan. This can be guarded against by splitting the proc up In your example I would consider having 5 procs, one for each final select statement, plus one to wrap these four up into a single call for the application AND not to use a table variable as an intermediare table, but collapse that select into each of the four procs e.g. the 2nd case (which is the one you have given parameters for) I would make SELECT
columns
FROM
users t1
LEFT JOIN userTexts t2 ON t1.userID = t2.userID AND t2.category = 4
LEFT JOIN blocked t3 ON t1.userID = t3.blockedUserID AND t3.userID = @userID
LEFT JOIN pictures t4 ON t4.userid=t1.userID
WHERE
(@sex = ” OR t1.sex = @sex )
AND t1.anv_namn like @username
AND t1.age between @age1 AND @age2
AND isnull(t4.userID,0) = case @pp when 1 then t4.userID else isnull(t4.userID,0) end
AND FreeText(rum_text, @searchString)
order by t.username then making sure that there are non-clustered indexes on
users( userID )
users( anv_namn, age, sex, userID )
users( age, anv_namn, sex, userID )
userTexts( userID, category {plus any other columns that are in the select part} )
blocked( blockedUserId, userID {plus any other columns that are in the select part} )
pictures( userID {plus any other columns that are in the select part} )
and also consider from the wrapping proc, calling each of the four sub-procs using a with recompile clause i.e. create proc wrapper( … )
as
set nocount on if @age2 = 0
Set @age2 = 100 set @username = @username + ‘%’ if len( @searchString ) > 4
if @style = 1
exec sub_proc1 … with recompile
else
exec sub_proc2 … with recompile
else
if @random = 1
exec sub_proc3 … with recompile
else
exec sub_proc4 … with recompile
end go Cheers
Twan

Thanks! Twan: Of course they should be split up, never thought of it like that… Thanks! I have one question though… users( anv_namn, age, sex, userID ) users( age, anv_namn, sex, userID ) Why should I create the index like that, it seems to me that they are booth the same? Was that a typo? Adriaan: I will look into that, maybe my last join will perform better then, but my first select should only return like 0 to 200 rows, but I will give it a check <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> What do you think of the freetext? I have tried to read articles/faq and such but I cannot find any performance tips for freetext.

Hi ya, no it aint a typo. The order of the leading columns being different allows sql to use the first if the parameter to search on anv_namn is more selective, and the second if the age range is more selective. in terms of freetext I don’t know of any performance improvements, other than perhaps look at memory usage on the server with perfmon, as you may have to limit SQL to all RAM except say a couple of 100MB to give the full text search some space to do its thing in…? Cheers
Twan

]]>