究竟哪种取数据的方式最快?

对于DataSet,DataReader,sql语句取数据,存储过程取数据这些常见的方式,它们之间的效率问题,网上的评论非常多,而且又不是十分地统一,为了证明它们到底"有多快",我进行了如下测试:

测试一:

主题:DataSet   VS  DataReader 

数据量:10万数据的测试表

连接方式:本机

先进行数据表DataTable的装载测试:

 

 1             SqlConnection conn = new SqlConnection("server=.;database=Test;uid=Test;pwd=test;");
 2             DataTable resultDt = new DataTable();
 3             resultDt.Columns.Add(new DataColumn("Id", typeof(int)));
 4             resultDt.Columns.Add(new DataColumn("TestData1",typeof(string)));
 5             resultDt.Columns.Add(new DataColumn("TestData2", typeof(string)));            
 6             string queryStr = "select * from TestTable";//构建数据表,连接对象及查询语句

 7             Stopwatch watch1 = new Stopwatch();//方式一:DataSet方式装载
 8             watch1.Start();
 9             SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
10             DataSet ds = new DataSet();
11             sda.Fill(ds);
12             resultDt = ds.Tables[0];
13             watch1.Stop();
14 
15 
16             Stopwatch watch2 = new Stopwatch();//方式二:DataReader方式装载
17             watch2.Start();
18             SqlCommand cmd = new SqlCommand(queryStr, conn);
19             conn.Open();
20             SqlDataReader sdr = cmd.ExecuteReader();
21             while (sdr.Read()) {
22                 DataRow dr = resultDt.NewRow();
23                 dr["Id"] = sdr["id"];
24                 dr["TestData1"] = sdr["TestData1"];
25                 dr["TestData2"] = sdr["TestData2"];
26                 resultDt.Rows.Add(dr);
27             }
28             sdr.Close();
29             conn.Close();
30             watch2.Stop();
31 
32             Console.WriteLine("DataSet    method,Reflection: " + watch1.Elapsed);
33             Console.WriteLine("DataReader method,Reflection: " + watch2.Elapsed);
34             Console.ReadLine();

 测试结果:

DataSet    method,Reflection: 00:00:01.0080464
DataReader method,Reflection: 00:00:01.1056812 

然后,我们把这个语句放进存储过程,再测试速度:

 存储过程中语句也就是刚刚的查询语句:

 

 1             SqlConnection conn = new SqlConnection("server=.;database=Test;uid=Test;pwd=test;");
 2             DataTable resultDt = new DataTable();
 3             resultDt.Columns.Add(new DataColumn("Id", typeof(int)));
 4             resultDt.Columns.Add(new DataColumn("TestData1", typeof(string)));
 5             resultDt.Columns.Add(new DataColumn("TestData2", typeof(string)));
 6 
 7 
 8 
 9             SqlCommand cmd = new SqlCommand();
10             cmd.CommandType = CommandType.StoredProcedure;
11             cmd.CommandText = "GetTestData";
12             cmd.Connection = conn;
13 
14             Stopwatch watch1 = new Stopwatch();
15             watch1.Start();            
16             SqlDataAdapter sda = new SqlDataAdapter(cmd);
17             DataSet ds = new DataSet();
18             sda.Fill(ds);
19             resultDt = ds.Tables[0];
20             watch1.Stop();
21 
22 
23             Stopwatch watch2 = new Stopwatch();
24             watch2.Start();            
25             conn.Open();
26             SqlDataReader sdr = cmd.ExecuteReader();
27             while (sdr.Read())
28             {
29                 DataRow dr = resultDt.NewRow();
30                 dr["Id"] = sdr["id"];
31                 dr["TestData1"] = sdr["TestData1"];
32                 dr["TestData2"] = sdr["TestData2"];
33                 resultDt.Rows.Add(dr);
34             }
35             sdr.Close();
36             conn.Close();
37             watch2.Stop();
38 
39             Console.WriteLine("DataSet    method,Reflection: " + watch1.Elapsed);
40             Console.WriteLine("DataReader method,Reflection: " + watch2.Elapsed);
41             Console.ReadLine();

 运行结果为:

DataSet    method,Reflection: 00:00:01.0025863
DataReader method,Reflection: 00:00:01.1531541

小结:不管是SQL语句方式,还是存储过程方式,把数据装进DataTable,DataSet方式都比DataReader要快一点点.要是不装载到DataTable呢,情况会不会有所改变呢?

带着这个问题,我有了如下测试:

 

 1             SqlConnection conn = new SqlConnection("server=.;database=Test;uid=Test;pwd=test;");
 2             DataTable resultDt = new DataTable();
 3             resultDt.Columns.Add(new DataColumn("Id", typeof(int)));
 4             resultDt.Columns.Add(new DataColumn("TestData1", typeof(string)));
 5             resultDt.Columns.Add(new DataColumn("TestData2", typeof(string)));
 6             string queryStr = "select * from TestTable";
 7             Stopwatch watch1 = new Stopwatch();
 8             watch1.Start();
 9             SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
10             DataSet ds = new DataSet();
11             sda.Fill(ds);
12             resultDt = ds.Tables[0];
13             watch1.Stop();
14 
15 
16             Stopwatch watch2 = new Stopwatch();
17             watch2.Start();
18             SqlCommand cmd = new SqlCommand(queryStr, conn);
19             conn.Open();
20             SqlDataReader sdr = cmd.ExecuteReader();
21             int i = 0;
22             while (sdr.Read())
23             {
24                 i++;
25             }
26             sdr.Close();
27             conn.Close();
28             watch2.Stop();
29 
30             Console.WriteLine("DataSet    method,Reflection: " + watch1.Elapsed);
31             Console.WriteLine("DataReader method,Reflection: " + watch2.Elapsed);
32             Console.ReadLine();
33 

 运行结果:

DataSet    method,Reflection: 00:00:00.9876266
DataReader method,Reflection: 00:00:00.1093267

情况有了改变.但现实中,DataReader不装载到任何对象中的情况还是非常少的.例如,我们经常把这些数据装到List<>对象中,效率究竟怎么样?

 

 1             SqlConnection conn = new SqlConnection("server=.;database=Test;uid=Test;pwd=test;");
 2             List<TestData> resultDt = new List<TestData>();            
 3             TestData td = null;
 4             string queryStr = "select * from TestTable";
 5             Stopwatch watch1 = new Stopwatch();
 6             watch1.Start();
 7             SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
 8             DataSet ds = new DataSet();
 9             sda.Fill(ds);            
10             foreach (DataRow dr in ds.Tables[0].Rows) {
11                 td = new TestData();
12                 td.Id = Convert.ToInt32(dr["Id"]);
13                 td.TestData1 = dr["TestData1"].ToString();
14                 td.TestData2 = dr["TestData2"].ToString();
15                 resultDt.Add(td);
16             }
17             watch1.Stop();
18 
19 
20             Stopwatch watch2 = new Stopwatch();
21             watch2.Start();
22             SqlCommand cmd = new SqlCommand(queryStr, conn);
23             conn.Open();
24             SqlDataReader sdr = cmd.ExecuteReader();            
25             while (sdr.Read())
26             {
27                 td = new TestData();
28                 td.Id = Convert.ToInt32(sdr["Id"]);
29                 td.TestData1 = sdr["TestData1"].ToString();
30                 td.TestData2 = sdr["TestData2"].ToString();
31                 resultDt.Add(td);
32             }
33             sdr.Close();
34             conn.Close();
35             watch2.Stop();
36 
37             Console.WriteLine("DataSet    method,Reflection: " + watch1.Elapsed);
38             Console.WriteLine("DataReader method,Reflection: " + watch2.Elapsed);
39             Console.ReadLine();

 

 运行结果:

DataSet    method,Reflection: 00:00:01.1134010
DataReader method,Reflection: 00:00:00.4901601

小结:

DataSet中包含了DataTable的集合,所以,在SqlDataAdapter.Fill(ds)的过程中,其实已经进行了一次数据装载,再装载到其它对象,等于是进行了2次装载.故尔速度稍慢.

要将数据装到DataTable时,DataReader比DataSet要慢,而装载到List<>对象时,DataReader要比DataTable快至少一倍.

 

我目前用的是2005的测试环境,下次我会到2008环境下测试延迟加载,看看情况会有什么不同.