将数据一次性加载到DataSet与逐行从DB内读取的性能比较
时间：2021-07-01 10:21:17
帮助过：17人阅读
strConnect =
"Data Source=localhost;Initial Catalog=cmsw;Integrated Security=True; Application Name=pgq";
DataSet dsCode =
new DataSet();
using (SqlConnection sqlConn =
new SqlConnection(strConnect))
{
sqlConn.Open();
SqlDataAdapter sda =
new SqlDataAdapter(
"SELECT top 200000 TRANCODE from JobRecord ", sqlConn);
sda.Fill(dsCode, "Test");
sqlConn.Close();
}
DateTime dt1 =
DateTime.Now;
foreach (DataRow dr
in dsCode.Tables[
0].Rows)
{
using (SqlConnection sqlConn =
new SqlConnection(strConnect))
{
string code = dr[
"TRANCODE"].ToString();
SqlCommand cmd =
new SqlCommand(
"SELECT * from JobRecord WHERE TRANCODE=@TRANCODE ", sqlConn);
cmd.Parameters.AddWithValue("TRANCODE", code);
sqlConn.Open();
SqlDataReader dataRead =
cmd.ExecuteReader();
if (dataRead.Read())
{
Console.WriteLine("One Cycle:{0}",dataRead[
"TRANCODE"]);
}
sqlConn.Close();
}
}
DateTime dt2 =
DateTime.Now;
TimeSpan ts1 = dt2 -
dt1;
Console.Clear();
Console.WriteLine("Total MilSeconds:{0}", ts1.Milliseconds);
DataSet dsTest2 =
new DataSet();
using (SqlConnection sqlConn =
new SqlConnection(strConnect))
{
sqlConn.Open();
SqlDataAdapter sda =
new SqlDataAdapter(
"SELECT top 200000 * from JobRecord ", sqlConn);
sda.Fill(dsTest2, "dsTest2");
sqlConn.Close();
}
int serialNo =
0;
while (serialNo <
200000)
{
Random rand =
new Random();
int index = rand.Next(
1,
200000);
var code = dsTest2.Tables[
0].AsEnumerable()
.Where(r => r.Field<
string>(
"TRANCODE") == dsCode.Tables[
0].Rows[index][
"TRANCODE"].ToString())
.Select(r => r.Field<
string>(
"TRANCODE")).ElementAtOrDefault(
0);
Console.WriteLine("Scend Cycle:{0}",code);
serialNo++
;
}
DateTime dt3 =
DateTime.Now;
TimeSpan ts2 = dt3 -
dt2;
Console.WriteLine("First Result, TotalSeconds:{0};\r\nSecond Result TotalSeconds:{1}", ts1.TotalSeconds, ts2.TotalSeconds);
Console.ReadLine();
将数据一次性加载到DataSet与逐行从DB内读取的性能比较
标签：校验 apt next select reac init nec 方式 value
