KB-當心SqlDataReader.Close時的額外資料傳輸量
13 | 30,230 |
正在寫一段由SqlDataReader取回資料逐一處理的程式,由於資料筆數很多,我就用了以下的程式,先小小Read()個三筆試算,就Close SqlDataReader及SqlConnection結束作業。
static void Test()
{
using (SqlConnection cn =
new SqlConnection("Data Source=mySql;
User Id=blah; Password=blahblah; Initial Catalog=MyDB;"))
{
cn.Open();
int i = 0;
SqlCommand cmd = new SqlCommand(
"SELECT * FROM BigTable", cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read() && i < 3)
{
Console.WriteLine(dr["Col1"].ToString());
/* Do something... */
i++;
}
dr.Close();
cn.Close();
}
}
沒想到程式在dr.Close()時會卡住一陣子,甚至以timeout收場。問過幾個.NET老鳥(雞老大、小熊子,怪了,我的好朋友有很多都屬於動物界),我們都一致認為依SqlDataReader的設計理念,應該會讀多少筆記錄,傳多少資料。不過用Ethereal偵測的結果,讓我大吃一驚!!
不管我Read()幾次,Client與SQL Server間的往來封包數都跟全部讀完一樣多。也就是說,SqlDataReader.Close()的同時,會將沒讀完的資料全部傳完。
雞老大隨後Pass給我摘自MS SqlDataReader.Close()的說明:
The Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query. When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method.
看來為了要計算RecordsAffected,SqlDataReader會堅持將全部資料讀完。在ADO時代,ForwardOnly Recordset的一大特點就是無法得知總筆數RecordCount。而我一直把DataReader當成ForwardOnly Recordset的傳人,想也想不到它會對計算資料筆數這麼堅持,這是所謂的"青出於藍"嗎? (摩亞風)
總之,因資料筆數在此完全沒用,我們可以依文件的建議,在dr.Close()前加上個cmd.Cancel(),就可避免不必要的統計操作。測了一下,加了cmd.Cancel之後,SqlDataReader果然就不再愚公移山了,不論SELECT母體大小為何,往來的封包數改由dr.Read()的次數決定。
【2007-04-23 補充】
以上所提事項,僅適用於SqlDataReader!! 用Reflector追蹤的結果,System.Data.OracleClient.OracleDataReader的Close()行為看起來沒有統計筆數這段,OracleCommand.Cancel()則沒做什麼事。至於ODP.NET 9207更好玩,如果你膽敢呼叫OracleCommand.Cancel(),它會賞你一個NotSupportedException。
Comments
# by carol
我也遇見這個問題,只是asp.net 功力上淺,想請教大大 cmd.Cancel()的cmd 是指什麼?
# by Jeffrey
是指在上述的程式片段中,可加入cmd.Cancle()放棄將資料全部回的後續作業。在程式範例中SqlCommand cmd = new SqlCommand("SELECT * FROM BigTable", cn);
# by carol
再請教大大 以下是我寫的程式 ==============主程式================ Sql_Select(sSQL) If SqlReder.Read = 0 Then SS_HTML = "請按下新增" ins_page.Enabled = True Else Do While SqlReder.Read SS_HTML = show_table() Loop End If SqlReder.Close() ===============function============== Public Function SQLReader(ByVal mySelectQuery As String) As SqlClient.SqlDataReader Dim myConnection As New SqlConnection(ConnString) Dim myComman d As New SqlCommand(mySelectQuery, myConnection) myConnection.Open() 'Dim myReader As SqlDataReader SQLReader = myCommand.ExecuteReader() End Function 'ReadMyData ================================= 我不能將 myCommand.Cancel寫在funciton 我要怎麼寫才能將 myCommand.Cancel 寫在 SqlReder.close 之上呢?
# by Jeffrey
cmd.Cancel()是在SqlDataReader讀一半忽然發現沒必要繼續下去踩剎車用的,所以一般會寫成 while (dr.Read()) { if (someCondition) { cmd.Cancel(); break; } ....... } 在你的例子中,myCommand是SQLReader()的區域變數,Scope不及於While SqlReder.Read的迴圈範圍,無從叫起。如果真想Cancel,可以考慮將myCommand拉到SQLReader()之外,也就是主程式存取得到的範圍中,不過我從你的Code片段中,倒看不出有Cancel的必要。
# by 迷途羔羊
請教大大一個問題,若是今天只做一些簡單的基本維護的動作,如果使用了SqlDataAdpater會不會對效能有影響,若是使用SqlDataReader將查詢的結果塞到DataSet中傳回,這樣會不會比直接用SqlDataAdapter沒有效率?
# by Jeffrey
to 迷途羔羊, 我認為二者背後的動作差不多,效能應該也不會差距太大,不過實際跑一次還是最準的。建議不妨用Stopwatch當碼錶計時,享受一下DIY的樂趣,順便還可以跟大家分享一下測試心得。 Stopwatch Class的使用可以參考: http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/08/15/ticks.aspx
# by PS
Hi 你說兩者背後動作差不多指的是哪方面? SqlDataAdapter 應該會把所有資料都載入, 載入完畢後可以斷線.. 而SqlDataReader 是一筆一筆的讀入資料, 同時必須保持連線. 還是我有哪邊搞錯了
# by Jeffrey
to PS, 我所謂的差不多是指"先寫入DataSet後更新DataSet"及"直接使用DataAdapter更新"二者做法,背後應該都是用DataAdpater進行更新。
# by PS
瞭解, 謝謝... ^_^
# by Leon
不知道再讀取 ACCESS 資料庫時 DATAREADER 是否也會這樣 !!
# by 小吳
感謝分享,真是受用。
# by fredli
觀察WireShark發現 ExecuteReader時候就會先傳輸一批資料放到buff 之後Read時候如果buff裡面資料還夠就不會請server傳回下一批 簡單的說, SqlDataReader不是Read的時候一筆一筆傳, 而是視buff來決定, 然後每次傳輸都是一批 另外, ExecuteReader(CommandBehavior.SingleRow)時 傳輸的資料還是一批, 不是只傳一筆.... 和以往認知的Read時才從server傳回一筆資料有點出入... 環境: client為 win7 32bit, framework 4.5 server為 win 2003, sql server 2000
# by 貓老大
如果使用 Npgsql 連線到 PostgreSQL 存取資料時,也不可以呼叫 cmd.Cancel(),會發生 Exception 的