Paging by currsor

Nhân cái sự ông bạn gửi cho một bài về kỹ thuật tìm kiếm và săp xếp ở đây. Hôm nay đẹp trời xin bàn xâu thêm một chút về vấn đề này và giới thiệu một kỹ thuật khác để paging nữa là sử dụng cursor để anh em tham khảo.

1. Nói về tìm kiếm.
Nếu bạn làm về tìm kiếm dữ liệu (chỉ nói trong môi trường SQL - Database). Điều đầu tiên bạn phải tính đến Index đây là một thuộc tính vô cùng mạnh mẽ giúp bạn tìm kiếm. Bên cạnh đó nếu gặp chở ngại về Index bạn phải sử dụng tiếp tới Full Text Search. nếu bạn không sử dụng những thuộc tính này của database thì ứng dụng của bạn sẽ đần ra khi bạn phải search khoảng 4 nghìn records. Index là một thuộc tính cổ điển của các hệ quản trị dữ liệu, Nhưng khi bạn Index trên một trường dữ liệu là TEXT thì độ dài không chế của trường này bị giới hạn và như thế INDEX không thể làm việc trên trường dữ liệu text dài. Hoặc nếu có thì cũng không hiệu quả. Nhưng đừng lo lắng vì bạn sẽ hoàn toàn thỏa mãn việc tìm kiếm text với Full Text Search. Một design tốt cho DB sẽ là tiền đề để bạn cải thiện hiệu năng của hệ thống cũng như việc sẽ phải thay đổi lại cấu trúc ... Design tốt rất quan trọng

2. Nói về con trỏ.
Có rất nhiều những tranh cãi nhau về kỹ thuật paging bằng con trỏ cũng như sử dụng subquery (con sp của ông bạn sử dụng subquery ) . Theo ý kiến riêng tôi thây rằng con trỏ nhanh hơn nhưng không an toàn. Nguy cơ là rất lớn khi bạn sử dụng con trỏ trong việc paging.

3. Paging by cursor - demo

CREATE PROCEDURE [dbo].[prc_Paging_Cursor] (
@Tables varchar(1000),
@PK nvarchar(100),
@Sort nvarchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields nvarchar(1000) = '*',
@Filter nvarchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Find the @PK type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int

IF CHARINDEX('.', @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter nvarchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
SET @strFilter = ''
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

--SET @type='uniqueidentifier'
/*Execute dynamic query*/

EXEC('
DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '

DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE
(
PK ' + @type + ' NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK

SET NOCOUNT ON

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)