July 16, 2003 at 6:14 pm
this query takes 1 minute and a half to execute when taking the top 500... it's going to be way to slow when I run it against live data.... can anyone help me improve the performance. This seems to be my main issue this month... sigh.
DECLARE @interests varchar(250)
DECLARE @personality varchar(250)
DECLARE @Photo int
DECLARE @Country varchar(50)
DECLARE @State varchar(50)
DECLARE @City varchar(50)
DECLARE @AdultContent varchar(50)
DECLARE @music varchar(250)
DECLARE @Sports varchar(250)
DECLARE @Gender varchar(2)
DECLARE @Religion varchar(50)
DECLARE @Language varchar(50)
DECLARE @Drinking varchar(50)
DECLARE @Smoking varchar(50)
--SET @Religion='L070'
SET @Religion=''
set @Language=NULL
set @Drinking=NULL
set @Smoking=NULL
SET @interests='1,7'
SET @personality='12,18'
SET @Photo=NULL
SET @Country='R0010'
select top 500 userID,birthdate,replyrate,lessthan11,photo from userinfo where 1=1
AND (DATEDIFF(YEAR, birthdate, GETDATE() ) - CASE WHEN birthdate > GETDATE() THEN 1 ELSE 0 END)>18
AND
( Country = ( CASE WHEN @Country IS NOT NULL THEN @Country ELSE Country END ) )
AND
( State = ( CASE WHEN @State IS NOT NULL THEN @State ELSE State END ))
AND
( City = ( CASE WHEN @City IS NOT NULL THEN @City ELSE City END ) )
AND ( Gender = ( CASE WHEN @Gender <> 'B' THEN @Gender ELSE Gender END ))
AND
( Photo = ( CASE WHEN @Photo IS NOT NULL THEN @Photo ELSE 0 END ))
AND
(AdultContent = ( CASE WHEN @AdultContent IS NOT NULL THEN @AdultContent ELSE 0 END ))
AND( userID in(select top 3000 u.userID from userinfo u, FlirtUserDescription fua where fua.userid = u.userID
AND ( fua.AttributeID = ( CASE WHEN @Personality IS NULL OR @Personality = '' THEN fua.AttributeID END) OR
CHARINDEX(',' + CAST(fua.AttributeID AS varchar) + ',', ',' + @Personality + ',' ) > 0
)
)
OR (userID in(select top 3000 u.userID from userinfo u, FlirtUserSports fua where fua.userid = u.userID
AND ( fua.AttributeID = ( CASE WHEN @Sports IS NULL OR @Sports = '' THEN fua.AttributeID END) OR
CHARINDEX(',' + CAST(fua.AttributeID AS varchar) + ',', ',' + @Sports + ',' ) > 0
)
))
OR (userID in(select top 3000 u.userID from userinfo u, FlirtUserMusic fua where fua.userid = u.userID
AND ( fua.AttributeID = ( CASE WHEN @music IS NULL OR @music = '' THEN fua.AttributeID END) OR
CHARINDEX(',' + CAST(fua.AttributeID AS varchar) + ',', ',' + @music + ',' ) > 0
)
))
OR (userID in(select top 3000 u.userID from userinfo u, FlirtUserActivity fua where fua.userid = u.userID
AND ( fua.AttributeID = ( CASE WHEN @interests IS NULL OR @interests = '' THEN fua.AttributeID END) OR
CHARINDEX(',' + CAST(fua.AttributeID AS varchar) + ',', ',' + @interests + ',' ) > 0
)
))
OR (userID in(select u.userID from userinfo u,userdetails ud where ud.userid = u.userID
and ((ud.religion = ( CASE WHEN @Religion IS NULL OR @Religion = '' THEN ud.religion END ) OR
CHARINDEX (',' + CAST(ud.religion AS varchar) + ',', ',' + @Religion + ',' ) > 0 )
OR
(ud.language = ( CASE WHEN @Language IS NULL OR @Language = '' THEN ud.language END ) OR
CHARINDEX (',' + CAST(ud.language AS varchar) + ',', ',' + @Language + ',' ) > 0 )
OR
(ud.drinking = (CASE WHEN @Drinking IS NULL OR @Drinking = '' THEN ud.drinking END ) OR
CHARINDEX (',' + CAST(ud.drinking AS varchar) + ',', ',' + @Drinking + ',' ) > 0 )
OR
(ud.smoking = (CASE WHEN @Smoking IS NULL OR @Smoking = '' THEN ud.smoking END ) OR
CHARINDEX (',' + CAST(ud.smoking AS varchar) + ',', ',' + @Smoking + ',' ) > 0 )
)
))
)
Thanks,
Anne
Anne
July 17, 2003 at 9:51 am
One thing that may speed it up is the following:
When possible, use isnull rather than the case statement.
According to the execution plan, it is twice as fast.
ie.
replace this:
CASE WHEN @Country IS NOT NULL THEN @Country ELSE Country END
with:
isnull(@Country,Country)
additionally:
You may want to put your top 3000 select statements into temporary tables and join on them rather than putting them in the where clause.
I'm not sure about this, but I think that sometimes the select top 3000 may be executed for every row that is returned???? Maybe not. I guess it depends upon how well the execution plan can interpret the query? Anybody have any knowledge about this?
-J
July 17, 2003 at 10:05 am
jraha - Good call! Joining is faster than using IN statements and using a subquery DOES mean that it gets executed for each row.
By the way - does TOP mean anything here? The output does not seem to be ordered so why worry about the TOP?
Sidebar: TOP N - ORDER BY have bad side affects so this is a question of curiosity. See BOL for more clarity.
Guarddata
July 17, 2003 at 2:25 pm
Second that. If you can't join on the TOP 3000, try to rewrite it as an EXISTS clause. That should be faster than an IN statement.
If you still end up with a 'slow' query, try splitting up the query according to the parameters being passed in. This means a lot of copy/paste work (not to mention any changes you might need), but it will speed up the query. Point is to avoid OR in the WHERE clause, since that slows down too.
A third suggestion might be to split the parameters like @sports, @music etc. and add each item to a separate (temporary) table you can join against. This will avoid all the casting, concatenating and CHARINDEXING for each and every row.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply