什么是在SQL Server中选择整个表的最快方法？

Question

我正在编写一个应用程序，它会读取整个表格，进行一些处理，然后将结果数据写入另一个表格。我正在使用SqlBulkCopy类（.net版本的“bcp in”），它可以非常快速地插入数据。但是我找不到任何有效的方法来选择数据。似乎没有 .net 版本的 "bcp out"，这对我来说很奇怪。

目前我正在使用select * from table_name。以6,000行为例，需要2.5秒才能完成选择...而仅需600毫秒即可批量插入相同数量的行。

我希望选择数据的速度应该始终比插入快。那么，从表中选择所有行和列的最快方法是什么？ 问题的答案如下：

- 我用两种方式计时了我的选择操作需要2.5秒。第一种是在运行我的应用程序并运行sql跟踪时。第二种是在SSMS中运行相同的查询。两者返回的结果大致相同。 - 我正在使用SqlDataReader读取数据。 - 没有其他应用程序正在使用此数据库。 - 我当前的处理时间少于1秒，因此2+秒的读取时间相对较长。但主要是关注当扩展到100,000行和数百万行时的性能。 - Sql Server 08r2和我的应用程序都在我的开发机上运行。 - 一些数据处理是基于设置的，因此我需要将整个表格存储在内存中（为了支持更大的数据集，我知道这一步可能需要转移到SQL中，以便我只需要在内存中对每一行进行操作）。

以下是我的代码：

DataTable staging = new DataTable();
using (SqlConnection dwConn = (SqlConnection)SqlConnectionManager.Instance.GetDefaultConnection())
{
    dwConn.Open();
    SqlCommand cmd = dwConn.CreateCommand();
    cmd.CommandText = "select * from staging_table";

    SqlDataReader reader = cmd.ExecuteReader();
    staging.Load(reader);
}

- TheSean

---

你是如何测量这2.5秒的？是在SSMS还是你的应用程序中进行的？如果是前者，如果启用“执行后丢弃结果”选项以消除SSMS处理时间会怎样？如果是后者，你的应用程序是如何检索行的-例如，你是否使用数据读取器？你的代码是什么样子的？ - Martin Smith

当您从此表中读取时，是否有其他应用程序向该表写入？如果是，则您可能想尝试使用“WITH (NOLOCK)”选项。 - WiseGuyEh

不一定。在读取时，您必须在磁盘上查找数据，但在写入时，您只需将其写入指定的位置。您可以尝试创建一个新的索引，定期重建/重组它，以使读取更有效率。 - Thyamine

我认为你需要提供一些关于如何计时的细节。你是使用DataAdapter还是DataReader？你是如何管理连接的？可能还有其他几个问题。如果你能发布你的计时代码，那将会很有帮助。 - David Hoerster

@juharr：SQL Server不支持CommandType.TableDirect。得知此事我感到非常惊讶。http://social.msdn.microsoft.com/Forums/en/csharpgeneral/thread/8a69e0c7-d839-450f-85bf-1942858766ba - TheSean

Answer 1

select * from table_name 是 读取整个表的最简单、最容易和最快速的方法。

让我解释一下为什么你的结果会导致错误的结论。

1. 复制整个表是一个经过优化的操作，只需要将旧的二进制数据克隆到新的数据中（根据存储机制，最多可以执行文件复制操作）。
2. 写操作是缓冲的。DBMS说记录已经被写入，但实际上还没有完成，除非你使用事务处理。磁盘操作通常会延迟。
3. 查询表还需要（与克隆不同）将数据从二进制存储的布局/格式适应为驱动程序依赖的格式，最终可由客户端读取。这需要时间。

- usr-local-ΕΨΗΕΛΩΝ

Answer 2

一切都取决于您的硬件，但很可能您的网络是瓶颈。除了将查询限制为只读取实际使用的列之外，执行选择操作的速度已经达到最快。这里涉及到缓存，当您连续两次执行时，第二次应该会更快，因为数据被缓存在内存中。执行dbcc dropcleanbuffers以检查缓存效果。

如果您想尽可能快地完成，请尝试在T-SQL中实现处理代码，这样它就可以直接在服务器上操作数据。

另一个提高速度的好方法是将正在读取的表放在一个磁盘上（查看文件组），并将要写入的表放在另一个磁盘上。这样，一个磁盘可以进行连续读取，而另一个磁盘可以进行连续写入。如果两个操作发生在同一个磁盘上，磁盘头会来回移动，严重降低性能。

如果您编写的逻辑不能在T-SQL中完成，还可以查看SQL CLR。

另一个提示：当您从表中选择*时，如果可能，请使用数据读取器。这样您就不必首先将整个内容物化到内存中。

GJ

- gjvdkamp

Answer 3

通常将列名包含在选择列表中是一个好主意，但是对于今天的RDBMS（关系型数据库管理系统），这不会有太大的影响。只有在限制所选列时才会看到差异。总的来说，包含列名是个好习惯。但就你描述的情况而言，似乎选择操作确实比插入操作更慢，是的，select * from table_name 确实是从表中读取所有行和列的最快方法。

- mohsensajjadi

---

+1 因为这很有帮助，包含了一个好的观点，我同意其他答案中没有提到。 - Booji Boy