Voici mon code de test, ce qui semble suggérer que c'est mieux de se connecter plusieurs fois au lieu de se connecter une seule fois.
Suis-je en train de faire quelque chose de mal?
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);
Sortie:
//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
Mise à jour:
OK, donc ceux qui disent qu'il serait plus rapide w/ une connexion à l'avoir. (bien que la différence est minime, le cas échéant.) Voici le code révisé et de sortie:
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);
}
Sortie:
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
Mise à jour:
la différence est plus frappant si j'utilise SQLConnection.ClearAllPools() après chaque fonction:
Sortie:
one connection: 00:00:09.8544728
many connections: 00:00:11.4967753
one connection: 00:00:09.7775865
