April 28, 2009 at 3:24 pm
Hi
I've got the following query running on a forum and although it seems to run okay it looks a bit unruly and so I wondered if there was a better way of writing it without having three sub-queries
I've also attached the execution plan
SELECT TOP (100) mykey, title, startedby, isLocked, isSticky, active,
(SELECT MAX(postDate) AS maxDate
FROM dbo.posts WITH (NOLOCK)
WHERE (active = 'Y') AND (threadID = dbo.threads.mykey)) AS postDate,
(SELECT COUNT(mykey) AS Expr1
FROM dbo.posts AS p2 WITH (NOLOCK)
WHERE (active = 'Y') AND (threadID = dbo.threads.mykey)) AS counter,
(SELECT TOP (1) userid
FROM dbo.posts AS posts_1 WITH (NOLOCK)
WHERE (active = 'Y') AND (threadID = dbo.threads.mykey)
ORDER BY postDate DESC) AS postedBy
FROM dbo.threads WITH (NOLOCK)
WHERE (topicID = 16) AND (active = 'Y')
April 28, 2009 at 3:34 pm
Hi,
I think something like this should do the same, except for the userid...
--Edit: forget that...misunderstood your statement
I hope this helps 🙂
-----------------------
SQL Server Database Copy Tool at Codeplex
April 28, 2009 at 3:42 pm
Thanks Jetro but the subqueries reference a table called posts which is missed out in your version and so an error would occur
April 28, 2009 at 3:54 pm
You shouldn't need all three. The first two are both summary queries with the same search criteria, and could be combined. You could also combine subqueries 1 and 3 because either will get you a maximum post date. I don't see a way to get them all into 1. So....
SELECT TOP (100)
t.mykey, t.title, t.startedby, t.isLocked, t.isSticky, t.active,
c1.postDate,c1.counter,
c2.postedBy
FROM dbo.threads t
--
cross apply (select max(PostDate) as postDate, count(mykey) as counter
from dbo.posts p1
where active = 'Y' and p1.threadID = t.mykey) c1
--
cross apply (select TOP (1) userid as postedBy
from dbo.posts p2
where active = 'Y' AND p2.threadID = t.mykey
order by postDate desc) c2
--
WHERE t.TopicID = 16 AND t.active = 'Y'
--
Use OUTER APPLY instead of CROSS APPLY, if you want to see threads that have no entries in the posts table.
Edited to clean up typos in query
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 4:04 pm
Also, I used Cross Apply because I believe it's going to produce a more efficient execution plan for you. Please let me know how it performs with your production volumes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 4:20 pm
[font="Verdana"]Dammit Bob! Now I need to wrap my head around CROSS APPLY... that looks useful![/font]
April 28, 2009 at 4:30 pm
It really is.... 😉
ESPECIALLY for the classic... "I need all the data from the row that has max([col])."
Try it, you'll like it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2009 at 2:39 am
Cheers Bob, I haven't seen that one before but it seems to work well:-D
April 29, 2009 at 6:45 am
It was new in 2005, Matt. 🙂
Cross Apply and Outer Apply also work very well with table-valued functions.
If the function is an inline (single query) table valued function, the optimizer actually works with it rather like a view, in that it can change the execution plan. It doesn't get the same performance advantage for a multiline table valued function, but it can still be convenient.
Have fun.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2009 at 8:05 am
Try something like this. See if it gets you what you need with the desired efficiency:
IF OBJECT_ID(N'tempdb..#Threads') IS NOT NULL
DROP TABLE #Threads ;
IF OBJECT_ID(N'tempdb..#Posts') IS NOT NULL
DROP TABLE #Posts ;
CREATE TABLE #Threads (
ThreadID INT IDENTITY
PRIMARY KEY,
MyKey VARCHAR(50),
Title VARCHAR(50),
StartedBy VARCHAR(50),
isLocked BIT DEFAULT (0),
isSticky BIT DEFAULT (0),
Active CHAR(1) DEFAULT ('Y'),
TopicID INT) ;
CREATE TABLE #Posts (
PostID INT IDENTITY
PRIMARY KEY,
ThreadID INT NOT NULL,
Active CHAR(1) DEFAULT ('Y'),
PostDate DATETIME,
MyKey VARCHAR(10),
UserID INT NOT NULL) ;
;
WITH CTE
AS (SELECT
MyKey,
PostDate,
UserID,
row_number() OVER (PARTITION BY ThreadID ORDER BY PostDate DESC) AS Row,
row_number() OVER (PARTITION BY ThreadID ORDER BY PostDate) AS Qty
FROM
#Posts
WHERE
active = 'y')
SELECT TOP (100)
#threads.mykey,
title,
startedby,
isLocked,
isSticky,
active,
CTE.PostDate,
CTE.Qty AS counter,
CTE.UserID AS postedBy
FROM
#threads
INNER JOIN CTE
ON #threads.MyKey = CTE.MyKey
WHERE
(topicID = 16)
AND (active = 'Y')
AND CTE.Row = 1 ;
The Row check might be better in the join criteria, instead of the Where clause, no way to tell without real data to test it on. Try it both ways.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 2:13 am
Thanks GSquared
I'll give it a go and see which way is the most efficient
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply