Forum query optimization - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Forum query optimization

Author

Topic

stanojoski
Starting Member

2 Posts

Posted - 2011-03-06 : 10:24:34

Greetings all,

I am quite new to SQL and i stuck. I create forum procedure and in one point i need all topics where there are new posts. So it happens in 2 cases:

1. New topic that user didnt even visited
2. New post in topic, after the user visited

Each time the user visit the topic, it creates record in [Forum Visits]

Here are the tables with relevant data in it:

[Forum Topic]
-------------
ID - Topic_ID)
[Last Post] - (datetime of last post)
...

[Forum Visits]
--------------
ID
User
Topic - (Topic_ID)
Data - (datetime of visiting the forum)
IP

I created the following query..

SELECT DISTINCT [Forum Topics].ID
FROM ([Forum Visits] INNER JOIN
[Forum Topics] ON [Forum Visits].topic = [Forum Topics].ID)
GROUP BY [Forum Visits].[User], [Forum Topics].ID, [Forum Topics].[Last Post]
HAVING ([Forum Visits].[User] = @User) AND ([Forum Topics].[Last Post] > MAX([Forum Visits].Data))
UNION
SELECT ID
FROM [Forum Topics] [Forum Topics_1]
WHERE (NOT EXISTS
(SELECT ID, [User], topic, Data, IP
FROM [Forum Visits] [Forum Visits_1]
WHERE ([Forum Topics_1].ID = topic) AND ([User] = @User)))

However, to execute this.. it needs 12-18 sec. per user. For forum it is just too much. Can someone please optimize this or give me some instructions to do fasten all this. Changes in the tables and query are acceptable.

Thank you all,
Toni Stanojoski

Toni Stanojoski

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-06 : 13:22:18

Toni, the query you are using may be more complex than it needs to be. Can you do the following:

a) Change the "union" in your query to "union all" and see if that gets you better performance.

b) Try the following query instead of the one you have. I may not have understood your requirements completely, so this may not give you the results or performance you are looking for, but easy enough to try.


select
   ft.[ID]
   -- uncomment the two lines below to debug
   --, ft.[Last Post]
   --, cfv.topic_last_visit_time
from
   [Forum Topic] ft
   outer apply
   (
      select top 1 Data as topic_last_visit_time
      from [Forum Visits] fv
      where fv.Topic = ft.id and fv.user = @user
      order by fv.Data desc
   ) cfv
where
   cfy.topic_last_visit_time < ft.[Last Post]
   or cfy.topic_last_visit_time is null

c) Post the DDL for the tables (the scripts that define the tables) and any indexes you have on the columns on the tables. You can copy that information from SQL server management studio, in object explorer by navigating to the tables and right clicking on the table.

stanojoski
Starting Member

2 Posts

Posted - 2011-03-07 : 06:29:38

Thanks a lot. I fixed it. First i created query with NOT IN, but instead of increasing the speed i slowed it down to 24 sec. It was disappointing. Then I got your post. Now It works something like 0.3 sec. ASP.NET created some weird errors, but i fixed it.. here is the query.

SELECT [Forum Topics].ID
FROM ([Forum Topics] LEFT OUTER JOIN
(SELECT DISTINCT ft.ID
FROM ([Forum Visits] fv INNER JOIN
[Forum Topics] ft ON fv.topic = ft.ID AND fv.Data > ft.[Last Post])
WHERE (fv.[User] = ?)) Fd ON [Forum Topics].ID = Fd.ID)
WHERE (Fd.ID IS NULL)

Thanks a lot once more.

Toni Stanojoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 07:27:19

Even shorter

SELECT		ft.ID
FROM		dbo.[Forum Topics] AS ft
LEFT JOIN	dbo.[Forum Visits] AS fv ON fv.Topic = ft.ID
			AND fv.Data > ft.[Last Post]
			AND fv.[User] = ?
WHERE		fv.Topic IS NULL

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 07:31:49

And for speed

SELECT		ft.ID
FROM		dbo.[Forum Topics] AS ft
LEFT JOIN	(
			SELECT		Topic,
					MAX(Data) AS Data	
			FROM		dbo.[Forum Visits]
			WHERE		[User] = ?
			GROUP BY	Topic
		) AS fv ON fv.Topic = ft.ID
			AND fv.Data > ft.[Last Post]
WHERE		fv.Topic IS NULL

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources