いくつかのページングクエリの実装と比較
3128 ワード
いくつかのページングクエリーの実現と比較:個人は比較的に第5の方式を推薦して、性能は比較的に安定して、文法の構造は簡単です
--【00】初期化データ(実行数分で数百万のデータを生成)Personテーブル構造:ID(int自増列),Name(nvarchar(50)),Age(int)
WHILE 1 = 1
BEGIN
INSERT INTO dbo.Person (Name,Age) VALUES (NEWID(),Round(Rand()*100,1))
END
SELECT * FROM dbo.Person
--【01】200万データの場合、テストに7秒を費やした:テンポラリ・テーブルの方法
--【02】200万データの場合、テスト時に4秒を使用した:行数に影響する方法
--【03】200万データの場合、テストに0秒かかった:サブクエリの方法
--【04】200万データの場合、テスト時に5秒を使った:カーソルの方法
--【05】200万データの場合、テスト時に1秒使用:行番号関数の方法(SQL Server 2005以降のみサポート)
--【00】初期化データ(実行数分で数百万のデータを生成)Personテーブル構造:ID(int自増列),Name(nvarchar(50)),Age(int)
WHILE 1 = 1
BEGIN
INSERT INTO dbo.Person (Name,Age) VALUES (NEWID(),Round(Rand()*100,1))
END
SELECT * FROM dbo.Person
--【01】200万データの場合、テストに7秒を費やした:テンポラリ・テーブルの方法
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 100
SET @EndRow = 110
SET ROWCOUNT 0
DECLARE @TempTable TABLE
(
ID int IDENTITY PRIMARY KEY,
PK int
)
INSERT INTO @TempTable SELECT ID FROM dbo.Person ORDER BY Name
SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.PK WHERE B.ID >= @StartRow AND B.ID < @EndRow
--【02】200万データの場合、テスト時に4秒を使用した:行数に影響する方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET @StartRow = 100
SET @PageSize = 10
DECLARE @Sort NVARCHAR(50)-- ORDER BY
SET ROWCOUNT @StartRow
SELECT @Sort = Name FROM Person ORDER BY Name
SET ROWCOUNT @PageSize
SELECT * FROM Person WHERE Name >= @Sort ORDER BY Name
--【03】200万データの場合、テストに0秒かかった:サブクエリの方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET ROWCOUNT 0
--SET @StartRow = 100(TOP )
--SET @PageSize = 10(TOP )
SELECT * FROM Person WHERE ID IN
(
SELECT TOP 10 ID FROM Person WHERE ID NOT IN(SELECT TOP 100 ID FROM Person ORDER BY Name)
ORDER BY Name
)
ORDER BY Name
--【04】200万データの場合、テスト時に5秒を使った:カーソルの方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET @StartRow = 100
SET @PageSize = 10
SET ROWCOUNT 0
DECLARE @ID INT
DECLARE @TempTable TABLE (ID INT NOT NULL PRIMARY KEY)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ID FROM Person ORDER BY Name
OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @ID
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TempTable(ID) VALUES(@ID)
FETCH NEXT FROM PagingCursor INTO @ID
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.ID ORDER BY Name
--【05】200万データの場合、テスト時に1秒使用:行番号関数の方法(SQL Server 2005以降のみサポート)
DECLARE @StartRow INT
DECLARE @PageSize INT
SET ROWCOUNT 0
SET @StartRow = 100
SET @PageSize = 10
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber,* FROM Person
) AS A WHERE A.RowNumber BETWEEN @StartRow + 1 AND @StartRow + @PageSize