Stored Procedures | Pagination read a few friends wrote about the stored procedure pagination article, feel a bit of a problem. Starleee and the Oriental spider hope I can make a point, I simply say.
The first is that the Allsky of the paging method is not tenable at all, see he is like this:
Select @iStart = (@iPage-1) * @iPageSize
Select @iEnd = @iStart + @iPageSize +1
That is, his start and end IDs are calculated on a per-page basis, and it is necessary to satisfy the requirement that the forum has only one layout and that the ID is contiguous from 1 and that there can be no spacing between them, which means that if you delete the sticker, it will go wrong.
The second is starleee that, in fact, the idea is right, but since using the method of the first and last ID paging, there is no need to use a cursor, you can use the SELECT top * or SET rowcount = syntax to find the end-end ID, the primary method can only be used in SQL Server, The latter is true in both Sybase and Oracle.
Starleee mentioned that the test was not as fast as using a cursor, but the problem was that his index was not well established and did not specifically index the stored procedure. The most important factor affecting database efficiency is the index, which is necessary to talk about here. Theoretically, if the index of the first field of a sort does not filter out most of the data, then the index is inappropriate, which may be somewhat obscure, for example:
Select ID, name, forumid from Tablexxx where forumid=1 and name like '%aaa% ' ORDER by ID
Look at the above statement, and if you want to be efficient, you need to create an index for it:
ForumID, ID
So to speak, if you use this statement in a table with a millions record, if this index is not built, the biggest possibility is to time out, and if the index is established, it can be answered within 1 seconds if there is a record of the condition (the first qualifying record is chosen), and if no record of the condition is met, You can also respond in a minute.
The following stored procedure is my BBS using the method of seeking the end-end ID pagination, we can look at
/*************************************************************************/
/* */
* Procedure:up_gettopiclist * *
/* */
/* Description: List of posts * *
/* */
/* Parameters: @a_intForumID: Layout ID * *
/* @a_intPageNo: Page number * *
/* @a_intPageSize: Per-page display number, with the root paste as quasi * *
/* */
* * Use TABLE:BBS, forum * *
/* */
* Author:bigeagle@163.net * *
/* */
* DATE:2000/2/14 * *
/* */
/* History: * *
/* */
/*************************************************************************/
if exists (select * from sysobjects where id = object_id (' up_gettopiclist '))
drop proc Up_gettopiclist
Go
Create proc Up_gettopiclist
@a_intForumID int,
@a_intPageNo int,
@a_intPageSize int
As
/* Define Local Variables * *
DECLARE @intBeginID int
DECLARE @intEndID int
DECLARE @intRootRecordCount int
DECLARE @intPageCount int
DECLARE @intRowCount int
/* Close Count * *
SET NOCOUNT ON
/* Detect if there is this page * *
If not EXISTS (SELECT * FROM forum where id = @a_intForumID)
Return (-1)
/* Ask for a total number of root paste * *
Select @intRootRecordCount = count (*) from BBS where fatherid=0 and forumid= @a_intForumID
if (@intRootRecordCount = 0)--returns zero if no posts
return 0
* To determine whether the number of pages is correct *
if (@a_intPageNo-1) * @a_intPageSize > @intRootRecordCount
Return (-1)
* * Ask to start rootid*/
Set @intRowCount = (@a_intPageNo-1) * @a_intPageSize + 1
/* Limit number of bars * *
SET ROWCOUNT @intRowCount
Select @intBeginID = Rootid from BBS where fatherid=0 and forumid= @a_intForumID
ORDER BY id DESC
/* End rootid*/
Set @intRowCount = @a_intPageNo * @a_intPageSize
/* Limit number of bars * *
SET ROWCOUNT @intRowCount
Select @intEndID = Rootid from BBS where fatherid=0 and forumid= @a_intForumID
ORDER BY id DESC
/* RESTORE SYSTEM Variable * *
SET ROWCOUNT 0
SET NOCOUNT OFF
Select a.ID, A.layer, A.forumid, A.subject, A.faceid, A.hits, A.time, A.userid, A.fatherid, A.rootid,
' Bytes ' = datalength (a.content), B.username, B.email, B.homepage, B.signature, B.point
From BBS as a join Bbsuser as B on a.userid = b.ID
where forumid= @a_intForumID and A.rootid between @intEndID and @intBeginID
ORDER BY A.rootid Desc, a.ordernum desc
return (@ @rowcount)
--select @ @rowcount
Go
