搜尋本站文章

2010-06-12

減少網路傳輸量,進而提升效能,以設定 SET NOCOUNT ON 不要回傳所影響的資料列筆數之訊息為例

在執行每句T-SQL陳述式時,依據預設值,系統都會自動傳送此陳述式所影響到的資料列之筆數。

請參考以下的範例與下圖所示:

USE Northwind; 
GO
-- 查詢資料表 Customers 前 5 筆的資料列 
SELECT TOP(5) CompanyName 
FROM Customers


01_回傳影響到的資料列之筆數

但若是要執行包含了多個陳述式的預存程序、觸發程序等,或是包含了迴圈等運算邏輯的陳述式,反而會產生大量的網路傳輸量。
請參考以下的範例與下圖所示:

-- 建立資料表 
CREATE TABLE count1 
(cid int) 
GO 
-- 新增 100 筆資料列 
DECLARE @cnt INT=1 
WHILE @cnt<=100 
BEGIN 
INSERT count1 VALUES(@cnt) 
SET @cnt +=1 
END


02_產生大量的網路來回流量




因此,建議在預存程序、觸發程序、使用者自訂函數等內或是執行T-SQL陳述式之前,加入設定SET NOCOUNT為ON,調整為不要回傳所影響的資料列之筆數訊息。

因為減少了網路往返流量,就可以提昇其執行效能。

請參考以下的範例:

-- 設定不要傳回所影響的資料列筆數之訊息 
SET NOCOUNT ON; 
GO
-- 再新增 2000 筆資料列 
DECLARE @cnt INT=1 
WHILE @cnt<=2000 
BEGIN 
INSERT count1 VALUES(@cnt) 
SET @cnt +=1 
END
-- 設定要回傳所影響之資料列筆數之訊息,此為預設值 
SET NOCOUNT OFF; 
GO


03_沒有逐筆顯示影響到的資料列之筆數

雖然設定不要回傳所影響的資料列之筆數訊息,但你仍是可以使用 @@ROWCOUNT 函數來查詢前一個陳述式所影響的資料列數。如果超過 20 億筆資料列,請改用 ROWCOUNT_BIG() 函數。



SET NOCOUNT (Transact-SQL)
  • 停止在部份結果集中傳回顯示 Transact-SQL 陳述式或預存程序所影響之資料列數的訊息。
  • 當 SET NOCOUNT 是 ON 時,不會傳回計數。
  • 當 SET NOCOUNT 是 OFF 時,會傳回計數。
  • 即使 SET NOCOUNT 是 ON,也會更新 @@ROWCOUNT 函數。
  • SET NOCOUNT ON 會防止針對預存程序中的每個陳述式,將 DONE_IN_PROC 訊息傳給用戶端。
  • 針對包含數個陳述式 (不會傳回許多實際資料的陳述式) 的預存程序或是包含 Transact-SQL 迴圈的程序,將 SET NOCOUNT 設為 ON 可以大幅提升效能,因為網路傳輸量會大幅降低。
  • SET NOCOUNT 所指定的設定是在執行階段進行設定,而不是在剖析階段進行設定。


@@ROWCOUNT (Transact-SQL)

  • 回傳類型:int。傳回受到前一個陳述式所影響的資料列數。

如果資料列的數目超過 20 億,請使用 ROWCOUNT_BIG。
  • ROWCOUNT_BIG (Transact-SQL) 傳回上次執行之陳述式所影響的資料列數。這個函數相當於 @@ROWCOUNT,只是 ROWCOUNT_BIG 的傳回類型是 bigint。




參考資料:
SET NOCOUNT (Transact-SQL)

http://msdn.microsoft.com/zh-tw/library/ms189837.aspx

@@ROWCOUNT (Transact-SQL)

http://msdn.microsoft.com/zh-tw/library/ms187316.aspx

ROWCOUNT_BIG (Transact-SQL)

http://msdn.microsoft.com/zh-tw/library/ms181406.aspx