December 12, 2012 at 8:19 am
funkseo (12/11/2012)
I modified as following and I think it's little bit changed. I removed tep tables and use built-in paging.ALTER PROCEDURE [dbo].[spGetSiteListFromTagPager]
@Tag nvarchar(64),
@page int = 0
AS
DECLARE @TagID int
SET @TagID = (select top 1 TagID FROM Tags with(nolock) WHERE Tag=@Tag)
IF(@page = 1)
BEGIN
SELECT TOP 20 Sites.SiteUrl,Sites.Title,Sites.Description
from Sites with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID
END
ELSE IF(@page > 1)
BEGIN
SELECT Sites.SiteUrl,Sites.Title,Sites.Description from Sites with(nolock)
INNER JOIN (select distinct SiteID FROM SiteTags with(nolock) WHERE TagID = @TagID) as t ON Sites.SiteID = t.SiteID
ORDER BY t.SiteID
OFFSET (@page-1)*20 ROWS
FETCH NEXT 20 ROWS ONLY
END
Umm... that OFFSET feature is a SQL 2012 feature, and this is the 2008 forum. Cool feature, but wouldn't have been considered by folks based on the question being here in the 2008 forum, unless you had indicated you had 2012. I hope I didn't miss that too... (not that I knew of the new feature anyway...)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 13, 2012 at 12:35 am
funkseo (12/11/2012)
I modified as following and I think it's little bit changed. I removed tep tables and use built-in paging.ALTER PROCEDURE [dbo].[spGetSiteListFromTagPager]
@Tag nvarchar(64),
@page int = 0
AS
DECLARE @TagID int
SET @TagID = (select top 1 TagID FROM Tags with(nolock) WHERE Tag=@Tag)
IF(@page = 1)
BEGIN
SELECT TOP 20 Sites.SiteUrl,Sites.Title,Sites.Description
from Sites with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID
END
ELSE IF(@page > 1)
BEGIN
SELECT Sites.SiteUrl,Sites.Title,Sites.Description from Sites with(nolock)
INNER JOIN (select distinct SiteID FROM SiteTags with(nolock) WHERE TagID = @TagID) as t ON Sites.SiteID = t.SiteID
ORDER BY t.SiteID
OFFSET (@page-1)*20 ROWS
FETCH NEXT 20 ROWS ONLY
END
Technically you dont need to have the 2 queries a single query is more than suitable, though you might get a performance boost from the Top 20 on the initial hit.
I also think you can simpify the whole thing to either
SELECT Sites.SiteUrl,Sites.Title,Sites.Description
From Sites
Where SiteId IN (select SiteID FROM SiteTags WHERE TagID = @TagID)
ORDER BY Sites.SiteID
OFFSET (@page-1)*20 ROWS
FETCH NEXT 20 ROWS ONLY
Or with an Exists
SELECT t.SiteUrl,t.Title,t.Description
From Sites t
Where EXISTS (select SiteID FROM SiteTags WHERE TagID = @TagID and SiteId=t.SiteId)
ORDER BY t.SiteID
OFFSET (@page-1)*20 ROWS
FETCH NEXT 20 ROWS ONLY
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply