[SQL Server] 効率が良いページング(paging)方法

inno
2015-08-17 15:13 1777 0
MS-SQL / SQL Server
効率が良いページング(paging)方法

ページング方法には色々方法がありますが、
その中で一番効率が良かった方法を説明したいと思います。

では、テストの為に下記のSQLを実行してテーブル作成とデータを登録してください。

-- #### テーブル作成
CREATE TABLE [dbo].[Board](
[Idx] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Memo] [nvarchar](100) NOT NULL,
[Visit] [int] NOT NULL,
[RegDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Board] PRIMARY KEY CLUSTERED 
(
[Idx] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

-- #### データ登録
INSERT INTO [dbo].[Board] (UserID, Title, Memo, Visit, RegDate)
SELECT 'ID1', 'Title1', 'Memo1', 20, GETDATE() UNION ALL
SELECT 'ID2', 'Title2', 'Memo2', 10, GETDATE() UNION ALL
SELECT 'ID3', 'Title3', 'Memo3', 15, GETDATE() UNION ALL
SELECT 'ID4', 'Title4', 'Memo4', 5, GETDATE() UNION ALL
SELECT 'ID5', 'Title5', 'Memo5', 15, GETDATE() UNION ALL
SELECT 'ID6', 'Title6', 'Memo6', 20, GETDATE() UNION ALL
SELECT 'ID7', 'Title7', 'Memo7', 20, GETDATE() UNION ALL
SELECT 'ID8', 'Title8', 'Memo8', 1, GETDATE() UNION ALL
SELECT 'ID9', 'Title9', 'Memo9', 30, GETDATE() UNION ALL
SELECT 'ID10', 'Title10', 'Memo10', 10, GETDATE()

上記のSQLを一般的な掲示板のテーブルとデータです。
すべて10件のデータを登録しました。
このデータを元にページング(paging)ができるSPを作成したいと思います。


CREATE PROCEDURE [dbo].[pBoardList_Get]
@Page int
, @ListSize int
AS
SET NOCOUNT ON

SELECT TOP (@ListSize)
B.Idx, B.UserID, B.Title, B.Memo, B.Visit, B.RegDate
FROM
(
SELECT TOP 1 A.Idx FROM
(
SELECT TOP ((@Page -1) * @ListSize + 1)  B.Idx
FROM  dbo.Board B  WITH (NOLOCK)
ORDER BY B.Idx DESC
) A 
ORDER BY A.Idx ASC
) A JOIN dbo.Board B WITH (NOLOCK)
ON A.Idx >= B.Idx
ORDER BY B.Idx DESC
GO

引数の説明

@Page : 現在のページ
@ListSize : 1Pageに表示する件数


では、SPを実行してデータを確認したいと思います。
テスト用のデータが10件しかありませんので、
1Pageに3件ずつ表示して4Pageまで作ってみたいと思います。


EXEC dbo.pBoardList_Get 1, 3
EXEC dbo.pBoardList_Get 2, 3
EXEC dbo.pBoardList_Get 3, 3
EXEC dbo.pBoardList_Get 4, 3

実行結果




最新データ順に並べた結果ですので、
1ページ目にはIdx値が10~8まで、2ページ目にはIdx値が7~5まで表示されています。
もちろん、このデータもデータが多くなればなるほど遅くなりますが、
他の方法に比べパフォーマンスが良いと思います。

コメント