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月12日T18:21:48.5419289-05:00
'''
 [1]: https://i.sstatic.net/xR3lM.png
 [2]: https://i.sstatic.net/Ayx7F.png

• Learning_DBAdmin: 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?
• D-K: See if this discussion helps: dba.stackexchange.com/q/229698
• NBender: @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...

Answer 1

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)

• NBender: 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)