| Author |
 Topic  |
|
|
sr_erick
Senior Member
USA
1318 Posts |
 Posted - 11 November 2004 : 23:19:31
|
Is there any way to make this query more efficient? I think most of the lag comes from the order by clause. I want to display latest topics but this is being just way too slow. Any ways I can speed it up? It is according to last replied to topic. Thanks!
SELECT TOP 5 FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_REPLIES, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_LAST_POST_AUTHOR, FORUM_TOPICS.T_LAST_POST, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, FORUM_TOPICS.T_MESSAGE FROM FORUM_MEMBERS, FORUM_FORUM, FORUM_TOPICS, FORUM_MEMBERS AS MEMBERS_1 WHERE FORUM_TOPICS.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND FORUM_FORUM.FORUM_ID = FORUM_TOPICS.FORUM_ID AND FORUM_MEMBERS.MEMBER_ID = FORUM_TOPICS.T_AUTHOR ORDER BY FORUM_TOPICS.T_LAST_POST DESC; |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 11 November 2004 23:19:59 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 12 November 2004 : 05:14:00
|
you could try experimenting with something like (pseudocode to follow)
select <all my fields> from <mytables> where <join conditions, although I prefer these in the from sub-clause> and FORUM_TOPICS.TOPIC_ID IN (SELECT top 5 TOPIC_ID from FORUM_TOPICS order by T_LAST_POST DESC) order by T_LAST_POST DESC
so you're only pulling on all the fields when you've identified the subset of records you want!
may help out
(edit...)
if this is SQL Server 7, we could also probably do some execution plan optimisations using 'Hints' (see BOL) |
Edited by - pdrg on 12 November 2004 05:16:36 |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 12 November 2004 : 11:37:37
|
| Depending on the results you are displaying on the screen will help us "cut the fat". What information do you need to have displayed as the ending result? |
 |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 12 November 2004 : 14:08:08
|
Topic Author (name and ID)
Subject
Date
Reply count
Topic Message
Probably don't need the last post author |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 12 November 2004 14:09:08 |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 12 November 2004 : 14:58:49
|
Then you should only need 2 tables with this query.
SELECT TOP 5 FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_REPLIES, FORUM_TOPICS.T_DATE, FORUM_MEMBERS.M_NAME
FROM FORUM_TOPICS INNER JOIN FORUM_MEMBERS ON FORUM_TOPICS.T_AUTHOR = FORUM_MEMBERS.MEMBER_ID
ORDER BY FORUM_TOPICS.T_DATE DESC;
|
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 12 November 2004 : 15:01:56
|
| wooops, that won't hide private topics though. errr, let me try that again in a minute. |
|
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 10 January 2005 : 20:09:13
|
| Sorry to bring this back up again. I've revisited the idea. There has to be a more efficient way. I need like say, the latest 5 replied to topics and who replied, reply date, topic title, etc. What's the absolutly most efficient way to get this? |
Erick
Snowmobile Fanatics
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 11 January 2005 : 04:06:35
|
SELECT TOP 5 FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_LAST_POST, FORUM_MEMBERS.M_NAME
FROM (FORUM_TOPICS INNER JOIN FORUM_MEMBERS ON FORUM_TOPICS.T_LAST_POST_AUTHOR = FORUM_MEMBERS.MEMBER_ID) INNER JOIN FORUM_FORUM ON FORUM_TOPICS.FORUM_ID = FORUM_FORUM.FORUM_ID
WHERE FORUM_FORUM.F_PRIVATEFORUMS=0
ORDER BY FORUM_TOPICS.T_LAST_POST DESC
This will give you
Topic Subject
Date of last reply
Author of last reply
If you wanted the original topic author aswell, then you will need to aadd a join to a second copy of member table. If you state exactly what you want I will redo for you.
From your several posts, it is not obvious what you actually want to return from your query
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 11 January 2005 : 04:08:58
|
| The initial query posted is about as efficient as it can be to return all the post info, it may be that your database needs better indexes |
|
|
| |
Topic |
|
Topic Locked
