Select中的表值构造函数最大行数限制 [英] Table Valued Constructor Maximum rows limit in Select

问题描述

我有一个 Table Valued Constructor，通过它我可以选择大约 100 万 条记录.它将用于更新另一个表.

SELECT *从(值(100,200,300)，(100,200,300),(100,200,300),(100,200,300),.......... --100 万条记录(100,200,300)) tc (proj_d, period_sid, val)

这是我的原始查询:

更新: 尝试使用 TRY/CATCH 块捕获错误消息或错误编号，但没有使用仍然与之前的图像相同的错误

BEGIN 尝试选择 *从(值(100,200,300)，(100,200,300),(100,200,300),(100,200,300),.......... --100 万条记录(100,200,300)) tc (proj_d, period_sid, val)结束尝试开始捕捉选择 Error_number(),错误信息()结束捕捉

为什么它不执行是 Select 中的 Table Valed 构造函数有任何限制.我知道 Insert 它是 1000 但我选择这里.

解决方案

没有相关的硬编码限制(65,536 * 4KB 的网络数据包大小为 268 MB，您的脚本长度远不及此)，尽管不建议使用此限制大量行的方法.

您看到的错误是由客户端工具而非 SQL Server 引发的.如果你在动态 SQL 编译中构造 SQL String 至少能够启动成功

DECLARE @SQL NVARCHAR(MAX) = '(100,200,300),';SELECT @SQL = 'SELECT * FROM (VALUES' + REPLICATE(@SQL, 1000000) + '(100,200,300)) tc (proj_d, period_sid, val)';SELECT @SQL AS [处理指令(x)]FOR XML 路径('')SELECT DATALENGTH(@SQL)/1048576.0 AS [长度以 MB 为单位] --30.517705917执行(@SQL)；

虽然我在大约 30 分钟的编译时间后杀死了上面的内容，但它仍然没有产生一行.文字值需要作为常量表存储在计划本身中，并且 SQL Server 花费

它试图将一个元素推送到一个已达到容量的 Token 向量上，但由于没有足够大的连续内存区域，它尝试调整大小失败.因此，该语句甚至从未涉及到服务器.

向量容量每次增加 50%(即遵循 此处的序列).向量需要增长到的容量取决于代码的布局方式.

以下需要从容量 19 增长到 28.

SELECT * FROM(值(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300)) tc (proj_d, period_sid, val)

以下只需要2个大小

SELECT * FROM (VALUES (100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300,tproj)

以下需要> 63 且<= 94 的容量.

SELECT *发件人(值(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300)) tc (proj_d, period_sid, val)

对于如案例 1 所示的一百万行，向量容量需要增长到 3,543,306.

您可能会发现以下任一情况都会使客户端解析成功.

1. 减少换行次数.
2. 重新启动 SSMS，希望在地址空间碎片较少时成功请求大容量连续内存.

然而，即使你成功地将它发送到服务器，它也只会在执行计划生成期间终止服务器，如上所述.

使用导入导出向导加载表格会好得多.如果您必须在 TSQL 中执行此操作，您会发现将其分成较小的批次和/或使用另一种方法(例如分解 XML)将比表值构造函数执行得更好.例如，以下在我的机器上在 13 秒内执行(尽管如果使用 SSMS，您仍然可能需要分成多个批次，而不是粘贴大量的 XML 字符串文字).

DECLARE @S NVARCHAR(MAX) = '<x proj_d="100" period_sid="200" val="300"/>';声明@Xml XML = REPLICATE(@S,1000000);选择x.value('@proj_d','int'),x.value('@period_sid','int'),x.value('@val','int')FROM @Xml.nodes('/x') c(x)

I have a Table Valued Constructor through which am Selecting around 1 million records. It will be used to update another table.

SELECT *
FROM   (VALUES (100,200,300),
               (100,200,300),
               (100,200,300),
               (100,200,300),
               .....
               ..... --1 million records
               (100,200,300)) tc (proj_d, period_sid, val) 

Here is my original query : https://www.dropbox.com/s/ezomt80hsh36gws/TVC.txt?dl=0#

When I do the above select it is simply showing Query completed with errors with showing any error message.

Update : Tried to catch the error message or error number using TRY/CATCH block but no use still same error as previous image

BEGIN try
    SELECT *
    FROM   (VALUES (100,200,300),
                    (100,200,300),
                    (100,200,300),
                    (100,200,300),
                    .....
                    ..... --1 million records
                    (100,200,300)) tc (proj_d, period_sid, val) 
END try

BEGIN catch
    SELECT Error_number(),
           Error_message()
END catch 

Why it is not executing is there any limit for Table Valed constructor in Select. I know for Insert it is 1000 but am selecting here.

解决方案

There's no relevant hard coded limit (65,536 * Network Packet Size of 4KB is 268 MB and your script length is nowhere near that) though it is inadvisable to use this method for a large amount of rows.

The error you are seeing is thrown by the client tools not SQL Server. If you construct the SQL String in dynamic SQL compilation is able to at least start successfully

DECLARE @SQL NVARCHAR(MAX) = '(100,200,300),
';

SELECT @SQL = 'SELECT * FROM (VALUES ' + REPLICATE(@SQL, 1000000) + '
(100,200,300)) tc (proj_d, period_sid, val)';

SELECT @SQL AS [processing-instruction(x)]
FOR XML PATH('')

SELECT DATALENGTH(@SQL) / 1048576.0 AS [Length in MB] --30.517705917

EXEC(@SQL);

Though I killed the above after ~30 minutes compilation time and it still hadn't produced a row. The literal values need to be stored inside the plan itself as a table of constants and SQL Server spends a lot of time trying to derive properties about them too.

SSMS is a 32 bit application and throws a std::bad_alloc exception whilst parsing the batch

It tries to push an element onto a vector of Token that has reached capacity and its attempt to resize fails due to unavailability of a large enough contiguous area of memory. So the statement never even makes it as far as the server.

The vector capacity grows by 50% each time (i.e. following the sequence here). The capacity that the vector needs to grow to depends on how the code is laid out.

The following needs to grow from a capacity of 19 to 28.

SELECT * FROM 
(VALUES 
(100,200,300),
(100,200,300),
(100,200,300),
(100,200,300),
(100,200,300),
(100,200,300)) tc (proj_d, period_sid, val)

and the following only needs a size of 2

SELECT * FROM (VALUES (100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300),(100,200,300)) tc (proj_d, period_sid, val)

The following needs a capacity of > 63 and <= 94.

SELECT *
FROM   (VALUES 
      (100,
       200,
       300),
      (100,
       200,
       300),
      (100,
       200,
       300),
      (100,
       200,
       300),
      (100,
       200,
       300),
      (100,
       200,
       300)
       ) tc (proj_d, period_sid, val) 

For a million rows laid out as in case 1 the vector capacity needs to grow to 3,543,306.

You might find that either of the following will allow the client side parsing to succeed.

1. Reduce the number of line breaks.
2. Restarting SSMS in the hope that the request for large contiguous memory succeeds when there is less address space fragmentation.

However even if you do successfully send it to the server it will only end up killing the server during execution plan generation anyway as discussed above.

You'll be much better off using the import export wizard to load the table. If you must do it in TSQL you'll find breaking it into smaller batches and/or using another method such as shreding XML will perform better than Table Valued Constructors. The following executes in 13 seconds on my machine for example (though if using SSMS you'd still likely have to break up into multiple batches rather than pasting a massive XML string literal).

DECLARE @S NVARCHAR(MAX) = '<x proj_d="100" period_sid="200" val="300" />
' ; 

DECLARE @Xml XML = REPLICATE(@S,1000000);

SELECT 
    x.value('@proj_d','int'),
    x.value('@period_sid','int'),
    x.value('@val','int')
FROM @Xml.nodes('/x') c(x)

这篇关于Select中的表值构造函数最大行数限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！