Recordset w/Subqueries
Jeff J.
realize this may not be the easiest question to answer, being as
abstract as it is.
I'm not looking for anyone to do this for me, just a suggestion or two
about a better approach to the problem. :)
As part of a message board, I have a query for selecting all of the
threads in a forum. For each thread (record), I'm currently running 10
functions and 4 inline SELECTs with 4 JOINs to get all the data I need
on one row.
I'm wondering whether there's a better (i.e. faster/cleaner) way to do
this, such as a temp table or cursor or something I've not thought of.
I don't know what the performance hit on a temp table is, and I can't
see how a cursor would work any better for this.
Thanks!
~ Jeff J.
Current SQL looks like this:
SELECT BT.ThreadID, ThreadViews, BP.Title, BP.EntryDate, BI.*,
dbo.ThreadTemp(BT.ThreadID) As ThreadTemp,
dbo.UserLink(BP.Poster) As Userlink,
dbo.Convert_ContactID_To_Username(BP.Poster) As Username,
(SELECT Count(*) FROM Board_Thread WHERE Board_Thread.ForumID = _
BT.ForumID) As Threads,
(SELECT Count(*) FROM Board_Post BP WHERE BP.ThreadID = BT.ThreadID)
As _
Posts,
dbo.PostInfo(dbo.LastPost_Thread(BT.ThreadID),1) As LastPostDate,
dbo.PostTitle(dbo.LastPost_Thread(BT.ThreadID)) As LastPostTitle,
dbo.Convert_ContactID_To_Username((SELECT TOP 1 Poster FROM
Board_Post WHERE _
PostID = dbo.LastPost_Thread(BT.ThreadID))) As LastPostUser,
dbo.UserLink((SELECT TOP 1 Poster FROM Board_Post WHERE PostID = _
dbo.LastPost_Thread(BT.ThreadID))) As LastPostUserLink,
dbo.MakeRating(Rating, Ratings) As Score, Rating, Ratings,
BS.SectionName + ' > ' + BF.ForumName As ForumName
FROM Board_Thread BT
INNER JOIN Board_Forum BF
ON BF.ForumID = BT.ForumID
INNER JOIN Board_Section BS
ON BS.SectionID = BF.SectionID
INNER JOIN Board_Post BP
ON BP.ThreadID = BT.ThreadID
AND BP.PostID = BT.FirstPost
LEFT JOIN Board_Icon BI
ON BI.IconID = BP.IconID
WHERE BT.ForumID = @ForumID
ORDER BY BP.EntryDate
Matt
Jeff,
Without studying your query and data structure at length I'll provide
a generalized answer.
If you only run this query once in a while as a report then live with
the big, ugly query. Make it a stored proc and be sure that all
supporting indexes are correct. Views probably won't help except to
make the query prettier. Some sort of temporary table might help, but
then you'll have to create a custom job to keep it up to date.
If however your end users run this query often, say each time they log
on, then I suggest reviewing your data structure. Probably you can
store data a little better so users can access it more efficiently.
Or maybe you want to review if this feature is really worth the
performance hit.
Matt.
Jeff J.
It finally came to me. I can simply join in the Board_Post table
twice, with different field links, and I'm down to one subquery and 2
deterministic functions.
Don't mind me, I just like doing things the hard way. :)
~ Jeff J.
jpj...@earthlink.net (Jeff J.) wrote in message news:<f2309d88.03021...@posting.google.com>...