c# - 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]) 

并插入100000行测试数据:

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

然后我创建了一个带有 2 个按钮的 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 比使用更新花费的时间更短？

Answer 1

UPDATE 实际上必须在服务器级别处理锁和磁盘访问，它还必须更新基表上的索引。

for 循环不必处理任何此类开销。但是，在 for 循环中所做的更改对于您的应用程序之外的任何其他内容都是不可见的。

UPDATE 将更改对该表的所有其他查询。 for 循环更改仅在数据表的应用程序范围内可见。

顺便说一句，如果您按照发布的顺序运行这些查询，那么第一个查询支付 IO 成本以从磁盘检索页面，而第二个运行能够拉取缓存的页面也是可能的来自内存。