C#；SQL Server-删除“”中多行的最佳方式；一次性”；使用存储过程

本文关键字：方式 最佳 一次性 存储过程 Server- SQL 删除 | 更新日期: 2023-09-27 18:27:19

我知道SO中有很多相同的主题问题，我的问题是，如果我想删除大约1K行，如果List<int>是RecordID，我可以避免使用DataTable，并将列表翻译成一个语句：

string ParmRecordsToDelete_CsvWhereIN = "("
for(int CurIdx=0; CurIdx < RecIdsToDelete.Count; CurIdx++)
{
  ParmRecordsToDelete_CsvWhereIN += RecIdsToDelete[CurIdx] + ", ";
  //this method to create passed parameter 
  //logic to remove on last Coma on last Index..
  //or use stringJoin and somehow remove the last coma 
}
ParRecordsToDelete_CsvWhereIN +=")";

这将创建类似于"（'1'，'2'，'3'……）"

然后创建一个SqlCommand来调用存储过程：

Delete * From @TblName WHERE @ColName IN @RecordsToDeleteCsvWhereIN

这是一种有效的方法吗？单个参数的长度有限制吗？我猜是N/VARCHAR(MAX)的长度。

我想如果这不是一个有点棘手的解决方案，它就不会受到长度的限制。。。

什么是最好的快速解决方案，或者我现在走在正确的轨道上？

C#；SQL Server-删除“”中多行的最佳方式；一次性”；使用存储过程

您可以使用表值参数来传递此值。应用层看起来像

C# 

var tvp = new DataTable();
tvp.Columns.Add("Id", typeof(int));
foreach(var id in RecIdsToDelete)
    tvp.Rows.Add(new {id});
var connection = new SqlConnection("your connection string");
var delete = new SqlCommand("your stored procedure name", connection)
{
  CommandType = CommandType.StoredProcedure
};
delete
  .Parameters
  .AddWithValue("@ids", tvp)
  .SqlDbType = SqlDbType.Structured;
delete.ExecuteNonQuery();

SQL

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'IDList')
BEGIN
    CREATE TYPE IDList AS TABLE(ID INTEGER)
END

CREATE PROCEDURE School.GroupStudentDelete
(                                         
        @IDS IDLIST READONLY      
)                                         
AS
SET NOCOUNT ON;
BEGIN TRY
        BEGIN TRANSACTION
        DECLARE @Results TABLE(id INTEGER)
        DELETE 
        FROM TblName 
        WHERE Id IN (SELECT ID FROM @IDS)        
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();
        ROLLBACK TRANSACTION
        THROW; -- Rethrow exception
END CATCH
GO

与构建字符串相比，这种方法有很多优点

  • 您可以避免在应用程序层中创建查询，从而创建关注点的分离
  • 您可以更轻松地测试执行计划并优化查询
  • 您不太容易受到SQL注入攻击，因为您给定的方法将无法使用参数化查询来构建IN子句
  • 该代码更具可读性和说明性
  • 你最终不会建立过长的字符串

性能

关于TVP在大型数据集上的性能，有一些考虑因素。

因为TVP是变量，所以它们不编制统计数据。这意味着查询优化器有时会篡改执行计划。如果发生这种情况，有几个选项：

  • 在任何有索引问题的TVP语句上设置OPTION (RECOMPILE)
  • 将TVP写入本地temp并在那里设置索引

这是一篇关于TVP的精彩文章，其中有一个很好的部分介绍了性能方面的考虑，以及什么时候可以期待。

因此，如果您担心字符串参数会受到限制，那么表值参数可能是最佳选择。但最终，如果不了解更多您正在使用的数据集，很难说。

IN @Parameter不是一个选项，这样的东西不起作用。您可以将id硬连接到IN (1,2,3,4...)，但这很糟糕。我测量了一些30000个id的轻量级选择：

TVP: 339ms first run, 319ms second run
hard wired: 67728ms first run, 42ms second run

正如您所看到的，当SQL服务器必须解析巨大的字符串时，它需要非常长的时间。在第二次运行时，可以从执行计划缓存中获取查询计划，不幸的是，由于id范围很大，这是极不可能的。它只是浪费了执行计划缓存。

TVP可以毫无问题地扩展到数百万个ID，硬连接字符串会导致sql server在少于100000个ID的情况下查询失败。它与字符串的最大长度无关，只是无法处理它。

Btw。如果像这样构建字符串，请使用StringBuilder或字符串。联接，在循环中附加到字符串是非常低效的。

更好的方法是使用表值参数。您必须为参数定义一个类型，但它比在字符串中指定值（尤其是数字或日期值）更有效，因为服务器不必解析字符串来获取单个值。

我不确定你的ID类型是什么，但如果是"BIGINT"，例如：

IF NOT EXISTS (SELECT * FROM dbo.systypes WHERE name='IDList')
    CREATE TYPE IDList AS TABLE (Id BIGINT);
GO

初始化类型，然后使用它创建一个存储过程，类似于以下内容：

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name='DeleteMultipleRecords')
   EXECUTE sp_executesql N'CREATE PROCEDURE DeleteMultipleRecords AS BEGIN SET NOCOUNT ON; END'
GO
ALTER PROCEDURE [dbo].[DeleteMultipleRecords]
   @IDs IDList READONLY
AS
BEGIN
   SET NOCOUNT ON
    DELETE FROM [Table] WHERE Id IN (SELECT Id FROM @IDs)
END

您还可以将它与C#中的动态SQL一起使用。

相关文章：