关于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();
        }

 

 

 

转载于:https://www.cnblogs.com/zyizyizyi/archive/2013/03/26/2982536.html