数据库sqlserver2008
编程vs2008,C#
该测试是数据库和程序在一台计算机上，如果不在一台计算机上就不一样了
我的数据库最大链接是127，好像不固定。
一、测试链接对速度的影响
static void RunSql()
{
DateTime datetime1 = DateTime.Now;
for (int i = 0; i < 1000; i++)
{
SqlHelper.ExSql();
}
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
1、初始化，打开，操作，关闭
public static void ExSql()
{
conn = new SqlConnection(connstr);
string sqlstr1 = "INSERT INTO [dbo].[T2]([C2])VALUES('a')";
SqlCommand cmd1 = new SqlCommand(sqlstr1, conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();
}
2、打开，操作，关闭
3、操作
总结：1000个周期，节省了100ms，没多大区别
二、SqlDataAdapter打开前和打开后的状态
1、开始是打开状态
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlstr,conn);
Console.WriteLine(conn.State.ToString());
DataTable dt=new DataTable();
da.Fill(dt);
Console.WriteLine(conn.State.ToString());
}
2、开始是关闭状态
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
conn.Open();
conn.Close();
SqlDataAdapter da = new SqlDataAdapter(sqlstr,conn);
Console.WriteLine(conn.State.ToString());
DataTable dt=new DataTable();
da.Fill(dt);
Console.WriteLine(conn.State.ToString());
}
总结：SqlDataAdapter.Fill()执行后的状态由执行前的状态决定
三、SqlDataAdapter和SqlDataReader的速度
1、SqlDataAdapter
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
SqlDataAdapter da = new SqlDataAdapter(sqlstr,conn);
DataTable dt=new DataTable();
da.Fill(dt);
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
2、SqlDataReader填充表
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
DataTable dt = new DataTable();
SqlCommand cmd=new SqlCommand(sqlstr,conn);
dt.Columns.Add("c1",typeof(int));
dt.Columns.Add("c2",typeof(string));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
object[] values = new object[2];
reader.GetValues(values);
dt.Rows.Add(values);
}
reader.Close();
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
3、SqlDataReader不填充表
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
DataTable dt = new DataTable();
SqlCommand cmd=new SqlCommand(sqlstr,conn);
dt.Columns.Add("c1",typeof(int));
dt.Columns.Add("c2",typeof(string));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
object[] values = new object[2];
reader.GetValues(values);
// dt.Rows.Add(values);//去掉该句
}
reader.Close();
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
4、SqlDataReader不读取值
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
DataTable dt = new DataTable();
SqlCommand cmd=new SqlCommand(sqlstr,conn);
dt.Columns.Add("c1",typeof(int));
dt.Columns.Add("c2",typeof(string));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//object[] values = new object[2];//去掉
//reader.GetValues(values);
// dt.Rows.Add(values);
}
reader.Close();
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
4、SqlDataReader不遍历值
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
DataTable dt = new DataTable();
SqlCommand cmd=new SqlCommand(sqlstr,conn);
dt.Columns.Add("c1",typeof(int));
dt.Columns.Add("c2",typeof(string));
SqlDataReader reader = cmd.ExecuteReader();
//while (reader.Read())
//{
// //object[] values = new object[2];
// //reader.GetValues(values);
// // dt.Rows.Add(values);
//}
reader.Close();
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
}
5、SqlDataReader不销毁
public static void ExSql()
{
string sqlstr="SELECT [C1],[C2] FROM [dbo].[T2]";
DateTime datetime1 = DateTime.Now;
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sqlstr, conn);
dt.Columns.Add("c1",typeof(int));
dt.Columns.Add("c2",typeof(string));
SqlDataReader reader = cmd.ExecuteReader();
//while (reader.Read())
//{
// //object[] values = new object[2];
// //reader.GetValues(values);
// // dt.Rows.Add(values);
//}
DateTime datetime2 = DateTime.Now;
string a = (datetime2 - datetime1).TotalMilliseconds.ToString();
Console.WriteLine(a);
reader.Close(); //该局调换了位置
}
总结：还是用SqlDataAdapter好一点，至少语句简单