SP getting bad execution plan...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP getting bad execution plan...

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-06-05 : 17:22:05

I have a stored procedure to display recent Snitz forum posts for a user:


CREATE PROCEDURE p_snitz_RecentTopics (@iUsers int,@sdStartDate smalldatetime=NULL,@iTenOnly tinyint=0)  AS
set nocount on
DECLARE @sdStart smalldatetime,@iPosts int
select @iPosts=IsNull(m_posts,0) from snitz_members_supp where i_users=@iUsers
if @iPosts>0
  if @iTenOnly=0
    BEGIN
    set @sdStart=@sdStartDate
    if @sdStart is null
      set @sdStart=dateadd(day,-30,getdate())
    SELECT distinct SNITZ_FORUM.FORUM_ID,SNITZ_FORUM.F_SUBJECT,SNITZ_FORUM.CAT_ID, SNITZ_TOPICS.TOPIC_ID,SNITZ_TOPICS.T_SUBJECT,SNITZ_TOPICS.T_STATUS,SNITZ_TOPICS.T_LAST_POST,SNITZ_TOPICS.T_REPLIES
     FROM ((SNITZ_FORUM LEFT JOIN SNITZ_TOPICS ON SNITZ_FORUM.FORUM_ID=SNITZ_TOPICS.FORUM_ID)
         LEFT JOIN SNITZ_REPLY ON SNITZ_TOPICS.TOPIC_ID = SNITZ_REPLY.TOPIC_ID) 
     WHERE (T_DATE > dbo.f_frnk_util_SnitzifyDate(@sdStart))
        AND (SNITZ_TOPICS.T_AUTHOR = @iUsers  OR SNITZ_REPLY.R_AUTHOR = @iUsers)
     ORDER BY SNITZ_TOPICS.T_LAST_POST DESC, SNITZ_TOPICS.TOPIC_ID DESC
    END
  Else
  SELECT distinct top 10 SNITZ_FORUM.FORUM_ID,SNITZ_FORUM.F_SUBJECT,SNITZ_FORUM.CAT_ID, SNITZ_TOPICS.TOPIC_ID,SNITZ_TOPICS.T_SUBJECT,SNITZ_TOPICS.T_STATUS,SNITZ_TOPICS.T_LAST_POST,SNITZ_TOPICS.T_REPLIES
   FROM ((SNITZ_FORUM LEFT JOIN SNITZ_TOPICS ON SNITZ_FORUM.FORUM_ID=SNITZ_TOPICS.FORUM_ID)
       LEFT JOIN SNITZ_REPLY ON SNITZ_TOPICS.TOPIC_ID = SNITZ_REPLY.TOPIC_ID) 
   WHERE SNITZ_TOPICS.T_AUTHOR = @iUsers
       OR SNITZ_REPLY.R_AUTHOR = @iUsers
   ORDER BY SNITZ_TOPICS.T_LAST_POST DESC, SNITZ_TOPICS.TOPIC_ID DESC
Else 
  Select NULL as FORUM_ID
GO

...as you can see, it can either display the top 10 results or all of the results. Displaying all of the results works fine.

Displaying the top 10 results by calling

exec dbo.p_snitz_RecentTopics @iUsers=1,@iTenOnly=1

...results in a query cost of 1.68. Displaying those same results by calling

exec dbo.p_snitz_RecentTopics @iUsers=1,@iTenOnly=1 WITH RECOMPILE

...results in a query cost of .391. Executing the relevant SQL by itself gets the same .391 cost.

What's wrong with my SP? Should WITH RECOMPILE force subsequent uses to use that same (good) query plan? The key difference seems to be nested loops on the fast version versus a hash match/outer join on the slow one.

Any ideas?

Thanks
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources