Most efficient JOIN on MAX child record

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

Transact-SQL (2000)

Most efficient JOIN on MAX child record

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-01-23 : 14:53:07

I have a content management system. A "Page" is made up of several "items" (e.g. paragraphs). There is then a "Content" table that contains all the bits of content - each Version of the content gets a new ID.

There is then a "Group" table which records each Content Record, and its Version. All the versions of a given piece of Content are given the same Group ID.

Each Page Item record the appropriate Group ID. Thus, for a given Page Item, using the Group table, all the versions of the Content can be listed.

What's the most efficient way to get all the most recent (highest version number) Content records for a given Page (all Items for that Page)?

Here's my attempt:


SET NOCOUNT ON 
CREATE TABLE MyPage
(
	PageID	int,
	PageItem int,
	PageTitle varchar(255),
	PageGroupID int
)
GO
CREATE TABLE MyGroup
(
	GroupID	int,
	GroupVersion int,
	GroupContentID int
)
GO
CREATE TABLE MyContent
(
	ContentID	int,
	ContentText 	text	-- Note: This is a TEXT column in the actual program
)
GO

INSERT INTO MyPage
SELECT 1001, 1, 'Home Page', 20011 UNION ALL
SELECT 1001, 2, 'Welcome Paragraph', 20012 UNION ALL
SELECT 1002, 1, 'About Us', 20021 UNION ALL
SELECT 1002, 2, 'Address', 20022 UNION ALL
SELECT 1002, 3, 'Employees', 20023

INSERT INTO MyContent
SELECT 300111, 'This is my Home Page Version 1' UNION ALL
SELECT 300112, 'This is my Home Page Version 2' UNION ALL
SELECT 300121, 'Welcome to my site Version 1' UNION ALL
SELECT 300211, 'About Us Version 1' UNION ALL
SELECT 300212, 'About Us Version 2' UNION ALL
SELECT 300221, 'Address Version 1' UNION ALL
SELECT 300231, 'Employees Version 1' UNION ALL
SELECT 300232, 'Employees Version 1'

INSERT INTO MyGroup
SELECT 20011, 1, 300111 UNION ALL
SELECT 20011, 2, 300112 UNION ALL
SELECT 20012, 1, 300121 UNION ALL
SELECT 20021, 1, 300211 UNION ALL
SELECT 20021, 2, 300212 UNION ALL
SELECT 20022, 1, 300221 UNION ALL
SELECT 20023, 1, 300231 UNION ALL
SELECT 20023, 2, 300232

-- List ALL versions of Content for a given page
SELECT	PageID,
	PageItem,
	GroupID,
	GroupVersion,
	ContentID,
	PageTitle,
	ContentText
FROM	MyPage
	JOIN MyGroup
		ON GroupID = PageGroupID
	JOIN MyCOntent
		ON ContentID = GroupContentID
WHERE	PageID = 1002	-- Use 1001 or 1002 as examples
ORDER BY PageID, PageItem, GroupID, ContentID

-- List just MOST RECENT version of each Page Item's Content
SELECT	T_PageID,
	T_PageItem,
	T_GroupID,
	T_GroupVersion,
	PageID,
	PageItem,
	GroupID,
	GroupVersion,
	ContentID,
	PageTitle,
	ContentText
FROM	(
		SELECT	[T_PageID] = PageID,
			[T_PageItem] = PageItem,
			[T_GroupID] = GroupID,
			[T_GroupVersion] = MAX(GroupVersion)
		FROM	MyPage
			JOIN MyGroup
				ON GroupID = PageGroupID
		WHERE	PageID = 1002	-- Use 1001 or 1002 as examples
		GROUP BY PageID, PageItem, GroupID
	) T
	JOIN MyPage
		 ON PageID = T_PageID
		AND PageItem = T_PageItem
	JOIN MyGroup
		 ON GroupID = T_GroupID
		AND GroupVersion = T_GroupVersion
	JOIN MyCOntent
		ON ContentID = GroupContentID
ORDER BY 
--	T_PageID, T_PageItem, T_GroupID, T_GroupVersion,
	PageID, PageItem, GroupID, ContentID


GO
DROP TABLE MyPage
GO
DROP TABLE MyGroup
GO
DROP TABLE MyContent
GO
SET NOCOUNT ON

Thanks

Kristen

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-24 : 01:50:48

How about this..


-- List just MOST RECENT version of each Page Item's Content
SELECT	PageID,
	PageItem,
	GroupID,
	GroupVersion,
	ContentID,
	PageTitle,
	ContentText
FROM	(
		SELECT	[T_GroupID] = GroupID,
			[T_GroupVersion] = MAX(GroupVersion)
		FROM	MyGroup
		GROUP BY GroupID
	) T
	JOIN MyPage
		 ON PageGroupID = T_GroupID
	JOIN MyGroup
		 ON GroupID = T_GroupID
		AND GroupVersion = T_GroupVersion
	JOIN MyCOntent
		ON ContentID = GroupContentID
WHERE PageID = 1002
ORDER BY 
--	T_PageID, T_PageItem, T_GroupID, T_GroupVersion,
	PageID, PageItem, GroupID, ContentID

or (even better) this...


-- List just MOST RECENT version of each Page Item's Content
SELECT	PageID,
	PageItem,
	GroupID,
	GroupVersion,
	ContentID,
	PageTitle,
	ContentText
FROM	MyPage
	,MyGroup
	,MyCOntent
WHERE PageID       = 1002
  AND GroupID      = PageGroupID
  AND ContentID    = GroupContentID
  and GroupVersion = (SELECT MAX(GroupVersion) FROM MyGroup where GroupID = MyPage.PageGroupID)

ORDER BY 
--	T_PageID, T_PageItem, T_GroupID, T_GroupVersion,
	PageID, PageItem, GroupID, ContentID

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-24 : 10:39:24

I was going to suggest something very similar to hgorijala's "How about this.."
I had guessed that the order (in performance from best to worst) would have been:
1. How about this..
2. Or (even better) this...
3. -- List just MOST RECENT version of each Page Item's Content

but after doing a very simple comparison in scan and read counts (by setting statistics and IO in current connection properties) it looks like the order is:
1. Or (even better) this...
2. How about this..
3. -- List just MOST RECENT version of each Page Item's Content

I thought the correlated sub-query wouldn't have been as good as the simple derrived table but at least with this sample data (and no indexes) I was wrong.

I don't know if you have the option, Kristen, but if you could alter the structure to include an IsCurrent bit column in MyGroup table that could be maintained by the ins, upd, del SPs for that table, that might be the best option of all because you wouldn't need a derrived table or a correlated subquery.

tg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources