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

查看：122
本文介绍了选择中的表值构造函数最大行数限制的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表值构造器，通过它选择了 100万条记录。它将用于更新另一个表。 

 选择* 
 FROM（值（100,200,300），
（100,200,300），
（ 100,200,300），
（100,200,300），
 ..... 
 ..... --1百万条记录
（100,200,300））tc（proj_d，period_sid，val）

这是我原来的查询：



更新：尝试使用 TRY / CATCH 块捕获错误消息或错误号，但仍不使用与先前图像相同的错误

  BEGIN尝试
选择* 
 FROM（值（100,200,300），
（100,200,300），
（100,200,300），
 （100,200,300），
 ..... 
 ....--1百万条记录
（100,200,300））tc（proj_d，period_sid，val）
 END try 
 
开始捕获
选择Error_number（），
 Error_message（）
结束捕获

为什么它不执行， Select 中的Table Valed构造函数没有任何限制。我知道对于插入，它是 1000 ，但是我在这里选择。

解决方案

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



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

  DECLARE @SQL NVARCHAR（MAX）=' （100,200,300），
'； 
 
 SELECT @SQL =‘SELECT * FROM（VALUES’+ REPLICATE（@SQL，1000000）+’
（100,200,300））tc（proj_d，period_sid，val）'; 
 
选择@SQL AS [处理指令（x）] 
 FOR XML PATH（''）
 
选择DATALENGTH（@SQL）/ 1048576.0 AS [长度[以MB为单位] --30.517705917 
 
 EXEC（@SQL）;

尽管我在大约30分钟的编译时间后杀死了上面的代码，但仍然没有产生任何结果。文字值需要作为常量表存储在计划本身中，并且SQL Server花费



它试图将元素推到已达到容量的Token向量上，由于无法提供足够大的连续内存区域，因此尝试调整大小失败。因此，该语句甚至不会到达服务器。



向量容量每次都会增长50％（例如，跟随此处的顺序）。向量需要增长的容量取决于代码的布局方式。



以下内容需要从19的容量增长到28。

 选择*从
（值
（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 （值（100,200,300），（100,200,300），（100,200,300），（100,200,300），（100,200,300），（100,200,300））tc（proj_d，period_sid，val）

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

  SELECT * 
 FROM（值
（100，
 200，
 300），
（100，
 200，
 300）， 
（100，
 200，
 300），
（100，
 200，
 300），
（100，
 200，
 3 00），
（100，
 200，
 300）
）tc（proj_d，period_sid，val）

对于第一种情况下布置的一百万行，向量容量需要增长到3,543,306。



发现以下任何一项将使客户端解析成功。


  1. 减少换行的次数。

  2. 重新启动SSMS，以期在地址空间碎片较少的情况下成功请求大的连续内存。

即使您确实将其成功发送到服务器，也只会如上所述在执行计划生成过程中最终杀死服务器。



使用导入导出向导以加载表。如果必须在TSQL中执行此操作，则会发现将其分成较小的批处理和/或使用其他方法（例如，分解XML）将比表值构造函数更好。例如，以下代码在我的计算机上在13秒内执行（尽管如果使用SSMS，您仍然可能必须分成多个批处理，而不是粘贴大量的XML字符串文字）。

  DECLARE @S NVARCHAR（MAX）='< x proj_d = 100 period_sid = 200 val = 300 /> 
’； 
 
 DECLARE @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)

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

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆