SQL Server 2012会消耗过多的网络流量来获取所有数据

Question

今天我做了一个测试，使用AnyDAC (firedac)来获取我的远程SQL Server2012中所有可用的数据。

我从中获取数据的表有以下简单的列：

1. date - (size 3 byte)
2. time - (max 5 byte)
3. int - (4 byte)
4. bit - (1 byte)
5. int - (4 byte)
6. float - (4 byte)
7. float - (4 byte)
8. int - (4 byte)
9. int - (4 byte)

总行大小应为最大33字节。

在获取了表中的所有可用行(超过214万行)后，我通过FireDAC检查了收到的tcp流量，并观察到它大约是280MB，这意味着每行需要大约130字节，而我的期望值接近33字节。

另一种测量方法是使用在服务器端定义的存储过程，该存储过程具有到上表的insert sql，并使用AnyDAC的Array DML特性调用该存储过程。数组大小是300K，我总共使用它添加了1880万条记录。用于它的流量实现为2.85 GB。(因此每行150字节)

在FireDAC或SQL Server端是否有任何配置可以减少流量？显然，这里出了点问题。有什么建议吗？

谢谢。

信息输出：

================================
Connection definition parameters
================================
User_Name=*****
Password=*******
SERVER=sql.***.gen
ApplicationName=Bist
Workstation=NB
DATABASE=BIST
MARS=yes
DriverID=MSSQL
================================
FireDAC info
================================
Tool = D18 Architect
FireDAC = 8.0.1 (Build 3279)
Platform = Windows 32 bit
Defines = AnyDAC_Unicode;AnyDAC_DBX;AnyDAC_NOLOCALE_META;
  AnyDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
      Found [SQL Server Native Client 11.0]
Driver Manager version = 03.80.7601.0000
================================
Session info
================================
Current catalog = 
Current schema = dbo
Driver name = sqlncli11.dll
Driver version = 11.00.2100
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 11.00.2100

Answer 1

您到SQL数据库服务器的网络流量不仅仅包括您希望传输的数据(正如您通过看到比行数据多得多的流量所观察到的)

在开始之前，让我先声明几件事:我不是网络流量和协议方面的专家，但我已经花了相当多的时间来了解它们。我也不太了解特定的DAC软件、查询组合等将采用什么协议来为您返回所请求的数据。话虽如此，我不能给出确切的例子。但这些概念应该仍然适用。

与SQL server的通信是通过各种协议进行的，但为了便于讨论，我们将只关注一种协议: TDS (表格数据流)协议。(有关SQL Server协议的更多信息，请访问：http://msdn.microsoft.com/en-us/library/ff420673(v=sql.105).aspx。TDS使用TCP协议，因此TDS分组只不过是包装在TCP分组中的规范)。

对于TCP，通信基本上是请求/响应类型的交换，涉及许多请求/响应/确认分组。再说一次，我不是这方面的专家，但你可以在http://en.wikipedia.org/wiki/Transmission_Control_Protocol上阅读更多

除了您引用的行大小之外，通信的“确认”(或确认)方面本身就会产生相当大的开销。您可以在下图中看到一个这样的示例，其中客户端应用程序和SQL服务器之间正在进行通信：

​

​

此外，在从DB服务器向应用程序发送数据包的实际数据包中，TCP数据包协议本身以及TDS数据包中都有大量开销。

让我们只关注TDS数据包的开销，它会增加您的整体数据传输有效负载。以下是可在TDS数据包中找到的所有文档(http://msdn.microsoft.com/en-us/library/dd340794.aspx)：

• ALTMETADATA
• ALTROW
• COLINFO
• COLMETADATA
• DONE
• DONEINPROC
• DONEPROC
• ENVCHANGE
• ERROR
• FEATUREEXTACK
• INFO
• LOGINACK
• NBCROW
• OFFSET
• ORDER
• RETURNSTATUS
• RETURNVALUE
• ROW <-这是您的数据行

的位置

实际上，您的实际数据(在上面描述的情况下)位于数据包的行部分中。

下面的屏幕截图显示了1个TDS数据包的ASCII版本。数据包帧的突出显示部分(在右侧)是实际的行数据。所有剩下的都是开销，使得整个通信和辅助的事情只是工作。

​

​

这甚至不包括TCP本身产生的开销(尽管有必要)。

总而言之

您正在传输非常大量的数据，并且这些数据总是会有开销。

• 如果最终要汇总数据，则在发送数据之前在DB服务器上进行汇总。
• 如果要搜索数据，请将搜索负载卸载到DB服务器，然后只返回所需内容
• 如果需要列出数据(每行)，则可能每页仅显示结果的子集-仅通过SELECT TOP 50 .... WHERE ....

之类的方式返回所需的结果

根据您对数据的实际使用，还有许多其他解决方案，但这些只是一些想法。

希望这能有所帮助！