从C#调用SQL select语句数千次，非常耗时.有更好的方法吗

本文关键字：非常 更好 方法 千次 select SQL 调用 语句 | 更新日期: 2023-09-27 18:20:37

我从excel文件中获得ID和金额的列表（数千个ID和相应的金额）。然后，我需要检查数据库，看看每个ID是否存在，以及它是否进行了检查，以确保数据库中的金额大于或等于excel文件中的金额。

问题是运行这个select语句超过6000次并返回我需要的值需要很长时间。即使是在1/2秒的速度下，完成所有的选择也需要大约一个小时。（我通常最多不会得到5个结果）

有更快的方法吗？

有没有可能一次通过所有的ID，只打一个电话就可以获得大量的收集？

我尝试过使用SqlDataReaders和SqlDataAdapters，但它们似乎大致相同（无论哪种方式都太长）

下的工作原理

for (int i = 0; i < ID.Count; i++)
{
    SqlCommand cmd = new SqlCommand("select Amount, Client, Pallet from table where ID = @ID and Amount > 0;", sqlCon);
    cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID[i];
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dataTable);
    da.Dispose();           
}

从C#调用SQL select语句数千次，非常耗时.有更好的方法吗

您可以通过一个表值参数一次传入所有值，而不是一个长的in列表（很难参数化，并且在执行计划方面还有许多其他效率低下的问题：编译时间、计划重用和计划本身）。

有关详细信息，请参阅SQL Server中的数组和列表。

通常，我会确保给表类型一个主键，并使用option (recompile)来获得最合适的执行计划。

将所有ID组合到一个大的IN子句中，因此它看起来像：

select Amount, Client, Pallet from table where ID in (1,3,5,7,9,11) and Amount > 0;

"我尝试过使用SqlDataReaders和SqlDataAdapters"

听起来您可能对其他API持开放态度。使用Linq2SQL或Linq2Entities：

var someListIds = new List<int> { 1,5,6,7 }; //imagine you load this from where ever
db.MyTable.Where( mt =>  someListIds.Contains(mt.ID) );

这在避免潜在的SQL注入漏洞方面是安全的，并且会生成"in"子句。但是请注意，someListId的大小可能太大，以至于生成的SQL查询超过了查询长度的限制，但涉及IN子句的任何其他技术也是如此。您可以通过将列表划分为大块来轻松解决此问题，并且仍然比按ID查询要好得多。

使用表值参数

有了它们，您可以将带有值的c#数据表作为结果集/表传递到存储过程中，您可以加入该结果集/表格并执行一个简单的操作：

SELECT * 
FROM YourTable 
WHERE NOT EXISTS (SELECT * FORM InputResultSet WHERE YourConditions)

使用in运算符。你的问题很常见，它有一个名字：N+1性能问题

你从哪里得到身份证？如果它来自另一个查询，则考虑将它们分组为一个。

与其对您拥有的每个ID执行单独的查询，不如执行一个查询，以获得您想要检查的每个ID的数量（或者，如果您有太多的ID无法放入一个查询中，则将它们分批处理为几千个）。

将数据直接导入SQL Server。使用存储过程输出所需的数据。

如果你必须在应用层中消费。。。使用xml数据类型传递到存储过程中。

您可以将excel文件中的数据作为表导入SQL server（使用导入数据向导）。然后，您可以在SQL server中执行一个查询，将该表联接到查找表，并在ID字段上联接。这个过程还有几个步骤，但它比试图将所有ID连接到一个更长的查询中要整洁得多。

我在这里假设对服务器有一定的访问权限，但这是我在正常情况下会做的。我也认为这是一次性任务。如果不是这样，数据到SQL server的导入也可以通过编程完成。

IN子句有限制，所以如果采用这种方法，请确保一次使用批处理大小来处理X个Id，否则您将遇到另一个问题。

@Robertharvey指出，如果没有太多ID，也没有发生任何事务，那么只需将所有ID一次拉入内存，放入一个类似字典的对象中，并在那里进行处理。六千个值并不多，一次选择可以在几秒钟内返回所有这些值。

请记住，如果另一个进程正在更新数据，则您的本地缓存版本可能已过时。

还有另一种处理方法，即生成ID的XML并将其传递给过程。这是程序代码。

IF OBJECT_ID('GetDataFromDatabase') IS NOT NULL
    BEGIN
        DROP PROCEDURE GetDataFromDatabase
    END
GO
--Definition
CREATE PROCEDURE GetDataFromDatabase
@xmlData XML
AS
BEGIN
DECLARE @DocHandle INT
DECLARE @idList Table (id INT)
EXEC SP_XML_PREPAREDOCUMENT  @DocHandle OUTPUT, @xmlData;   
INSERT INTO @idList (id) SELECT x.id FROM OPENXML(@DocHandle, '//data', 2) WITH ([id] INT) x
EXEC SP_XML_removeDOCUMENT  @DocHandle ;
--SELECT * FROM @idList
SELECT t.Amount, t.Client, t.Pallet FROM yourTable t INNER JOIN  @idList x ON t.id = x.id and t.Amount > 0;
END
GO
--Uses
EXEC GetDataFromDatabase @xmlData = '<root><data><id>1</id></data><data><id>2</id></data></root>'

你可以在程序中加入任何逻辑。您也可以通过XML传递id、amount。您可以通过XML传递庞大的id列表。

SqlDataAdapter对象太重了。首先，使用存储过程会更快。其次，使用group操作，将此传递作为数据库侧标识符列表的参数，对这些参数运行查询，并返回处理后的结果。它将快速高效，因为所有数据处理逻辑都在数据库服务器一侧

您可以选择整个结果集（或加入多个"有限"结果集）并将其全部保存到DataTable。然后，您可以直接在数据表上进行选择和更新（如果需要）。然后插回新数据。。。就内存而言，这不是超高效的，但在批量工作时通常是非常好的（也是唯一的）解决方案，并且需要非常快。因此，如果您有数千条记录，可能需要几分钟的时间才能将所有记录填充到DataTable 中

然后你可以这样搜索你的桌子：

string findMatch = "id = value";
DataRow[] rowsFound = dataTable.Select(findMatch);

然后只循环foreach (DataRow dr in rowsFound)

相关文章：