我想检查在遍历 DataTable
时使用 for
循环还是在 SQL Server 中执行此操作更快。因此,我创建了这样一张表:
CREATE TABLE [dbo].[tbl_Test_Data](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[LName] [nvarchar](50) NOT NULL,
[f_date] [datetime] NOT NULL,
[flag] [bit] NOT NULL,
[Age] [int] NOT NULL,
[amount] [decimal](23, 5) NOT NULL,
CONSTRAINT [PK_tbl_Test_Data] PRIMARY KEY CLUSTERED ([Id] ASC)
然后我在 Age
列上创建了一个索引:
CREATE NONCLUSTERED INDEX [idx_age] ON [dbo].[tbl_Test_Data]
(
[Age] ASC
)
INCLUDE ( [Id], [f_date])
并插入10万行测试数据:
DECLARE @a INT
SET @a = 100000;
WHILE @a >= 0
BEGIN
DECLARE @d DATETIME ;
SET @d = DATEADD(d,CAST(RAND() * 100 AS INT),GETDATE());
DECLARE @b BIT;
IF @a % 2 =0
SET @b=0;
ELSE
SET @b=1;
INSERT INTO tbl_Test_data
VALUES(N'nima',
N'agha',
DATEADD(d,CAST(RAND() * 100 AS INT),GETDATE()),
@b,
CAST(RAND() * 100 AS INT),
CAST(RAND() AS DECIMAL(23,5)) )
SET @a=@a -1;
END
我创建了一个带有两个按钮的C# Windows应用程序。为了从sql查询更新数据,我编写了以下代码:
private void button2_Click(object sender, EventArgs e)
{
Stopwatch sq = new Stopwatch();
sq.Start();
using (SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand())
{
string txt = "UPDATE tbl_Test_Data SET f_date=getdate() WHERE Age>80;";
txt += "SELECT Id,Name,LName,f_date,flag,Age,amount FROM [Northwind].[dbo].[tbl_Test_Data]";
cmd.CommandText = txt;
cmd.CommandType = CommandType.Text;
cmd.Connection = cn;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = cmd;
DataTable dt = new DataTable();
cn.Open();
da.Fill(dt);
cn.Close();
dataGridView2.DataSource = dt;
}
}
}
sq.Stop();
label2.Text = sq.Elapsed.TotalMilliseconds.ToString();
}
使用 for
循环时,我写了以下代码:
Stopwatch sq = new Stopwatch();
sq.Start();
using (SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "SELECT Id,Name,LName,f_date,flag,Age,amount FROM [Northwind].[dbo].[tbl_Test_Data]";
cmd.CommandType = CommandType.Text;
cmd.Connection = cn;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = cmd;
DataTable dt = new DataTable();
cn.Open();
da.Fill(dt);
cn.Close();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (int.Parse(dt.Rows[i]["Age"].ToString()) > 80)
{
dt.Rows[i]["f_date"] = DateTime.Now;
}
else
{
dt.Rows[i]["f_date"] = DateTime.Now.AddDays(-100);
}
}
dataGridView1.DataSource = dt;
}
}
}
sq.Stop();
label1.Text = sq.Elapsed.TotalMilliseconds.ToString();
然而令我惊讶的是,当我使用 for
循环时,它所需的时间比 SQL Server 更短,约为其的50%。我进行了多次测试。
为什么使用 for 循环所需的时间比更新操作更短?