学步园

AdoNet vs LinqToSql vs NIntegrateQuery查询性能测试.

先上测试结果:

AdoNetTest: 267ms
AdoNetCachedCommandTest: 182ms
LinqToSqlTest: 3191ms
LinqToSqlCompiledQueryTest: 386ms
NIntegrateQueryTest: 266ms

下载

下载测试数据库：AdventureWorksLT2008

下载测试代码：NIntegrateQueryPerformanceComparison.zip

代码导航

 1    class AdoNetTest : ITest
 2    {
 3        private static readonly string _connString = ConfigurationManager.ConnectionStrings[Constants.ConnectingStringName].ConnectionString;
 4
 5        private void DoExecute(int repeatTime)
 6        {
 7            for (var i = 1; i <= repeatTime; ++i)
 8            {
 9                using (var conn = new SqlConnection(_connString))
10                {
11                    if (conn.State != System.Data.ConnectionState.Open)
12                        conn.Open();
13                    var cmd = new SqlCommand(
14                        "SELECT TOP 1 ProductID,Name FROM SalesLT.Product WHERE ProductID = @ProductID", conn)
15                                  {CommandType = System.Data.CommandType.Text};
16                    cmd.Parameters.Add(new SqlParameter("@ProductID", i));
17                    using (var reader = cmd.ExecuteReader())
18                    {
19                        if (reader.Read())
20                        {
21                            var product = new Product
22                                              {
23                                                  ProductID = ((int) reader["ProductID"]),
24                                                  Name = ((string) reader["Name"])
25                                              };
26                        }
27                    }
28                }
29            }
30        }
31
32        ITest Members#region ITest Members
33
34        public void Prepare()
35        {
36            DoExecute(1);
37        }
38
39        public void Execute()
40        {
41            DoExecute(Constants.RepeatTime);
42        }
43
44        #endregion
45    }
46

 

 1    class AdoNetCachedCommandTest : ITest
 2    {
 3        private static readonly string _connString = ConfigurationManager.ConnectionStrings[Constants.ConnectingStringName].ConnectionString;
 4        private static SqlCommand _cachedCommand;
 5        private static SqlCommand GetCachedCommand()
 6        {
 7            if (_cachedCommand == null)
 8            {
 9                _cachedCommand = new SqlCommand(
10                    "SELECT TOP 1 ProductID,Name FROM SalesLT.Product WHERE ProductID = @ProductID")
11                                     {CommandType = System.Data.CommandType.Text};
12                _cachedCommand.Parameters.Add(new SqlParameter("@ProductID", 0));
13            }
14
15            return _cachedCommand.Clone();
16        }
17
18        private void DoExecute(int repeatTime)
19        {
20            for (var i = 1; i <= repeatTime; ++i)
21            {
22                using (var conn = new SqlConnection(_connString))
23                {
24                    if (conn.State != System.Data.ConnectionState.Open)
25                        conn.Open();
26                    var cmd = GetCachedCommand();
27                    cmd.Parameters[0].Value = i;
28                    cmd.Connection = conn;
29
30                    using (var reader = cmd.ExecuteReader())
31                    {
32                        if (reader.Read())
33                        {
34                            var product = new Product
35                                              {
36                                                  ProductID = ((int) reader["ProductID"]),
37                                                  Name = ((string) reader["Name"])
38                                              };
39                        }
40                    }
41                }
42            }
43        }
44
45        ITest Members#region ITest Members
46
47        public void Prepare()
48        {
49            DoExecute(1);
50        }
51
52        public void Execute()
53        {
54            DoExecute(Constants.RepeatTime);
55        }
56
57        #endregion
58    }
59

 

 1    class LinqToSqlTest : ITest
 2    {
 3        private static readonly string _connString = ConfigurationManager.ConnectionStrings[Constants.ConnectingStringName].ConnectionString;
 4
 5        private void DoExecute(int repeatTime)
 6        {
 7            for (var i = 1; i <= repeatTime; ++i)
 8            {
 9                using (var conn = new SqlConnection(_connString))
10                {
11                    if (conn.State != System.Data.ConnectionState.Open)
12                        conn.Open();
13
14                    var ctx = new AdventureWorksDataContext(conn);
15                    var product = ctx.Products.SingleOrDefault(p => p.ProductID == i);
16                }
17            }
18        }
19
20        ITest Members#region ITest Members
21
22        public void Prepare()
23        {
24            DoExecute(1);
25        }
26
27        public void Execute()
28        {
29            DoExecute(Constants.RepeatTime);
30        }
31
32        #endregion
33    }
34

 

 1    class LinqToSqlCompiledQueryTest : ITest
 2    {
 3        private static readonly string _connString = ConfigurationManager.ConnectionStrings[Constants.ConnectingStringName].ConnectionString;
 4
 5        private void DoExecute(int repeatTime)
 6