[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秒內就跑完,這次才真正的搞定。
總結:
- 直接改sqlcommand的time out時間雖然也可以,但是需要花費超過30秒才能完成,等待時間實在太久,對UX來說不好。
- 正式環境的stored procedure都應該加上『SET NOCOUNT ON』,並減少不必要的訊息產生,以加快處理速度。
參考資料: