T-SQL筆記38_改善SQL分頁查詢效能_sp_cursoropen 和 sp_cursorfetch
之前有分享過分頁寫法
這次遇到其他種分頁作法
舊系統分頁查詢SP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Create proc [dbo].[SSP_GetPage] ( @sqlstr nvarchar(4000), @pagepos int=1, @pagesize int=99999999, @recsize int output ) as begin set nocount on declare @P1 int ,@P2 int ,@P3 int,@reccount int set @pagepos=(@pagepos-1)*@pagesize+1 exec sp_cursoropen @P1 output , @sqlstr , 1 , 8193 , @recsize output exec sp_cursorfetch @P1,16,@pagepos,@pagesize exec sp_cursorclose @P1 end |
這段程式碼定義了一個名為 SSP_GetPage 的存儲過程。該過程接受四個參數：
@sqlstr：一個字符串參數，用於指定將要執行的 SQL 陳述式。
@pagepos：一個整數參數，指定要檢索的記錄起始位置，默認為 1。
@pagesize：一個整數參數，指定要檢索的記錄數目，默認為 99999999。
@recsize：一個輸出參數，指定檢索到的記錄數目。
在存儲過程的主體中，首先使用 set nocount on 語句來禁用向客戶端返回計數信息的功能。
然後聲明了四個整型變量 @P1、@P2、@P3 和 @reccount。
接下來，根據 @pagepos 和 @pagesize 計算要檢索的記錄起始位置，然後執行 sp_cursoropen 系統存儲過程來打開游標，使用 sp_cursorfetch 函數檢索指定頁面的記錄，最後再使用 sp_cursorclose 函數關閉游標。
用到了幾個比較沒碰過的語法
sp_cursoropen,
sp_cursorfetch,
sp_cursorclosee
sp_cursoropen是一個系統預存程序，用於打開一個游標，將查詢結果集儲存在系統內存中，並返回一個參數以供後續操作。它的語法為：
sp_cursoropen語法參數
1 | sp_cursoropen [@cursor RETURN] output, [@stmt] NVARCHAR(4000), [@options] INT, [@scrollopt] INT, [@ccopt] INT, [@scrollcursortype] INT |
其中，
@cursor RETURN是一個輸出參數，用於返回一個游標句柄；
@stmt是一個 NVARCHAR(4000)類型的參數，表示要執行的 SQL 陳述式；
@options是一個 INT類型的參數，指定了游標的類型；
@scrollopt是一個 INT類型的參數，指定了游標滾動的選項；
@ccopt是一個 INT類型的參數，指定了游標的光標屬性；
@scrollcursortype是一個 INT類型的參數，指定了游標的滾動類型。
sp_cursorfetch是一個系統預存程序，用於檢索一個游標中的一個或多個行。它的語法為：
sp_cursorfetch語法參數
1 | sp_cursorfetch [@cursor] INT, [@fetchtype] INT, [@row] INT, [@rows] INT, [@rowcount] INT output |
其中，
@cursor是一個 INT類型的參數，表示要檢索的游標；
@fetchtype是一個 INT類型的參數，表示要檢索的類型；
@row是一個 INT類型的參數，表示要檢索的第一行的位置；
@rows是一個 INT類型的參數，表示要檢索的行數；
@rowcount是一個輸出參數，返回檢索到的行數。
sp_cursorclose是一個系統預存程序，用於關閉游標，釋放遊標相關的資源，包括內存、鎖定和其他系統資源。它的語法為：
sp_cursorclose語法參數
1 | sp_cursorclose [@cursor] INT |
其中，@cursor是一個 INT類型的參數，表示要關閉的游標。
底下是嘗試產生大量資料來模擬這支sp如何呼叫使用的範例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 建立一個表來存儲資料 CREATE TABLE TestTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(50), Age INT ) -- 插入 2000 萬筆資料 DECLARE @i INT = 1 WHILE (@i <= 20000000) BEGIN INSERT INTO TestTable (Name, Age) VALUES ('User ' + CAST(@i AS VARCHAR(10)), @i % 100) SET @i = @i + 1 END -- 呼叫 SSP_GetPage 存儲過程來檢索資料 DECLARE @recsize INT EXEC SSP_GetPage 'SELECT * FROM TestTable', 1, 1000, @recsize OUTPUT |
上面的例子會向 TestTable 表中插入 2000 萬筆資料，
然後調用 SSP_GetPage 存儲過程來檢索這些資料。
在呼叫存儲過程時，我們指定要檢索的記錄起始位置為 1，每頁檢索的記錄數目為 1000。@recsize 參數將被用來存儲檢索到的記錄數目。
注意，由於檢索的資料量很大，所以可能需要等待一段時間才能完成操作。
由於利用SQL的遊標存儲過程分頁效能會比較差
用游標的方式,只適合於資料量沒這麼大量的表,如果表在一萬行以上一些效能差異就很明顯
能感受到轉圈耗費的時間拉長。
sp_cursoropen 和 sp_cursorfetch 是 SQL Server 提供的用於處理查詢結果集的系統函數，它們都使用游標來處理結果集。游標是一種指標，可以讓程式在結果集中移動並操作數據。
sp_cursoropen 函數用於打開一個遊標，它接受多個參數，包括 SQL 語句、游標類型、游標並發控制方式、游標選項等。它會將查詢結果放入一個暫存表中，然後返回一個遊標的句柄，這個句柄可以用來進一步操作結果集。
sp_cursorfetch 函數用於從遊標中獲取數據，它接受多個參數，包括遊標句柄、擷取方式、起始位置、擷取行數等。它會根據參數指定的位置和數量擷取數據並返回，並且在擷取完成後自動將遊標移動到下一個位置，以便進一步擷取數據。
由於 sp_cursoropen 和 sp_cursorfetch 都使用游標來處理結果集，因此它們的效能較差，會占用大量資源。游標需要在內存中建立一個暫存表來存儲結果集，而且游標還需要保持與數據庫的連接狀態，這些都會導致效能降低。此外，使用游標還容易出現死鎖等問題，這些都會對程式的穩定性造成影響。
經改寫的版本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | CREATE PROCEDURE [dbo].[SSP_GetPage2] ( @sqlstr nvarchar(max), @pagepos int = 1, @pagesize int = 99999999, @recsize int OUTPUT ) AS BEGIN SET NOCOUNT ON; DECLARE @offset INT = (@pagepos - 1) * @pagesize + 1; DECLARE @fetchSize INT = @pagesize; DECLARE @totalCount INT; -- Get total record count SET @sqlstr = N'SELECT @totalCount = COUNT(*) FROM (' + @sqlstr + ') AS [t]'; EXEC sp_executesql @sqlstr, N'@totalCount INT OUTPUT', @totalCount OUTPUT; -- Calculate output parameters SET @recsize = @@ROWCOUNT; IF @recsize = 0 BEGIN SET @pagepos = 0; SET @pagesize = 0; END; -- Retrieve data for specified page SET @sqlstr = CONCAT( 'SELECT * FROM (', @sqlstr, ') AS [t4] ORDER BY (SELECT NULL) OFFSET @offset ROWS FETCH NEXT @fetchSize ROWS ONLY;' ); EXEC sp_executesql @sqlstr, N'@offset INT, @fetchSize INT', @offset, @fetchSize; END |
留言
張貼留言