关于odp.net的FetchSize属性
书上说设置了FetchSize可以加快性能,于是测试了下具体情况。数据库用的是oracle11g
分3种,一种是不设置FetchSize属性,一种是设置FetchSize属性,一种是使用DataSet填充
最终结果如下:
1条 | 200条 | 5000条 | 10W条 | |
DataReader | 39 | 18 | 116 | 2200 |
FetchSizeDataReader | 37 | 18 | 136 | 2171 |
DataSet | 57 | 19 | 117 | 2056 |
结果和预想的不一样,查了下发现主要是因为FetchSize已经有默认值造成的,并不是像网上说的默认值是1。
/// <summary> /// /// </summary> /// <param name="id"></param> public static void selectDataReaderTest(int id) { string sql = "select s_gongchengbh,s_cid,i_yewulx,i_gongchenglx,i_gongchengzt,d_caozuosj,d_shoulisj,s_hm,s_dz from mb_gongchengxx where rownum<=" + id.ToString(); OracleConnection cn = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString); cn.Open(); OracleCommand cmd = new OracleCommand(sql, cn); OracleDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable("test"); dt.Columns.Add("s_gongchengbh", typeof(string)); dt.Columns.Add("s_cid", typeof(string)); dt.Columns.Add("i_yewulx", typeof(int)); dt.Columns.Add("i_gongchenglx", typeof(int)); dt.Columns.Add("i_gongchengzt", typeof(int)); dt.Columns.Add("d_caozuosj", typeof(DateTime)); dt.Columns.Add("d_shoulisj", typeof(DateTime)); dt.Columns.Add("s_hm", typeof(string)); dt.Columns.Add("s_dz", typeof(string)); while (reader.Read()) { DataRow dr = dt.NewRow(); dr[0] = reader.GetValue(0); dr[1] = reader.GetValue(1); dr[2] = reader.GetValue(2); dr[3] = reader.GetValue(3); dr[4] = reader.GetValue(4); dr[5] = reader.GetValue(5); dr[6] = reader.GetValue(6); dr[7] = reader.GetValue(7); dr[8] = reader.GetValue(8); dt.Rows.Add(dr); } cn.Close(); } /// <summary> /// /// </summary> /// <param name="id"></param> public static void selectFetchTest(int id) { string sql = "select s_gongchengbh,s_cid,i_yewulx,i_gongchenglx,i_gongchengzt,d_caozuosj,d_shoulisj,s_hm,s_dz from mb_gongchengxx where rownum<=" + id.ToString(); OracleConnection cn = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString); cn.Open(); OracleCommand cmd = new OracleCommand(sql, cn); OracleDataReader reader = cmd.ExecuteReader(); reader.FetchSize = cmd.RowSize * 100; DataTable dt = new DataTable("test"); dt.Columns.Add("s_gongchengbh", typeof(string)); dt.Columns.Add("s_cid", typeof(string)); dt.Columns.Add("i_yewulx", typeof(int)); dt.Columns.Add("i_gongchenglx", typeof(int)); dt.Columns.Add("i_gongchengzt", typeof(int)); dt.Columns.Add("d_caozuosj", typeof(DateTime)); dt.Columns.Add("d_shoulisj", typeof(DateTime)); dt.Columns.Add("s_hm", typeof(string)); dt.Columns.Add("s_dz", typeof(string)); while (reader.Read()) { DataRow dr = dt.NewRow(); dr[0] = reader.GetValue(0); dr[1] = reader.GetValue(1); dr[2] = reader.GetValue(2); dr[3] = reader.GetValue(3); dr[4] = reader.GetValue(4); dr[5] = reader.GetValue(5); dr[6] = reader.GetValue(6); dr[7] = reader.GetValue(7); dr[8] = reader.GetValue(8); dt.Rows.Add(dr); } cn.Close(); } /// <summary> /// /// </summary> /// <param name="id"></param> public static void selectDataSetTest(int id) { string sql = "select s_gongchengbh,s_cid,i_yewulx,i_gongchenglx,i_gongchengzt,d_caozuosj,d_shoulisj,s_hm,s_dz from mb_gongchengxx where rownum<=" + id.ToString(); OracleConnection cn = new OracleConnection(Platform.Configuration.ConfigHelper.BusinessConnString); cn.Open(); OracleCommand cmd = new OracleCommand(sql, cn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cn.Close(); }