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

问题描述

这是我的测试代码，似乎表明多次连接比一次连接更好。

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

我做错了吗？

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:
//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

更新：

好的，所以那些说通过一个连接会更快的人拥有它。 （尽管差异很小，如果有的话。）
这是修改后的代码和输出：

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);
}

输出：

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

更新：

如果我使用 SQLConnection，则区别更加明显.ClearAllPools（）每个函数之后：

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

输出：

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

推荐答案

默认情况下，SqlConnection将使用连接池。因此，无论哪种情况，您的代码很可能实际上并未打开许多连接。

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

您可以通过在连接字符串中启用或禁用池来控制SqlConnection是否使用池，具体取决于您的连接字符串是哪个DB。

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.

请参见此处，以了解使用MSSQLServer的一些信息。尝试在连接字符串中设置Pooling = false，看看是否有区别。

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屋！