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)  You guys are gonna cringe when you see this...

Author  Topic  Malby Starting Member 10 Posts Posted - 2001-02-11 : 13:30:43 subquery hell, sql server using 100% cpu: SELECT ThreadID, (SELECT ImageName FROM tblBoard_Style_Icons WHERE tblBoard_Style_Icons.ID = tblBoard_Threads.IconID) AS IconName, (SELECT Username FROM tblMembers WHERE tblMembers.UserID = tblBoard_Threads.AuthorID) AS AuthorName, ThreadSubject, (SELECT COUNT(MessageID) - 1 FROM tblBoard_Messages WHERE tblBoard_Messages.ThreadID = tblBoard_Threads.ThreadID) AS Replies, Views, (SELECT TOP 1 MessageDate FROM tblBoard_Messages WHERE tblBoard_Messages.ThreadID = tblBoard_Threads.ThreadID ORDER BY tblBoard_Messages.MessageDate DESC, tblBoard_Messages.MessageID DESC) AS LastPostDate, (SELECT TOP 1 MessageID FROM tblBoard_Messages WHERE tblBoard_Messages.ThreadID = tblBoard_Threads.ThreadID ORDER BY tblBoard_Messages.MessageDate DESC, tblBoard_Messages.MessageID DESC) AS LastPostID, (SELECT TOP 1 tblMembers.Username FROM tblMembers INNER JOIN tblBoard_Messages ON tblMembers.UserID = tblBoard_Messages.UserID WHERE tblBoard_Messages.ThreadID = tblBoard_Threads.ThreadID AND tblMembers.UserID = tblBoard_Messages.UserID ORDER BY tblBoard_Messages.MessageDate DESC, tblBoard_Messages.MessageID DESC) AS LastPostName, ThreadLocked FROM tblBoard_Threads WHERE ForumID = @ForumID ORDER BY LastPostDate DESC case you guys offer any help with a query like that, where I'm grabbing data from all over the place? I don't even really know how to do joins, but I know that what i have there MUST be able to be written to be a helluva lot faster. Coz its making my sql server crap itself. Takes a few seconds to crap a couple of hundred records