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
 SQL Server Development (2000)
 Paging and sequence

Author  Topic 

skillile
Posting Yak Master

208 Posts
Posted - 2003-06-30 : 11:13:57
I currently use paging in most of my web apps. I run the rows into a primary key on a temp var table and then return the correct next...x records.

Anyway, has anyone created a better way to do this. I am stumped trying to return the same sequence every time. ie.

set rowcoun @var
select
'sequence # here' as seq,
field blah, blah
from table
case when seq = @var then 0 else 1 end

this is just an idea ( not tested ) but, I can't get the sequence id.

Any ideas or any other ideas would be appreciated. Just trying to get away from the temp vars.

Thanks

order by


slow down to move faster...

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-06-30 : 14:39:04
Look at my post here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25496

I haven't seen a better way ... it works pretty well. any comments are welcome -- I've posted this method many times and have yet to receive feedback on it! how odd...

Let me know if it doesn't make any sense or is too hard to follow, or if it is not clear what it does.

- Jeff
Go to Top of Page

skillile
Posting Yak Master

208 Posts
Posted - 2003-06-30 : 20:48:39
thanks man but,

again you have to have a uniq field already generated. I do have a uniq id but, there may be holes in the sequence and I sort the results by date ASC

slow down to move faster...
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-06-30 : 21:36:15
maybe that's why no one gets it. it can be ordered by any column, such as date, or any number of columns -- not just some an identity field. The only caveat is the ordering MUST be unique to return consistent retults. (not really a caveat, since we *are* working with RDMS's here ...)

In your case, since date is probably not unique, you need some unique way of ordering ... so, suppose you sort by "Date" and then some unique ID field:

(try this out. don't assume it doesn't work) :

-------------------------------------

-- for each column in your sort, you need a variable to hold
-- the "starting values". In our case, we need two:
declare @startingDate datetime;
declare @startingID int;

-- again, we want to returns resutls from row @a to row @b:
declare @a int;
declare @b int;

set @a = 200 -- start at row 200
set @b = 250 -- end at row 250

-- get the starting date and starting ID to return results:
set rowcount @a
select @StartingDate = DateCol, @startingID = ID
from yourtable
order by DateCol ASC,ID ASC

-- find out how many rows to return, and set the rowcount:
set @b = @b - @a
set rowcount @b

-- now return the results:
select * from yourtable
where
DateCol > @StartingDate OR
(DateCol = @StartingDate AND ID > @StartingID)
order by DateCol ASC, ID ASC

-- clean up:
set rowcount 0


----------------
note the WHERE clause of final SELECT statement that returns results, and how it makes sure that it returns the exact results you want. And again, note that this data is ordered by DateCol and then ID (ID is included to be sure that every row is unique), and there can be duplicate Dates or gaps in the dates, no problem.

Try it out ...

- Jeff

Edited by - jsmith8858 on 07/01/2003 22:31:37
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-07-01 : 22:30:35
Apparently, still too complex to follow ...

maybe the next person who asks about paging will find it useful and give it a shot. I really think you can't get much more efficient ...

- Jeff
Go to Top of Page

skillile
Posting Yak Master

208 Posts
Posted - 2003-07-02 : 00:41:11
OK,

I did look at your code. It will work like you said but, I don't see much of a performance gain using this or using a temp var table.

Note: My select brings back about 2000 rows at a time.

But, thanks for the code I will try it for a while.





slow down to move faster...
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2003-07-02 : 05:18:12
Jeff:
quote:
any comments are welcome

Haha, obviously both your codes are nice. So, no any comment...
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-07-02 : 08:20:14
quote:

OK,
I did look at your code. It will work like you said but, I don't see much of a performance gain using this or using a temp var table.


If you have 100 total rows in your data, and want to return rows 20 through 30 when sorted, lets look at a few methods:

Temp table Method 1
Read ALL rows from the source, sorting by your sort fields, and assigning an unique ID to each row, into a temp table. Then, from the temp table, return rows 20 through 30. This first step processes 100 rows, then returns 10 rows.

Total rows processed: 110.
Temp table or table variable used? Yes

Temp table Method 2
Being a little smarter, will set ROWCOUNT to 30 and only put 30 rows into the temp table, knowing we don't need to process more than 30 rows. Then, from the temp table, we return rows 20-30.

Total rows processed: 40
Temp table or table variable used? Yes

My Method
Read rows 1-20 to determine the "starting point." Then, from there, return the next 10 rows.

Total rows processed: 30.
Temp table or table variable used? No.

--------------------

Does that make sense?

- Jeff

Edited by - jsmith8858 on 07/02/2003 08:20:52
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2003-07-02 : 13:43:56
Exactly. Your code should be added into FAQ. And I'm afraid
that skillile doesn't mind performance issue at all... :)

- Vit

Edited by - Stoad on 07/02/2003 15:02:57
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-05-22 : 02:36:54
Sorry, this thread was a while ago, but I've got a question
quote:
Originally posted by jsmith8858

set @a = 200  -- start at row 200
set @b = 250  -- end at row 250

-- get the starting date and starting ID to return results:
set rowcount @a 
select @StartingDate = DateCol, @startingID = ID 
from yourtable 
order by DateCol ASC,ID ASC


I presume SQL actually goes to the bother of reading all the records and stuffing the value into @StartingDate and @startingID? Thus starting at, say, row 1,000,000 would perhaps take a while?

Would it be more efficient to do something like:

SELECT TOP 1 @StartingDate = DateCol, @startingID = ID 
FROM
(
    SELECT TOP 1000000 DateCol, ID 
    FROM   yourtable 
    ORDER BY DateCol ASC,ID ASC 
) X
ORDER BY DateCol DESC,ID DESC

or does this, in effect, involve creating a TEMP table and thus slow things down?

I suppose, say I want the next 20 rows from 1,000,000 onwards, I could actually do

SELECT TOP 20 @StartingDate = DateCol, @startingID = ID 
FROM
(
    SELECT TOP 1000020 DateCol, ID 
    FROM   yourtable 
    ORDER BY DateCol ASC,ID ASC 
) X
ORDER BY DateCol DESC,ID DESC

and then just join that back to the original table to get any other columns of interest

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-05-22 : 22:30:01
very interesting idea ... i'll have to test to see which is more efficient ; i suspect "under the hood" sql must evaluate both in a similar manner .

- Jeff
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2004-05-23 : 05:36:05
I love Jeff's method! Sorry I haven't commented dude, but nothing beats it without having to write dynamic SQL. Kristen's will slightly out perform (mainly due to sql's bias for the TOP operator over the rowcount), but they will both process the same amount of rows...

DavidM

"Always pre-heat the oven"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-05-23 : 07:54:33
Nightmare! I thought this would be a good opportunity to knock up a template for a Function that returned the PKs for a "Page" of data - with some optional Parameterised Query Constraints.

We need this sort of stuff all the time (to do paged-resultsets on ASP web applications). Our current approach is similar, but not quite as efficient, but basically goes along the lines of:

USE Northwind
GO

SELECT   *
FROM     dbo.MyFunction(@MyPageNo, @MyStartDate, @MyEndDate, ...) T
         JOIN Orders O
              ON O.OrderID = T.OrderID
ORDER BY ...

we can therefore use the function in a number of different SProcs and centralise the "Search and paging" logic.

I thought introducing the SET ROWCOUNT would be Great! so I tried this function:

PRINT 'Create function xx_FN_Orders_List'
GO

drop function [dbo].[xx_FN_Orders_List]
GO


CREATE FUNCTION dbo.xx_FN_Orders_List
(
--[1]	@intPageNo	int,	-- Page number to display [base 1]
	-- Examples of additional query constraint parameters
	@CustomerID	nchar(5),	-- NULL=All
	@StartOrderDate	datetime,	-- Null=From first
	@EndOrderDate	datetime	-- Null=To End
)
/*
 * xx_FN_Orders_List	Return PKs of Orders table for a given page
 * 
 * NOTE: This Function has a hard-wired number of rows/page of 20 
 *       - alter globally as appropriate
 */
RETURNS @tblOrder TABLE
(
	KEY_OrderID	int	-- List all the PK Columns here ...

	PRIMARY KEY CLUSTERED 
	(
		KEY_OrderID	-- ... and here
	)

)
AS
BEGIN
--[1] Number of rows to retrieve in set
--[1] (includes all rows for PREVIOUS pages AND the actual page required)
--[1] DECLARE	@intNumberOfRows	int

--[1]	SELECT	@intNumberOfRows = @intPageNo * 20

--[1]	-- Get all preceeding rows, and the rows for the required page
--[1]	SET ROWCOUNT @intNumberOfRows

	INSERT INTO @tblOrder
--[2a]
	SELECT TOP 20
		OrderID		-- List all PK Columns
	FROM
	(
		SELECT TOP 100 PERCENT WITH TIES
			OrderID		-- List all PK Columns
		FROM	Orders
		-- Example of Parameterised Query Criteria
		WHERE	    (@CustomerID IS NULL OR CustomerID = @CustomerID)
			AND (@StartOrderDate IS NULL OR OrderDate >= @StartOrderDate)
			AND (@EndOrderDate IS NULL OR OrderDate <= @EndOrderDate)
		ORDER BY OrderID ASC	-- List all PK Columns sorted in paging order
--[2b]
	) X
	ORDER BY OrderID DESC	-- List all PK Columns sorted in REVERSE paging order

--[1]	SET ROWCOUNT 0

	RETURN	
END
--==================== xx_FN_Orders_List ====================--
GO

PRINT 'Create function xx_FN_Orders_List DONE'
GO

First off the SET ROWCOUNT stuff doesn't work within a function. Not a big problem, I can set that in the SProc before/after I call the Function. (i.e. comment out the stuff marked --[1])

Here is my "test rig", which displays the first 5 pages:

DECLARE	@intNumberOfRows	int,	-- Number of rows to retrieve in set
-- (includes all rows for PREVIOUS pages AND the actual page required)
	@intPageNo		int
SELECT	@intPageNo = 1	-- Start page
WHILE @intPageNo < 5
BEGIN
	SELECT	@intNumberOfRows = @intPageNo * 20
	SET ROWCOUNT @intNumberOfRows

-- SELECT [DEBUG(1)] = '', [@intPageNo]=@intPageNo, [@intNumberOfRows]=@intNumberOfRows
-- SELECT [DEBUG(2)] = OrderID FROM Orders ORDER BY OrderID ASC
-- SELECT [DEBUG(3)] = @@ROWCOUNT

	SELECT	*
	FROM	dbo.xx_FN_Orders_List(NULL, NULL, NULL)	-- (No Constraint Criteria used)
		JOIN Orders
			ON OrderID = KEY_OrderID
	ORDER BY OrderID ASC
	SET ROWCOUNT 0
	SELECT @intPageNo = @intPageNo + 1
END

Then [Thud!] whichever page I ask for it only returns the LAST 20 rows in the table. If I comment out the stuff at --[2] I get the first N rows (i.e. for Page=1 I get 20 rows, for Page=2 I get 40 rows and so on), so it is definitely the SELECT TOP 20 ... ORDER BY ... DESC that is mucking that up.

Either I've made an elementary error, or this isn't going to fly ...

(The BOL says
quote:
"SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value."
so, if I've read it coirrectly, this should work<sigh!>)

So then I had a look at:

CREATE FUNCTION dbo.xx_FN_Orders_List
(
	@intStartRow	int,
	@intEndRow	int
)
RETURNS TABLE
AS
RETURN
(
	SELECT	[KEY_OrderID] = O1.OrderID
	FROM	dbo.Orders O1
	WHERE	
	(
		SELECT	COUNT(*)
		FROM	dbo.Orders O2
		WHERE	O2.[OrderID] <= O1.[OrderID]
	) BETWEEN @intStartRow AND @intEndRow
)	
GO

which certainly does the job, but there is a noticeable delay in execution (even though there is a CLUSTER PRIMARY KEY on the OrderID column and only 830 rows in the table. I would not be surprised to discover that the SELECT COUNT(*) ... is very "expensive".

So then I resorted to pretty much what we are currently using:

CREATE FUNCTION dbo.xx_FN_Orders_List ()
RETURNS @tblOrder TABLE
(
	KEY_Sequence	int IDENTITY(1,1) NOT NULL, 
	KEY_OrderID	int NOT NULL,	-- List all the PK Columns here ...

	PRIMARY KEY CLUSTERED 
	(
		KEY_OrderID
	)

)
AS
BEGIN
	INSERT	@tblOrder
	(
		KEY_OrderID
	)
	SELECT	OrderID
	FROM	dbo.Orders
	ORDER BY OrderID	-- Insert in "order" so that IDENTITY provided ROWID

	RETURN
END
GO

SELECT	*
FROM	dbo.xx_FN_Orders_List()
	JOIN Orders
		ON OrderID = KEY_OrderID
	-- Use @VARIABLES here to control the range of ROWs for a given "page"
	WHERE	KEY_Sequence BETWEEN 11 AND 20
ORDER BY OrderID ASC

I reckon the bad news with this one is that it pulls ALL the rows from the main table, into the temporary table, every time it runs. Given that users will generally only page through the first few pages of a given recordset this could turn out to be very inefficient for a table with lots of rows.

So that pretty much only leaves me with the option of using NEXT and PREVIOUS links on my page which pass the [LAST and FIRST respectively] primary key of the, displayed, recordset to my Function, which can then be used that to retrieve the "First 20 AFTER" or "The 20 immediately preceeding" [respectively] allowing the application to then display the next "recordset". The only thing this precludes is being able to jump to "Page N", but I suppose I could do a couple of COUNT(*) to return the number of pages Before/After the page being viewed for information. Tables with Multi-column PKs get a little more trickey with URLs, as does any columns being used for paging which are non-unique.

However, I do perceive ONE upside, which is that if the data is changing the use of "Next page bigger than MyPkValue" will display a sensible page of data, whereas "Page 10" will be at the mercy of however many rows have been added/deleted and will either re-display some records already shown on "Page 9" or, probably worse!, miss some out.

Perhaps I should just do it in the SProc where I can use SET ROWCOUNT ...

All comments welcome, hopefully I haven't made an elementary mistake!

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-05-23 : 18:51:38
Kristen--

have you seen my blog? I present my technique a couple of different ways, and there's a stored procedure that will apply the paging technique to any SQL statement/sort. check it out at http://weblogs.sqlteam.com/jeffs .

as mentioned a few times, the main advantage of my technique (thanks, David, for the compliment by the way !!) is that no temp tables are involved and it does theoretically process as few rows as posssible per pass. no matter how you slice it, to in a relational DB to return rows x through y from some dataset when ordered a certain way, the DB engine needs to process "y" rows -- i don't think there's a way to avoid this. you could save key values and what "row number" they end up on per sort for each call in a buffer table, but if your data changes then this would be invalidated...

anyway, it's an interesting problem, so check out my dynamic SQL stored proc for some ideas ... you have some great ideas as well; I also wish UDF's were a little more flexbile (not much -- i definitely undertand they need to not affect the database's state on each call), and somewhere in the middle of our ideas there's probably a great solution.

and, of course, supposedly Yukon has some solutions to this as well but from what i've heard they involve server-side cursors or using ADO.NET at the client side and returning all rows which would be pretty inefficient I would imagine ....

- Jeff
Go to Top of Page

AlTodd
Starting Member

2 Posts
Posted - 2005-06-30 : 13:58:31
heres an sp that can page and sort on a given column either ascending or descending, based on jeff's work. It neither uses cursors or stored procedures. There's a bit more leg work and some replication for the ASC and DESC functionality, but id personally rather do that than use expensive cursors / temp tables and have real compiled SPs - not generated and exec-ed sql

Alter Procedure TEST_Paging
@pageNum int, @pageSize int, @sortColumn varchar(50), @sortAscending bit, 
@rowCount int out, 
@pageCount int out
As

SET NOCOUNT ON

if @pageNum = 0 or @pageSize = 0 raiserror('@pageNum and @pageSize must both be greater than 0', 16, 1)
declare @startRow int
declare @endRow int

set @startRow = @pageSize * (@pageNum - 1)
set @endRow = @pageSize * @pageNum

select @rowCount = count(*)
from Note 
where Title <> ''

declare @d as decimal(8,2)
Set @d = @rowCount
if(@d > 0 and @pageSize > 0) set @pageCount = ceiling(@d / @pageSize)
else set @pageCount = 0


-- for each column in your sort, you need a variable to hold
-- the "starting values". In our case, we need two:
declare @startingValue varchar(200)
declare @startingID int


-- get the starting date and starting ID to return results:
if @sortAscending = 1
begin
	if(@startRow > 0) set rowcount @startRow 
	select @startingValue = 
	case @sortColumn
		When 'Title' then Title
		When 'NoteText' then NoteText
		When 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
	end
	, @startingID = NoteID 
	from Note 
	where Title <> ''
	order by
	case @sortColumn
		when 'Title' then Title 
		when 'NoteText' then  NoteText 
		when 'CreatedDate' then  convert(varchar(23), CreatedDate, 120) 
	end
	asc, NoteID asc
end
else
begin
	if(@startRow > 0) set rowcount @startRow 
	select  @startingValue = 
	case @sortColumn
		When 'Title' then Title
		When 'NoteText' then NoteText
		When 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
	end
	, @startingID = NoteID 
	from Note 
	where Title <> ''
	order by
	case @sortColumn
		when 'Title' then Title 
		when 'NoteText' then  NoteText 
		when 'CreatedDate' then  convert(varchar(23), CreatedDate, 120) 
	end
	desc, NoteID desc
end

-- find out how many rows to return, and set the rowcount:
set @endRow = @endRow - @startRow 
set rowcount @endRow 


-- now return the results:
if @sortAscending = 1
begin
	select NoteID, Title, NoteText, CreatedDate  
	from Note 
	where 
	(@startRow = 0 OR
	(@sortColumn = 'Title' AND ((Title = @startingValue And NoteID > @startingID) OR Title > @startingValue) ) OR
	(@sortColumn = 'NoteText' AND ((NoteText = @startingValue And NoteID > @startingID) OR NoteText > @startingValue) ) OR
	(@sortColumn = 'CreatedDate' AND ((convert(varchar(23), CreatedDate, 120) = @startingValue And NoteID > @startingID) 
				OR convert(varchar(23), CreatedDate, 120) > @startingValue) ))
	And Title <> ''
	order by
	case @sortColumn
		when 'Title' then Title 
		when 'NoteText' then  NoteText 
		when 'CreatedDate' then  convert(varchar(23), CreatedDate, 120) 
	end
	asc, NoteID asc
end
else
begin
	select NoteID, Title, NoteText, CreatedDate  
	from Note 
	where 
	(@startRow = 0 OR
	(@sortColumn = 'Title' AND ((Title = @startingValue And NoteID < @startingID) OR Title < @startingValue) ) OR
	(@sortColumn = 'NoteText' AND ((NoteText = @startingValue And NoteID < @startingID) OR NoteText > @startingValue) ) OR
	(@sortColumn = 'CreatedDate' AND ((convert(varchar(23), CreatedDate, 120) = @startingValue And NoteID < @startingID) 
				OR convert(varchar(23), CreatedDate, 120) < @startingValue) ))
	And Title <> ''
	order by
	case @sortColumn
		when 'Title' then Title 
		when 'NoteText' then  NoteText 
		when 'CreatedDate' then  convert(varchar(23), CreatedDate, 120) 
	end
	desc, NoteID desc
end 

-- clean up:
set rowcount 0 
SET NOCOUNT OFF
RETURN



and some test calls
declare @myRowcount int, @myPageCount int
exec TEST_Paging 1, 3, 'Title', 1,  @rowcount = @myRowcount out,  @pageCount = @myPageCount out
exec TEST_Paging 2, 3, 'Title', 1, @rowcount = @myRowcount out,  @pageCount = @myPageCount out
exec TEST_Paging 3, 3, 'Title', 1, @rowcount = @myRowcount out,  @pageCount = @myPageCount out
select top 9 NoteID, Title, NoteText, CreatedDate, Title + convert(varchar, NoteID) 
from note
where title<>''
order by Title asc, NoteID asc
select @myRowcount As [RowCount], @myPageCount as PageCount

GO

declare @myRowcount int, @myPageCount int
exec TEST_Paging 1, 3, 'CreatedDate', 0,  @rowcount = @myRowcount out,  @pageCount = @myPageCount out
exec TEST_Paging 2, 3, 'CreatedDate', 0, @rowcount = @myRowcount out,  @pageCount = @myPageCount out
exec TEST_Paging 3, 3, 'CreatedDate', 0, @rowcount = @myRowcount out,  @pageCount = @myPageCount out
select top 9 NoteID, Title, NoteText, CreatedDate
from note
where title<>''
order by convert(varchar(23), CreatedDate, 120) desc, NoteID desc
select @myRowcount As [RowCount], @myPageCount as PageCount

GO
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site