Table Valued Constructor Maximum rows limit in Select

Question

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.

check this link: http://stackoverflow.com/questions/14790548/updating-4-million-records-in-sql-server-using-list-of-record-ids-as-input — Laxmi, Nov 07 '16 at 08:44
All of the literal values need to be compiled into the execution plan. How long does it take to compile before you see the error? — Martin Smith, Nov 07 '16 at 08:50
I've tried this and it is still struggling on eight minutes later with no results or error (yet) `DECLARE @SQL NVARCHAR(MAX) = '(100,200,300),' ; SELECT @SQL = 'SELECT * FROM (VALUES ' + REPLICATE(@SQL,1000000) + '(100,200,300)) tc (proj_d, period_sid, val)';EXEC(@SQL)` — Martin Smith, Nov 07 '16 at 08:53
I let it run for 28 minutes then killed it. Probably it was doing something like this http://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values/8640583#8640583 — Martin Smith, Nov 07 '16 at 09:16
TRY/CATCH doesn't catch errors with severity 10 or less. Try get the error number/message right after the SELECT. — TT., Nov 07 '16 at 09:30
@TT.- Tried it result is `NULL` for the both the functions :/ — Pரதீப், Nov 07 '16 at 09:35
It would be interesting to see the estimated and actual (if possible) query plans. Use pastebin.com or something similar to post the plan's XML. Also, you can try to find the tipping point, the number of values after which the query breaks. — Vladimir Baranov, Nov 07 '16 at 09:46
@VladimirBaranov - unfortunately execution plan also not getting generated. Will post my original 1 million code — Pரதீப், Nov 07 '16 at 09:54
Most likely the engine runs out of memory while compiling the plan. The memory for the plans and for compilation is separate to the buffer pool for data, as far as I understand. It never actually tries to run the query. It doesn't get to this stage. That's why `TRY ... CATCH` don't catch anything. Try to run a smaller query (10K - 20K rows) and look for `CompileMemory` and `CachedPlanSize` in the XML of the actual plan. You'll get an idea how much memory would be needed for 1M rows. — Vladimir Baranov, Nov 07 '16 at 10:26
Running it directly in SSMS it certainly fails much faster. After 3-5 seconds so something is different from the EXEC case. — Martin Smith, Nov 07 '16 at 10:28
Have you tried creating a TRACE in SQL Server Profiler for "Errors and Warnings" to see if that gives any indication of the error reported in SSMS? — TT., Nov 07 '16 at 11:01

score 3 · Accepted Answer · edited May 23 '17 at 12:10

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.

Reduce the number of line breaks.
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)

score 0 · Answer 2 · answered Nov 07 '16 at 08:26

0

Check this for query limitations: https://msdn.microsoft.com/en-us/library/ms143432.aspx

answered Nov 07 '16 at 08:26

Dexion

1,101
8
14

Table Valued Constructor Maximum rows limit in Select

2 Answers2

Linked