| Author |
 Topic  |
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
 Posted - 03 August 2011 : 11:09:20
|
you could rewrite my query as follows to use an in emory table
SELECT DISTINCT TOP (20) T_AUTHOR, MAX(T_LAST_POST) OVER(PARTITION BY T_AUTHOR) AS LastPost
INTO @TempTopics
FROM FORUM_TOPICS
ORDER BY LastPost DESC;
SELECT * FROM FORUM_TOPICS Tp INNER JOIN @TempTopics ON
Tp.T_AUTHOR = @TempTopics.T_AUTHOR AND Tp.T_LAST_POST = @TempTopics.LastPost
actually to use an in memory table i is a bit more long winded.
DECLARE @TempTopics TABLE (
T_AUTHOR INT,
LastPost VARCHAR(14) );
INSERT @TempTopics(T_AUTHOR,LASTPOST)
SELECT DISTINCT TOP (20) T_AUTHOR, MAX(T_LAST_POST) OVER(PARTITION BY T_AUTHOR) AS LastPost
FROM FORUM_TOPICS
ORDER BY LastPost DESC;
SELECT * FROM FORUM_TOPICS Tp INNER JOIN @TempTopics Tt ON
Tp.T_AUTHOR = Tt.T_AUTHOR AND Tp.T_LAST_POST = Tt.LastPost |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 11:25:52
|
quote:
Originally posted by HuwR
code posted above to do the same with just an inline query, no functions needed
Oh yeah?
Look, no temp tables:
SELECT T.* FROM FORUM_TOPICS T INNER JOIN
(SELECT T_AUTHOR, (SELECT TOPIC_ID FROM FORUM_TOPICS T2 WHERE T2.T_AUTHOR=T1.T_AUTHOR AND T2.T_LAST_POST=T1.LASTPOST) As T_ID
FROM (
SELECT TOP (10) T_AUTHOR, MAX(T_LAST_POST) AS LastPost
FROM FORUM_TOPICS
GROUP BY T_AUTHOR
ORDER BY LastPost DESC
)T1
) As MyT ON MyT.T_ID=T.TOPIC_ID
ORDER BY T_LAST_POST DESC
This one also runs on MySQL, just by replacing TOP by LIMIT (and placing limit at the end of the subquery). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 11:34:58
|
This was fun, now to get back to work  . Of course, I am hoping no one noticed that I contradicted myself and ended up solving it all with a very, very, simple ( ) SQL statement. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 12:26:56
|
From an execution point of view, I tested both ways with a database from a live Snitz forum and your option had a higher subtree cost (0,8 vs. 3.17) and a higher number of scans and logical reads (rather big difference here). Probably a disadvantage on a high traffic forum.
I had performed a similar comparison before, with Snitz too (query performance has always been of interest to me) and using derived tables wasn't actually faster than using temporary tables, probably because the number of records involved was much higher. I thought it was interesting to compare it in this situation.
This is fun, but I have already spent 2 or 3 hours with this. I better go do something more boring  .
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
Topic |
|
SQL: Getting Forum Topics 1 per User?
