C# 代码和 SQL Server 性能

Question

我有一个这样设计的 SQL Server 数据库：

TableParameter
  Id    (int, PRIMARY KEY, IDENTITY)
  Name1 (string)
  Name2 (string, can be null)
  Name3 (string, can be null)
  Name4 (string, can be null)

TableValue
  Iteration         (int)
  IdTableParameter  (int, FOREIGN KEY)
  Type              (string)
  Value             (decimal)

因此，正如您刚才所理解的，TableValue 链接到 TableParameter。 TableParameter 就像一个多维字典。

TableParameter 应该有很多行（超过 300,000 行）

在我的 C# 客户端程序中，我必须在每个 Compute() 函数之后填充此数据库：

for (int iteration = 0; iteration < 5000; iteration++)
{
    Compute();
    FillResultsInDatabase();
}

在 < code>FillResultsInDatabase() 方法，我必须：

1. 检查我的参数的标签是否已存在于 TableParameter 中。如果它不存在，我必须插入一个新的。
2. 我必须将值插入 TableValue

第 1 步需要很长时间！我将所有表 TableParameter 加载到 IEnumerable 属性中，然后，对于每个参数，我创建一个

.FirstOfDefault( x => x.Name1 == item.Name1 &&
                      x.Name2 == item.Name2 &&
                      x.Name3 == item.Name3 &&
                      x.Name4 == item.Name4 );

以检测它是否已经存在（然后获取 id）。

这样的表现非常糟糕！

我尝试使用 WHERE 一词进行选择，以避免加载 TableParameter 的每一行，但性能更差！

如何提高第 1 步的性能？

对于第 2 步，使用经典的 INSERT 性能仍然很差。我将尝试SqlBulkCopy。

如何提高第 2 步的性能？

已编辑

我已尝试使用存储过程：

CREATE PROCEDURE GetIdParameter
    @Id     int OUTPUT,
    @Name1  nvarchar(50) = null,
    @Name2  nvarchar(50) = null,
    @Name3  nvarchar(50) = null
AS
SELECT TOP 1 @Id = Id FROM TableParameter
WHERE
TableParameter.Name1 = @Name1   
AND
(@Name2 IS NULL OR TableParameter.Name2= @Name2)
AND
(@Name3 IS NULL OR TableParameter.Name3 = @Name3)
GO

CREATE PROCEDURE CreateValue
    @Iteration int,
    @Type   nvarchar(50),
    @Value  decimal(32, 18),
    @Name1  nvarchar(50) = null,
    @Name2  nvarchar(50) = null,
    @Name3  nvarchar(50) = null
AS
DECLARE @IdParameter int
EXEC GetIdParameter @IdParameter OUTPUT, 
                    @Name1, @Name2, @Name3
IF @IdParameter IS NULL
BEGIN
    INSERT TablePArameter (Name1, Name2, Name3) 
                               VALUES
                              (@Name1, @Name2, @Name3)

    SELECT @IdParameter= SCOPE_IDENTITY()
END
  INSERT TableValue (Iteration, IdParamter, Type, Value) 
                              VALUES
                              (@Iteration, @IdParameter, @Type, @Value)
GO

我仍然具有相同的性能...:-(（不可接受）

Answer 1

如果我明白发生了什么，您正在查询数据库以查看步骤 1 中的数据是否存在。我将使用对存储过程的 db 调用，如果数据不存在，则插入数据。因此，只需计算结果并将其传递给 sp 即可。

可以先计算结果，然后批量插入吗？

计算函数是否从数据库中获取数据？如果是这样，您可以将操作转换为基于集合的操作并在服务器本身上执行吗？或者可能是其中的一部分？

请记住，sql server 是为大型数据集操作而设计的。

编辑：反映评论
由于代码在数据插入方面速度很慢，并且您怀疑这是因为插入必须在完成之前向后搜索，因此我建议您可能需要在搜索的列上放置 SQL 索引，以便提高搜索速度。

不过我有另一个想法。

为什么不直接插入数据而不进行检查，然后在读取数据时删除该查询中的重复项？

Answer 2

考虑到 name2 - name3 可以为 null，是否可以重构参数表：

TableParameter
  Id    (int, PRIMARY KEY, IDENTITY)
  Name  (string)
  Dimension int

现在您可以对其建立索引并简化查询。 (WHERE name = "TheNameIWant" AND Dimension="2")

（说到索引，您确实在参数表中对名称列建立了索引？）

您在哪里进行插入的提交？如果您执行一条语句提交，请将多个插入分组为一个。

如果您是唯一一个插入值的人，如果速度确实至关重要，请将所有值从数据库加载到内存中并在那里进行检查。

一些想法

的

只是马里奥

Answer 3

我必须承认，我正在努力掌握您试图在这里实现的业务流程。

初步审查时，您似乎正在应用程序层中执行数据比较。我建议您不要这样做，并建议您让数据库引擎完成其设计目的，即管理和实现数据访问。

正如另一位发帖者所提到的，我同意您应该创建一个存储过程来处理您的记录插入逻辑。该过程可以执行简单的检查以查看您的记录是否已存在。

您还应该考虑：

• 通过在四个名称列之间创建唯一约束来强制执行插入逻辑/规则。
• 创建包含四个名称列的覆盖非聚集索引。

关于插入件的性能，也许您可​​以提供一些指标来限定您所看到的内容以及如何衡量它？

为了给您一个衡量标准，SQL Server 当前的 ETL 插入记录约为每秒 1600 万行。您期待并希望看到什么样的数字？

Answer 4

最快的方法（到目前为止我知道）是批量插入。但不仅仅是 INSERT 行。尝试插入+选择+联合。它的工作速度非常快。

insert into myTable
select a1, b1, c1, ...
union select a2, b2, c2, ...
union select a3, b3, c3, ...