代码之家 › 专栏 › 技术社区 › cmarcus

如何将大型SQL server表拉入C#进行分析

large-data sql-server sql c#

cmarcus · 技术社区 · 10 年前

我需要一些关于如何最好地解决这个问题的建议。我继承了一个从现有SQL server数据库构建报告实用程序的项目。数据库包含一个“原始数据”表,其中转储了每个生产数据点。报告需要提供几个数据列的平均值和Cpk。

“原始数据”表包含25列,包括 ID 列,a LotID 列,以及 TimeStamp 以及包含测量数据的5列。总的来说,该表似乎有2000多万条记录,并且基本上是一个巨大的平面文件。

我需要做的是提供两种搜索方法;一个批次ID可以保存多达2M条记录。

我开始开发一个使用简单SELECT查询的C#应用程序。

SELECT * 
FROM tblRawData 
WHERE [LotID] = "XXXX"

然后使用 SqlDataAdapter.Fill 函数来填充 DataTable 。我也尝试过 SqlDataReader 并循环以将结果填充到 数据表 .

我看到的最大问题是90%以上的内存使用率(其中很大一部分在SQL Server进程上),偶尔会出现内存不足警告,每次查询都需要几分钟才能运行。

我不是一个SQL Server专家,我正在寻求关于这种方法是否合理的建议,还是我应该尝试做一些不同的事情?

1 回复 | 直到 7 年前

Cryptc 10 年前

2000万条记录通常不被认为是“大量数据”,除非您的服务器速度慢,或者您的数据集中有text/bblob/(n)varchar(max)数据类型,如果可能,您应该避免这种情况。为了澄清varchar(8000)/nvarchar(4000)或更少,数据类型是可以的,因为它们不会被视为blob样式的存储(性能要慢得多)。

有几种方法可以优化您的方法:

不要“选择*”。只回拉所需的字段,这将减少从SQL服务器中提取数据并将其移动到C#应用程序的“连线”时间。
在SQL server上执行处理。SQL服务器的性能往往很高,尽管并不总是像C#那样高。如果您的应用程序只需要答案,请考虑使用内置的AVG()函数进行平均。虽然我从未做过Cpk,但可能有一种方法 do that in SQL 也此外,您还可以使用BETWEEN关键字创建日期范围。
合理使用 INDEXing 。不幸的是,正确的索引几乎是一门艺术。本质上,使用尽可能少的索引。始终有一个主聚集索引,然后是重要数据聚合的目标非聚集索引。索引降低INSERT、UPDATE和DELETE操作的速度,同时(有时)提高SELECT的性能。在您的案例中,您可能需要一个LotID索引,或者是LotID和时间戳/日期字段的组合。
将数据分块。如果可行的话,一次只能拉出合理数量的行。在许多情况下,这是不可行的,但保持开放作为一种选择。您可以将数据分块到循环中,或将数据拉到一个单独的结构中,如内存中的临时表(表示为@tableName)或服务器上的临时表,表示为#tableName。每个都有优点和缺点。服务器上的临时表可能更适合您的问题,因为它们不会占用太多内存。
如果您使用的是较新版本的SQL Server Management Studio,则内置了查询分析器/优化器。其他主要工具也通常具有此功能。它可以告诉你你所有的时间都被发送到哪里,并经常建议使用索引。

因此,如果您必须将大量数据拉入C#,那么您希望只对索引字段进行SELECT,并且只拉回可能的最小数据集。

根据我的经验,将数据拉入C#的所有形式都很快。这包括SqlDataAdapter、SQLDataReader,甚至实体框架的ORM。然而,如果您要提取的数据集非常庞大,那么您肯定会在较小的盒子上耗尽内存,并且您必须等待将所有数据移出磁盘——在磁盘上,除了任何网络延迟之外,磁盘速度也会成为性能的一大瓶颈。如果您有权访问SQL服务器盒的资源管理器,您可以实时看到它。