文章/答案/技术大牛

发布

社区首页 >问答首页 >使用嵌入式交叉应用openjson选择into和insert insert性能

问使用嵌入式交叉应用openjson选择into和insert insert性能
EN

Database Administration用户

提问于 2021-11-12 23:50:34

回答 1查看 421关注 0票数 0

注:更新11/15/2021，以回应意见反馈。

Server 2016在性能方面似乎有一些奇怪的小角落。

版本：

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  (Build 14393: ) (Hypervisor)

我已经确定了一个例子，在时间2886 in中进行选择进入运行。运行相同的查询，但对现有表执行插入所需的时间大约是现有表的10倍(20717 as )。如果对临时表执行select into，然后从select *对临时表执行insert into，则总的运行时间与执行select into (3167 as )的顺序相同。

在所有情况下，目标表都是没有索引的堆，该堆是使用select into创建的。

使用完整select查询的insert into的统计数据导致对神秘的"Worktable“对象的过度读取：

桌子‘工作表’扫描计数1，逻辑读取197253，物理读取1，先读读取0，lob逻辑读取3500000，lob物理读取0，lob预读读取0.

其他两种情况下的类似统计量都是零。

select into生成此查询计划(粘贴计划：https://www.brentozar.com/PasteThePlan/?id=HkiRkme_T)：

这是插入到(粘贴计划：https://www.brentozar.com/pastetheplan/?id=BJnrBQldK)的查询计划：

这个小警告图标有这样的信息：

查询内存授权检测到"ExcessiveGrant"，这可能会影响可靠性。授权大小:初始1024 KB，最终1024 KB，使用0 KB。

下面是我使用的SQL：

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

以下是经过适当编辑的统计数据：

*********************** 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

sql-server

performance

insert

json

回答 1

Database Administration用户

发布于 2021-11-13 01:10:38

你在运行什么版本和版本？我得到了

*********************** 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.

在我的工作站上

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  (Build 22000: ) (Hypervisor)

票数 1

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/302568

复制

相似问题

问使用嵌入式交叉应用openjson选择into和insert insert性能
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用嵌入式交叉应用openjson选择into和insert insert性能EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用嵌入式交叉应用openjson选择into和insert insert性能
EN