SQL Server和C#之间的性能问题

Question

我想检查在遍历 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 循环所需的时间比更新操作更短？

- Arian

Answer 1

UPDATE实际上需要在服务器级别处理锁定和磁盘访问，还需要更新基本表上的索引。 for循环不需要处理任何这些开销。但是，在for循环中进行的更改除了应用程序外，其他任何东西都无法看到。 UPDATE将更改应用于该表的所有其他查询。 for循环的更改仅在应用程序范围内与数据表可见。

另外，如果按发布的顺序运行这些查询，则第一个查询支付了从磁盘检索页面的IO成本，并且第二次运行能够从内存中提取缓存的页面。

- JNK

Answer 2

因为在实际情况下，你并没有通过for循环来直接改变服务器上的数据，而是将数据加载到你的应用程序中并在那里进行修改。

而Update操作首先会在服务器上更改数据，然后再将其加载到应用程序中。显然，这需要更多的时间。

- Oleg Dok