Environment:
DB : SQL Server 2008
client framework : .Net Framework 3.5
Test Code:
Case1 : use connection pool, 3000 times select table
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "Network Library=DBMSSOCN;Data Source=serverip,port;
Initial Catalog=wgs;User ID=your id; Password=your password;"+
"Min Pool Size=0; Max Pool Size=8;
Connection Timeout=3;Connection Lifetime=10";
//"Pooling=false; Connect Timeout=3";
string queryString = "SELECT * FROM dbo.yourtable;";
//Console.WriteLine("State: {0}", sqlconn.ConnectionString);
SqlCommand command = new SqlCommand(queryString, sqlconn);
DateTime beging = DateTime.Now;
int n = 0;
while (n < 3000) {
sqlconn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
n=n+1;
sqlconn.Close();
//Console.WriteLine("State: {0}", sqlconn.State);
}
DateTime end = DateTime.Now;
Console.WriteLine("Cost Time: {0}", end.Subtract(beging));
Case2 :don't use connection pool, 3000 times select table
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "Network Library=DBMSSOCN;Data Source=serverip,port;
Initial Catalog=wgs;User ID=your id; Password=your password;"+
"Pooling=false; Connect Timeout=3";
string queryString = "SELECT * FROM dbo.yourtable;";
//Console.WriteLine("State: {0}", sqlconn.ConnectionString);
SqlCommand command = new SqlCommand(queryString, sqlconn);
DateTime beging = DateTime.Now;
int n = 0;
while (n < 3000) {
sqlconn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
n=n+1;
sqlconn.Close();
//Console.WriteLine("State: {0}", sqlconn.State);
}
DateTime end = DateTime.Now;
Console.WriteLine("Cost Time: {0}", end.Subtract(beging));
Conclusion:
Case1: cost 4.1~.4.3 seconds.
Case2: cost up 25 seconds.
Using Connection Pool is a good solution.
