它是更好地执行多个SQL命令有一个连接，或每次重新连接？ [英] Is it better to execute many sql commands with one connection, or reconnect everytime?

问题描述

下面是我的测试code，这似乎表明，它是更好地连接连接只有一次的多次吧。

我是不是做错了什么？

  INT numIts = 100;
秒表SW =新的秒表（）;
sw.Start（）;
使用（SqlConnection的连接=新的SqlConnection（connectionParameters））
{
            connection.Open（）;
    的for（int i = 0; I＆LT; numIts;我++）
    {
        SqlCommand的命令=新的SqlCommand（sqlCommandName，连接）;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue（par1Name，par1Val）;
                command.Parameters.AddWithValue（par2Name，par2Val）;
        使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
        {
        }
    }
}
sw.Stop（）;
时间跨度durationOfOneConnectionManyCommands = sw.Elapsed;
Console.WriteLine（durationOfOneConnectionManyCommands）;

sw.Reset（）;

sw.Start（）;
的for（int i = 0; I＆LT; numIts;我++）
{
    使用（SqlConnection的连接=新的SqlConnection（connectionParameters））
    {
                connection.Open（）;
        SqlCommand的命令=新的SqlCommand（sqlCommandName，连接）;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue（par1Name，par1Val）;
                command.Parameters.AddWithValue（par2Name，par2Val）;
        使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
        {
        }
    }
}
sw.Stop（）;
时间跨度durationOfManyConnections = sw.Elapsed;
Console.WriteLine（durationOfManyConnections）;
 

输出：

  //输出：
//00:00:24.3898218 //只有一个连接建立
//00:00:23.4585797 //许多连接建立。
//
//变参数后输出（预期的要短得多）：
//00:00:03.8995448
//00:00:03.4539567
 

更新：

好了，那些谁表示，将更快瓦特/一个连接拥有它。 （虽然不同的是边缘，如果有的话）。 下面是修改后的code和输出：

 公共无效TimingTest（）
{
    numIts = 1000;
    commandTxt =选择+ colNames +由+ tablename;如果

    OneConnection（）;
    ManyConnections（）;
    OneConnection（）;
}
私人无效ManyConnections（）
{
    秒表SW =新的秒表（）;
    sw.Start（）;
    的for（int i = 0; I＆LT; numIts;我++）
    {
        使用（SqlConnection的连接=新的SqlConnection（connectionParameters））
        {
            connection.Open（）;
            使用（SqlCommand的命令= connection.CreateCommand（））
            {
                command.CommandText = commandTxt;

                使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
                {
                }
            }
        }
    }
    sw.Stop（）;
    时间跨度durationOfManyConnections = sw.Elapsed;
    Console.WriteLine（许多连接：+ durationOfManyConnections）;
}
私人无效OneConnection（）
{
    秒表SW =新的秒表（）;
    sw.Start（）;
    使用（SqlConnection的连接=新的SqlConnection（connectionParameters））
    {
        connection.Open（）;
        的for（int i = 0; I＆LT; numIts;我++）
        {
            使用（SqlCommand的命令= connection.CreateCommand（））
            {
                command.CommandText = commandTxt;
                使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
                {
                }
            }
        }
    }
    sw.Stop（）;
    时间跨度durationOfOneConnectionManyCommands = sw.Elapsed;
    Console.WriteLine（一个连接：+ durationOfOneConnectionManyCommands）;
}
 

输出：

 一个连接：00：00：08.0410024
许多连接：00：00：08.7278090
一个连接：00：00：08.6368853

一个连接：00：00：10.7965324
许多连接：00：00：10.8674326
一个连接：00：00：08.6346272
 

更新：

的差异更加显着，如果我使用 SQLConnection.ClearAllPools（）每个函数之后：

输出：

 一个连接：00：00：09.8544728
许多连接：00：00：11.4967753
一个连接：00：00：09.7775865
 

解决方案

在默认情况下，SqlConnection的将使用连接池。因此，你的code也很可能没有真正打开任何情况下，许多连接。

您可以控制​​，如果SqlConnection的将使用池通过启用或禁用池在ConnectionString，这取决于你的数据库连接字符串是，语法会有所不同。

请参阅这里的一些信息，如果您使用的MSSQLServer 。尝试设置池=虚假的连接字符串中，看看它是否有差别。

Here's my test code, which seems to suggest that it's better to connect multiple times instead of connecting just once.

Am I doing something wrong?

int numIts = 100;
Stopwatch sw = new Stopwatch();
sw.Start();
using (SqlConnection connection = new SqlConnection(connectionParameters))
{   
            connection.Open();
    for(int i = 0; i < numIts; i++)
    {
        SqlCommand command = new SqlCommand(sqlCommandName, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue(par1Name, par1Val);
                command.Parameters.AddWithValue(par2Name, par2Val);
        using(SqlDataReader reader = command.ExecuteReader())
        {
        }
    }
}
sw.Stop();
TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed;
Console.WriteLine(durationOfOneConnectionManyCommands);

sw.Reset();

sw.Start();
for(int i = 0; i < numIts; i++)
{
    using (SqlConnection connection = new SqlConnection(connectionParameters))
    {   
                connection.Open();
        SqlCommand command = new SqlCommand(sqlCommandName, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue(par1Name, par1Val);
                command.Parameters.AddWithValue(par2Name, par2Val);
        using(SqlDataReader reader = command.ExecuteReader())
        {
        }
    }                               
}
sw.Stop();
TimeSpan durationOfManyConnections = sw.Elapsed;
Console.WriteLine(durationOfManyConnections);

Output:

//output:
//00:00:24.3898218   // only one connection established
//00:00:23.4585797   // many connections established.
//
//output after varying parameters (expected much shorter):
//00:00:03.8995448
//00:00:03.4539567

Update:

OK, so those who said it would be faster w/ one connection have it. (although the difference is marginal, if any.) Here's the revised code and output:

public void TimingTest()
{
    numIts = 1000;
    commandTxt = "select " + colNames + " from " + tableName;

    OneConnection();
    ManyConnections();
    OneConnection();
}
private void ManyConnections()
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    for (int i = 0; i < numIts; i++)
    {
        using (SqlConnection connection = new SqlConnection(connectionParameters))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = commandTxt;

                using (SqlDataReader reader = command.ExecuteReader())
                {
                }
            }
        }
    }
    sw.Stop();
    TimeSpan durationOfManyConnections = sw.Elapsed;
    Console.WriteLine("many connections: " + durationOfManyConnections);
}
private void OneConnection()
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    using (SqlConnection connection = new SqlConnection(connectionParameters))
    {
        connection.Open();
        for (int i = 0; i < numIts; i++)
        {
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = commandTxt;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                }
            }
        }
    }
    sw.Stop();
    TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed;
    Console.WriteLine("one connection: " + durationOfOneConnectionManyCommands);
}

Output:

one connection: 00:00:08.0410024
many connections: 00:00:08.7278090
one connection: 00:00:08.6368853

one connection: 00:00:10.7965324
many connections: 00:00:10.8674326
one connection: 00:00:08.6346272

Update:

the difference is more striking if I use SQLConnection.ClearAllPools() after each function:

Output:

one connection: 00:00:09.8544728
many connections: 00:00:11.4967753
one connection: 00:00:09.7775865

解决方案

By default, SqlConnection will use connection pooling. Therefore your code does most likely not actually open many connections in either case.

You can control if SqlConnection will use pooling by enabling or disabling the pool in the connectionstring, depending on what DB your connection string is for, the syntax will vary.

See here for some info if you use MSSQLServer. Try setting Pooling=false in the connection string and see if it makes a difference.

这篇关于它是更好地执行多个SQL命令有一个连接，或每次重新连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！