[SQL]以ADO .Net呼叫stored procedure的注意事項

[SQL]以ADO .Net呼叫stored procedure的注意事項

今天在使用ADO .Net呼叫stored procedure時,突然跳出一個執行逾時的錯誤訊息,

實際debug時發現,呼叫超過30秒就會發生這個錯誤,

原以為是連線字串給的time out時間太短,但檢查一下又發現連線的time out時間已經拉長到120秒,

不應該30秒就回應逾時,認真看一下錯誤訊息:

已超過連接逾時的設定。在作業完成之前超過逾時等待的時間,或者是伺服器未回應。

看起來不大像是連接到SQL的逾時,故排除是connecting string中的設定問題,

查了一下MSDN,原來sqlcommand的預設執行時間是30秒,於是將sqlcommand的time out時間改成120秒

SqlCommand cmd = sqlconn.CreateCommand();
cmd.CommandText = "stored procedure name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120;

再重新跑一次,搞定!

..

..

回頭想了一下發現不大對,實際在SQL Server中執行該stored procedure,只花了不到6秒就完成,

為什麼在程式中呼叫確要超過30秒??

stackoverflowt查詢了一下,發現應該是裡面沒有寫『SET NOCOUNT ON』導致。

根據MSDN中的解釋SET NOCOUNT ON的作用是

簡單來說就是平常我們在執行SQL Query時會看到的(XX資料列受到影響)

為什麼這種東西在SQL Server中執行跟ADO .Net中執行時間會有這麼大的落差?

原來每一個訊息都會被ADO .Net接收下來,只要在執行越多會顯示(XX資料列受到影響)的動作,

執行時間就會大幅的增加而造成逾時,於是動手將stored procedure中加上『SET NOCOUNT ON』,

思考了一下,順手將所有列印訊息(print 'xxx')的程式都註解起來,

回頭將sqlcommand的time out時間恢復預設值,重新跑一次,5秒內就跑完,這次才真正的搞定。

總結:

  1. 直接改sqlcommand的time out時間雖然也可以,但是需要花費超過30秒才能完成,等待時間實在太久,對UX來說不好。
  2. 正式環境的stored procedure都應該加上『SET NOCOUNT ON』,並減少不必要的訊息產生,以加快處理速度。

參考資料:

https://stackoverflow.com/questions/23505041/stored-procedure-taking-a-long-time-to-return-in-controller-but-not-in-query-ana

https://stackoverflow.com/questions/1915405/does-set-nocount-on-really-make-that-much-of-a-performance-difference/1918085#1918085

MSDN