SQL Server select into vs. insert into performance with embedded cross apply openjson

Question

Note: updated 11/15/2021 to address comment feedback.

It seems SQL Server 2016 has some odd little corners when it comes to performance.

Version:

Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I have identified a case where doing a select into runs in time 2886ms. Running the same query but doing an insert into an existing table takes approximately 10 times as long (20717ms). If I do a select into to a temporary table and then an insert into from a select * on the temporary table the total run time is on the same order as doing a select into (3167ms).

The destination table in all cases is a heap with no indexes which is created using select into.

The stats for the insert into with the full select query are causing excessive reads for the mysterious "Worktable" object:

Table 'Worktable'. Scan count 1, logical reads 197253, physical reads 1, read-ahead reads 0, lob logical reads 3500000, lob physical reads 0, lob read-ahead reads 0.

The analogous stats in the other two cases are all zeros.

The select into generates this query plan (Paste The Plan: https://www.brentozar.com/PasteThePlan/?id=HkiRkme_t):

query plan for select into

And this is the query plan for the insert into (Paste The Plan: https://www.brentozar.com/pastetheplan/?id=BJnrBQldK):

query plan for insert into

The little caution icon has this message:

The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 1024 KB, Final 1024 KB, Used 0 KB.

Here is the SQL I'm using:

create table dbo.nb_test(batch_id varchar(100), record_number bigint, record_data varchar(max), primary key (batch_id, record_number))
declare @batch int = 1
while @batch <= 10
    begin
        set @batch += 1

        ;with e1(n) as
        (
            select 1 union all select 1 union all select 1 union all 
            select 1 union all select 1 union all select 1 union all 
            select 1 union all select 1 union all select 1 union all select 1
        ), -- 10
        e2(n) as (select 1 from e1 cross join e1 as b),
        e3(n) as (select 1 from e1 cross join e2),
        e4(n) as (select 1 from e2 cross join e3),
        e100k as (select n = ROW_NUMBER() over (order by n) from e4)
        insert into dbo.nb_test
        select cast('batch-' + cast(@batch as varchar(100)) as varchar(100)) as batch_id
             , cast(n as bigint) as record_number
             , '[["' + + cast(n as varchar(max)) + '"' + replicate(',"some data"', 99) + ']]' as record_data
        from e100k
    end

select max(record_number), count(*) from dbo.nb_test

set statistics io on
set statistics time on

drop table if exists dbo.nb_parsed
go

print '
*********************** select into'
go

select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
into dbo.nb_parsed
from (   
    select rd.batch_id, rd.record_number, ca.*   
    from dbo.nb_test rd   
    cross apply openjson(record_data)      
    with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
    where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go

print '
*********************** insert into inner'
go

insert into dbo.nb_parsed with(tablock)
select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
from (   
    select rd.batch_id, rd.record_number, ca.*   
    from dbo.nb_test rd   
    cross apply openjson(record_data)      
    with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
    where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go

drop table if exists #t
go

print '
*********************** select into temp'
go

select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
into #t
from (   
    select rd.batch_id, rd.record_number, ca.*   
    from dbo.nb_test rd   
    cross apply openjson(record_data)      
    with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
    where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go

print '
*********************** insert from temp (tablock)'
go

insert into dbo.nb_parsed with(tablock)
select * from #t
go

Here are the mildly edited statistics:

*********************** select into

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'nb_parsed'. Scan count 0, logical reads 60009, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 5, logical reads 8985, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(50000 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 11062 ms,  elapsed time = 2886 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

*********************** insert into inner

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 7 ms, elapsed time = 7 ms.
Table 'nb_parsed'. Scan count 0, logical reads 60001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 1, logical reads 8400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 197253, physical reads 1, read-ahead reads 0, lob logical reads 3500000, lob physical reads 0, lob read-ahead reads 0.

(50000 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 20422 ms,  elapsed time = 20717 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


*********************** select into temp

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 6 ms, elapsed time = 6 ms.
Table '#t__________________________________________________________________________________________________________________00000002D27D'. Scan count 0, logical reads 60004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 5, logical reads 8985, physical reads 0, read-ahead reads 796, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(50000 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 10845 ms,  elapsed time = 2768 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

*********************** insert from temp (tablock)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table '#t__________________________________________________________________________________________________________________00000002D27D'. Scan count 5, logical reads 10002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(50000 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1453 ms,  elapsed time = 399 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2021-11-12T18:21:48.5419289-05:00
'''


  [1]: https://i.stack.imgur.com/xR3lM.png
  [2]: https://i.stack.imgur.com/Ayx7F.png

Did you check the DDL of table which is created with select into? Is it having similar datatype as that of the one you are doing insert into? Worktable is having very high logical reads in the case of insert into inner compared to select into? Is the table having any clustered index while performing insert into? — Learning_DBAdmin, Nov 13, 2021 at 17:15
See if this discussion helps: dba.stackexchange.com/q/229698 — D-K, Nov 16, 2021 at 18:59
@D-K - I did come across that. I don't think it's the issue here since the select into temp / insert from select * approach doesn't have the issue. It must be something to do with how the query is executed when it is coupled with insert. Optimizer bug? Openjson bug? Hard to tell... — NBender, Nov 17, 2021 at 18:02

David Browne - Microsoft · Accepted Answer · 2021-11-13 01:10:38Z

What version and edition are you running? I get

*********************** select into

 SQL Server Execution Times:
   CPU time = 7281 ms,  elapsed time = 983 ms.

*********************** insert into inner

 SQL Server Execution Times:
   CPU time = 7938 ms,  elapsed time = 1180 ms.


*********************** select into temp

 SQL Server Execution Times:
   CPU time = 7438 ms,  elapsed time = 1010 ms.


*********************** insert from temp (tablock)

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 410 ms.

on my workstation

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) 
    Jan 25 2021 20:16:12 
    Copyright (C) 2019 Microsoft Corporation
    Standard Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22000: ) (Hypervisor)

My bad - was looking at wrong environment. It is standard edition: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) — NBender, Nov 13, 2021 at 20:41

Stack Exchange Network

SQL Server select into vs. insert into performance with embedded cross apply openjson

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
insert
json
or ask your own question.

Linked

Hot Network Questions

SQL Server select into vs. insert into performance with embedded cross apply openjson

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverperformanceinsertjson or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
insert
json
or ask your own question.