多种存储过程分页方法的速度对比

 

四.SubQuery

 

There are 2 more methods I’ve taken into consideration, and they come from different resources. The first one is well known triple query or the SubQuery method. The most thorough approach is the one I’ve found in the following article
http://www.winnetmag.com/Article/ArticleID/40505/40505.html
Although you'll need to be subscribed, a .zip file with the SubQuery stored procedure variations is available. The Listing_04.SELECT_WITH_PAGINGStoredProcedure.txt file contains the complete generalized dynamic SQL. I used a similar generalization logic with all other stored procedures in this text. Here is the principle followed by the link to the whole procedure (I shortened the original code a bit, because a recordcount portion was unnecessary for my testing purposes).

SELECT ... FROMTableWHERE PK IN     (SELECTTOP @PageSize PK FROMTableWHERE PK NOTIN        (SELECTTOP @StartRow PK FROMTableORDERBY SortColumn)    ORDERBY SortColumn)ORDERBY SortColumn

五.Cursor(游标)

 

I’ve found the last method while browsing through the Google groups, you can find the original thread here. This method uses a server-side dynamic cursor. A lot of people tend to avoid cursors, they usually have poor performance because of their non-relational, sequential nature. The thing is that paging IS a sequential task and whatever method you use you have to somehow reach the starting row. In all the previous methods this is done by selecting all rows preceding the starting row plus the desired rows and then discarding all the preceding rows. Dynamic cursor has the FETCH RELATIVE option which does the “magic” jump. The base logic goes like this

DECLARE @PK /* PK Type */DECLARE @tblPK TABLE (    PK /* PK Type */NOTNULLPRIMARYKEY)DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FORSELECT @PK FROMTableORDERBY SortColumnOPEN PagingCursorFETCH RELATIVE @StartRow FROM PagingCursor INTO @PKWHILE @PageSize > 0AND @@FETCH_STATUS = 0BEGININSERT @tblPK(PK) VALUES(@PK)    FETCH NEXT FROM PagingCursor INTO @PK    SET @PageSize = @PageSize - 1ENDCLOSE PagingCursorDEALLOCATE PagingCursorSELECT ... FROMTableJOIN @tblPK temp ONTable.PK = temp.PK 
ORDERBY SortColumn