July 14, 2003 at 12:19 pm
hi.. I have noticed that when I run the following stored procedure the query times out... this is not surprising because of the number of joins and the millions of records there are to keep track of... Is there any way you can help me optimize this stored procedure, specifically is there any way to break up a select distinct statement in such a way as to only query the 1st 100 records, for example?
INSERT INTO @tbl_temp
SELECT DISTINCT ui.UserID AS UserID,Gender,Birthdate,Country,State,
City,Hair,Eye,Height,Headline,DateDiff(day, Convert(datetime, MemberSince, 112), GETDATE()) AS DtDifference,
ReplyRate,LessThan11,Photo
FROM UserInfo ui WITH (NOLOCK)
LEFT OUTER JOIN UserDetails ud ON ud.UserID = ui.UserID
LEFT OUTER JOIN FlirtUserDescription fud ON fud.UserID = ui.UserID
LEFT OUTER JOIN FlirtUserActivity fua ON fua.UserID = ui.UserID
LEFT OUTER JOIN FlirtUserMusic fum ON fum.UserID = ui.UserID
LEFT OUTER JOIN FlirtUserSports fus ON fus.UserID = ui.UserID
WHERE
( ( @AgeStart IS NULL AND @AgeEnd IS NULL ) OR
Birthdate <= @StartDate AND
Birthdate > @EndDate)
AND
( Gender = ( CASE WHEN @Gender <> 'B' THEN @Gender ELSE Gender END ) OR
( @Gender = 'B' AND Gender IS NULL ) )
AND
( Photo = ( CASE WHEN @Photo IS NOT NULL THEN @Photo ELSE 0 END ) OR
( @Photo IS NULL AND Photo IS NULL ) )
AND
( Country = ( CASE WHEN @Country IS NOT NULL THEN @Country ELSE Country END ) OR
( @Country IS NULL AND Country IS NULL ) )
AND
( State = ( CASE WHEN @State IS NOT NULL THEN @State ELSE State END ) OR
( @State IS NULL AND State IS NULL ) )
AND
( City = ( CASE WHEN @City IS NOT NULL THEN @City ELSE City END ) OR
( @City IS NULL AND City IS NULL ) )
AND
( AdultContent = ( CASE WHEN @AdultContent IS NOT NULL THEN @AdultContent ELSE AdultContent END ) OR
( @AdultContent IS NULL AND AdultContent IS NULL ) )
AND
( Height >= ( CASE WHEN @HeightStart IS NOT NULL THEN @HeightStart ELSE 0 END ) OR
( @HeightStart IS NULL AND Height IS NULL ) )
AND
( Height <= ( CASE WHEN @HeightEnd IS NOT NULL THEN @HeightEnd ELSE 300 END ) OR
( @HeightEnd IS NULL AND Height IS NULL ) )
AND
( Hair = ( CASE WHEN @Hair IS NULL OR @Hair = '' THEN Hair END ) OR
CHARINDEX (',' + CAST(Hair AS varchar) + ',', ',' + @Hair + ',' ) > 0 OR
( @Hair IS NULL AND Hair IS NULL ) )
AND
( Body = ( CASE WHEN @Body IS NULL OR @Body = '' THEN Body END ) OR
CHARINDEX (',' + CAST(Body AS varchar) + ',', ',' + @Body + ',' ) > 0 OR
( @Body IS NULL AND Body IS NULL ) )
AND
( Language = ( CASE WHEN @Language IS NULL OR @Language = '' THEN Language END ) OR
CHARINDEX (',' + CAST(Language AS varchar) + ',', ',' + @Language + ',' ) > 0 OR
( @Language IS NULL AND Language IS NULL ) )
AND
( Eye = ( CASE WHEN @Eye IS NULL OR @Eye = '' THEN Eye END ) OR
CHARINDEX (',' + CAST(Eye AS varchar) + ',', ',' + @Eye + ',' ) > 0 OR
( @Eye IS NULL AND Eye IS NULL ) )
AND
( Ethnicity = ( CASE WHEN @Ethnicity IS NULL OR @Ethnicity = '' THEN Ethnicity END ) OR
CHARINDEX (',' + CAST(Ethnicity AS varchar) + ',', ',' + @Ethnicity + ',' ) > 0 OR
( @Ethnicity IS NULL AND Ethnicity IS NULL ) )
AND
( Religion = ( CASE WHEN @Religion IS NULL OR @Religion = '' THEN Religion END ) OR
CHARINDEX (',' + CAST(Religion AS varchar) + ',', ',' + @Religion + ',' ) > 0 OR
( @Religion IS NULL AND Religion IS NULL ) )
AND
( Drinking = ( CASE WHEN @Drinking IS NULL OR @Drinking = '' THEN Drinking END ) OR
CHARINDEX (',' + CAST(Drinking AS varchar) + ',', ',' + @Drinking + ',' ) > 0 OR
( @Drinking IS NULL AND Drinking IS NULL ) )
AND
( Smoking = ( CASE WHEN @Smoking IS NULL OR @Smoking = '' THEN Smoking END ) OR
CHARINDEX (',' + CAST(Smoking AS varchar) + ',', ',' + @Smoking + ',' ) > 0 OR
( @Smoking IS NULL AND Smoking IS NULL ) )
AND
( fua.AttributeID = ( CASE WHEN @interests IS NULL OR @interests = '' THEN fua.AttributeID END) OR
CHARINDEX(',' + CAST(fua.AttributeID AS varchar) + ',', ',' + @interests + ',' ) > 0 OR
( @interests IS NULL AND fua.AttributeID IS NULL ) )
AND
( fud.AttributeID = ( CASE WHEN @Personality IS NULL OR @Personality = '' THEN fud.AttributeID END) OR
CHARINDEX (',' + CAST(fud.AttributeID AS varchar) + ',', ',' + @Personality + ',' ) > 0 OR
( @Personality IS NULL AND fud.AttributeID IS NULL) )
AND
( fum.AttributeID = ( CASE WHEN @music IS NULL OR @music = '' THEN fum.AttributeID END) OR
CHARINDEX (',' + CAST(fum.AttributeID AS varchar) + ',', ',' + @music + ',' ) > 0 OR
( @music IS NULL AND fum.AttributeID IS NULL) )
AND
( fus.AttributeID = ( CASE WHEN @Sports IS NULL OR @Sports = '' THEN fus.AttributeID END) OR
CHARINDEX (',' + CAST(fus.AttributeID AS varchar) + ',', ',' + @Sports + ',' ) > 0 OR
( @Sports IS NULL AND fus.AttributeID IS NULL) )
AND
Birthdate <= dateadd(yyyy,-18,getdate())
Thanks so much for your help,
Anne
Anne
July 14, 2003 at 12:43 pm
Ann,
Have you tried looking at the Execution plan generated by this procedure. I would gues that the JOINs are the least of your worries if there is a proper index on UserID in each of the child tables. That WHERE clause is going to kill this procedure in a heartbeat. Can you post the results of SET STATISTICS IO ON and SET SHOWPLAN_TEXT ON with your procedure?
July 15, 2003 at 1:30 am
Seems to me like you're checking on different parameters that you pass into your procedure.
One way to speed things up is to write separate procedures for distinct combinations of parameters. This way you can get rid of a hoist of CASE and OR statements in the WHERE clause.
Looking at the sheer number of parameters, there is no way to write a separate procedure for each and every combination. So try to predict / assume / measure which of the combinations are most common, and give them a separate procedure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply