RiCo技術農場

以前，我在ASP.net webform時，有時一個webpage需要有多個datatable，

例如查詢畫面可能會有多個下拉選單和其他資料集，一般處理就需要new 多個connection和sqlcommand，

如下面我查詢三個結果集，並簡單計算總筆數

public async Task QueryMutipleData()
        {
            string sql1 = @"select top 10 DepartmentID
from Department";
            string sql2 = @"select top 20 BusinessEntityID
from Employee";
            string sql3 = @"select top 30 AddressID
from Address";
            var totals = 0;
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                if (sqlConnection.State == System.Data.ConnectionState.Closed)
                {
                    await sqlConnection.OpenAsync();
                }
                var result1 = await sqlConnection.QueryAsync(sql1, commandType: System.Data.CommandType.Text);
                totals += result1.AsList().Count;
            }
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                if (sqlConnection.State == System.Data.ConnectionState.Closed)
                {
                    await sqlConnection.OpenAsync();
                }
                var result1 = await sqlConnection.QueryAsync(sql2, commandType: System.Data.CommandType.Text);
                totals += result1.AsList().Count;
            }
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                if (sqlConnection.State == System.Data.ConnectionState.Closed)
                {
                    await sqlConnection.OpenAsync();
                }
                var result1 = await sqlConnection.QueryAsync(sql3, commandType: System.Data.CommandType.Text);
                totals += result1.AsList().Count;
            }
            await Task.Factory.StartNew<int>(() => totals);
        }

可以看到sqlconnection和command我都得各new 三次

 

但如果啟用MARS(連線字串新增MultipleActiveResultSets=True)，

我們就可以共用一條connection，並執行多個批次sqlcommand，如下

public async Task QueryMutipleDataMars()
        {
            string sql1 = @"select top 10 DepartmentID
from Department";
            string sql2 = @"select top 20 BusinessEntityID
from Employee";
            string sql3 = @"select top 30 AddressID
from Address";
            var totals = 0;
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                if (sqlConnection.State == System.Data.ConnectionState.Closed)
                {
                    await sqlConnection.OpenAsync();
                }
                var result1 = await sqlConnection.QueryAsync(sql1, commandType: System.Data.CommandType.Text);
                totals += result1.AsList().Count;
 
                var result2 = await sqlConnection.QueryAsync(sql2, commandType: System.Data.CommandType.Text);
                totals += result2.AsList().Count;
 
                var result3 = await sqlConnection.QueryAsync(sql3, commandType: System.Data.CommandType.Text);
                totals += result3.AsList().Count;
            }
            await Task.Factory.StartNew<int>(() => totals);
        }

啟用MARS我只需要new 一次sqlconnection，其他command都可共用sqlconnection

 

接下來，我使用jmeter模擬1000條http request，

分別存取QueryMutipleData和QueryMutipleDataMars Action

擷取部分

 

執行時間結果如下

可以看到，使用MARS共用connection，平均執行時間比New 三個connection進行查詢來的快3倍左右

如果你有類似的需求，或許可以啟用MARS。

 

補充:針對單一connection和單一command，啟用MARS對查詢效能影響(100條http request)

MARS=true ,三次執行平均:13 , 16 , 13

MARS=false ,三次執行平均:13 , 13 , 15

看來MARS對單一查詢的效能影響不大。

 

 

 

參考

Using MARS with SQL Native Client [Chris Lee]

MARS - does anyone use it?

Using Multiple Active Result Sets (MARS)

Report from the battlefield #9 - async/await + MARS