Ok, here's a tough query (snitz related)

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)

Ok, here's a tough query (snitz related)

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-30 : 13:09:59

(And I promise I won't snipe anyone this time!)

Anyways, I need a query that will show the last 10 posts a given snitz user has made. Actually, I have such a query, it just performs really poorly, and it has the annoying quirk that it lists the last 10 topics that *anyone* has posted to in which the user in question also posted. That is, rather than showing you where you can find a given user's last 10 posts, it shouls you the last 10 posts that have had any activity by anyone, to which the user in question posted at some point in the past.

As I see it, the real complication here comes down to a poor design decision in Snitz: the seperation of "topics" from "replies." The two tables are nearly identical, and should probably be merged into a single "posts" table. But I don't really have the time to do that redesign.

Here's the query I've got:

DECLARE @iUsers int
select @iUsers=1117714

  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 WITH(NOLOCK)
             LEFT JOIN SNITZ_TOPICS WITH(NOLOCK)
                ON SNITZ_FORUM.FORUM_ID=SNITZ_TOPICS.FORUM_ID
        )
         LEFT JOIN SNITZ_REPLY WITH(NOLOCK)
            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

...where @iUsers is the member we're looking up. I know "distinct" hurts performance, but I don't see any other way to eliminate duplicates when the user has posted multiple responses to a single topic.

Any advice appreciated; this query works, but between its performance issues and the slight deviation from desired results, I'd really like to rev it.

Cheers
-b

r937
Posting Yak Master

112 Posts

Posted - 2002-08-31 : 19:23:40

please allow me to preface this by saying that i am not a performance guy, in fact i'm not even what you'd call a DBA -- i design databases rather than tune them, and i've never seen the snitz design until today...

but i do hate to see a question sit there unanswered

so let's start with the last 10 posts made by the user, and make note of just the TOPIC_ID and the date

  select top 10 
         SNITZ_TOPICS.TOPIC_ID
       , SNITZ_TOPICS.T_LAST_POST   as last_author_date
    from SNITZ_TOPICS  
   where SNITZ_TOPICS.T_AUTHOR = @iUsers  
order by SNITZ_TOPICS.T_LAST_POST desc

and similarly, the last 10 replies

  select top 10 
         SNITZ_REPLY.TOPIC_ID
       , SNITZ_REPLY.R_LAST_POST    as last_author_date
    from SNITZ_REPLY 
   where SNITZ_REPLY.R_AUTHOR = @iUsers  
order by SNITZ_REPLY.R_LAST_POST desc

substitute whatever the correct field is for SNITZ_REPLY.R_LAST_POST

combine these with UNION ALL to avoid an unnecessary sort (since all the datetimes are presumably different) and take the top 10 of those

select top 10
       TOPIC_ID
     , last_author_date
  from (
  select top 10 
         SNITZ_TOPICS.TOPIC_ID
       , SNITZ_TOPICS.T_LAST_POST   as last_author_date
    from SNITZ_TOPICS  
   where SNITZ_TOPICS.T_AUTHOR = @iUsers  
order by SNITZ_TOPICS.T_LAST_POST desc
union all
  select top 10 
         SNITZ_REPLY.TOPIC_ID
       , SNITZ_REPLY.R_LAST_POST    as last_author_date
    from SNITZ_REPLY 
   where SNITZ_REPLY.R_AUTHOR = @iUsers  
order by SNITZ_REPLY.R_LAST_POST desc
       )
order by last_author_date desc

and those will be the topics to list

select 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_TOPICS 
inner join
       SNITZ_FORUM
    on SNITZ_TOPICS.FORUM_ID = SNITZ_FORUM.FORUM_ID
 where SNITZ_TOPICS.TOPIC_ID
       in ( select TOPIC_ID
              from
( select top 10
       TOPIC_ID
     , last_author_date
  from (
  select top 10 
         SNITZ_TOPICS.TOPIC_ID
       , SNITZ_TOPICS.T_LAST_POST   as last_author_date
    from SNITZ_TOPICS  
   where SNITZ_TOPICS.T_AUTHOR = @iUsers  
order by SNITZ_TOPICS.T_LAST_POST desc
union all
  select top 10 
         SNITZ_REPLY.TOPIC_ID
       , SNITZ_REPLY.R_LAST_POST    as last_author_date
    from SNITZ_REPLY 
   where SNITZ_REPLY.R_AUTHOR = @iUsers  
order by SNITZ_REPLY.R_LAST_POST desc
       )
order by last_author_date desc
)

i'd be interested in finding out if this (a) gets the right answer, and (b) does it efficiently

rudy
http://rudy.ca/

Edited by - r937 on 08/31/2002 19:25:48

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-09-01 : 01:00:22

Thanks for the answer, however at least in my testing, it doesn't solve the performance issue; in fact, it it is somewhere between 10% faster and 10% slower depeinding on any given user's ratio between posts and replies.

However, I think I have come up with a workable, high-performance solution. That is, where my original query costs ran between 1.14 and 1.90, total cost for this approach rangees between 0.12 and 0.16, for at least a 10x performance gain. Of course, I understand that query costs are inexact, but in a random sampling, I am indeed finding emprical performace gains of 90% or more.

As far as I can tell, the new version always returns the correct results, in the proper order even (unlike my original query) -- though I can see a hypothetical issue if a user posted a topic and reply, or two replies to the same topic, at the exact same year/month/day/hour/minute/second... though our of 200,000+ topics/replies, I have yet to see that issue.

Here's what I've got:

	DECLARE @tPosts table ( CAT_ID int,
			TOPIC_ID int PRIMARY KEY CLUSTERED,
			FORUM_ID int,
			REPLY_ID int,
			P_DATE varchar(14)
			)

	insert into @tPosts (CAT_ID,TOPIC_ID,FORUM_ID,P_DATE)
  		select CAT_ID,TOPIC_ID,FORUM_ID,T_DATE
   		from SNITZ_TOPICS
   		where T_AUTHOR=@iUsers

	insert into @tPosts (CAT_ID,TOPIC_ID,FORUM_ID,P_DATE,REPLY_ID)
  		select CAT_ID,TOPIC_ID,FORUM_ID,MAX(R_DATE),MAX(REPLY_ID)
   		from SNITZ_REPLY R
   		where R_AUTHOR=@iUsers
     			and not exists (Select * from @tPosts where TOPIC_ID=R.TOPIC_ID) 
   		group by CAT_ID,TOPIC_ID,FORUM_ID

    	select top 10 tp.FORUM_ID,SNITZ_FORUM.F_SUBJECT,tp.CAT_ID,
           			tp.TOPIC_ID,SNITZ_TOPICS.T_SUBJECT,SNITZ_TOPICS.T_STATUS,SNITZ_TOPICS.T_LAST_POST,SNITZ_TOPICS.T_REPLIES,P_DATE,REPLY_ID
     		from @tPosts tp 
       			join SNITZ_FORUM WITH(NOLOCK) on tp.FORUM_ID=SNITZ_FORUM.FORUM_ID
       			join SNITZ_TOPICS WITH(NOLOCK) on tp.TOPIC_ID=SNITZ_TOPICS.TOPIC_ID
     		order by p_date desc

Cheers... as always, I welcome comments!

-b

Edited by - aiken on 09/01/2002 01:03:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources