时间戳

首页 > 解决方案 > 活动监视器上的 INSERT 语句昂贵的查询

json - 活动监视器上的 INSERT 语句昂贵的查询

问题描述

我有一个大约 300k 的对象列表。我正在通过 Web API 批量发送。由于这个对象是多层次的，我决定分批发送 1000 个转换为 JSON 字符串的项目。目前，我看到每秒插入大约 50 条记录，我的估计需要大约 1 个小时才能完成。

在我的 SQL Server 中，我有一个接受 JSON 字符串并使用 OPENJSON 的存储过程。数据被插入到一个 TempTable 中，然后我重复使用 while 如图所示（不确定是否有更好的方法）

While (Select Count(*) From #temporganisations Where process_ind = 0) > 0
Begin
  select top 1
     @orgid=torg.orgid
  from
     #temporganisations torg
  where torg.process_ind=0
  exec AddOrganisation... retvalid out
  exec AddLocation...retvalid
  exec AddContact...retvalid
  etc
  .
  .
  .
  delete from #temporganisations        
    where orgid= @orgid
 
end

如图所示，在这个存储过程中调用了多个存储过程。每个存储过程在执行 INSERT 语句之前都有重复检查。我正在努力加快这个过程。完成这个过程确实需要很长时间。所以我发现了活动监视器，并注意到如下所示的“最近的昂贵查询”显示了被调用的 INSERT 语句

在此处输入图像描述

我只是想知道我是否可以做些什么来提高这些 INSERT 的性能，这是其中一个 INSERT 的执行计划

在此处输入图像描述

我在某处读到我可以直接执行 INSERT 但存储过程将是巨大的，因为我需要添加重复检查。我还阅读了有关使用变量表而不是临时表的信息，但我还阅读了一篇文章，变量表适用于小型数据集。提前致谢。

下面是 XML 结构。如您所见，我需要将每个子节点链接到作为组织的父节点。

<Organisations>
<Organisation orgRecordClass="R43">
  <Name>ORGANIZATION X</Name>
  <Date>
    <Type value="Operational" />
    <Start value="1991-04-01" />
    <End value="1994-03-31" />
  </Date>
  <OrgId root="5161f" assigningAuthorityName="H51" extension="R34" />
  <Status value="Inactive" />
  <LastChangeDate value="2013-05-08" />
  <GeoLoc>
    <Location>
      <AddrLn1>458 HOMER ROAD</AddrLn1>
      <Town>LONDON</Town>
      <PostCode>E1 8PL</PostCode>
      <Country>ENGLAND</Country>
      <UPRN>21521</UPRN>
    </Location>
  </GeoLoc>
  <Contacts>
    <Contact type="tel" value=" 233344" />
    <Contact type="fax" value=" 233355" />
  </Contacts>
  <Roles>
    <Role id="R12" uniqueRoleId="1" primaryRole="true">
      <Date>
        <Type value="Operational" />
        <Start value="1991-04-01" />
        <End value="1994-03-31" />
      </Date>

    </Role>
  </Roles>
  <Rels>
    <Rel id="RE6" uniqueRelId="58005">
      <Date>
        <Type value="Operational" />
        <Start value="1991-04-01" />
        <End value="1994-03-31" />
      </Date>
      <Status value="Inactive" />

    </Rel>
  </Rels>

  <Succs>
    <Succ uniqueSuccId="12">
      <Date>
        <Type value="Legal" />
        <Start value="1993-04-01" />
      </Date>
      <Type>Successor</Type>
      <Target>
        <OrgId root="5161f" assigningAuthorityName="H51" extension="R561" />
        <PrimaryRoleId id="R12" uniqueRoleId="37607" />
      </Target>
    </Succ>       
    <Succ uniqueSuccId="12">
      <Date>
        <Type value="Legal" />
        <Start value="1993-04-01" />
      </Date>
      <Type>Successor</Type>
      <Target>
        <OrgId root="5161f" assigningAuthorityName="H51" extension="R561" />
        <PrimaryRoleId id="R12" uniqueRoleId="37607" />
      </Target>
    </Succ>
  </Succs>
</Organisation>

标签： jsonsql-serverinsertopen-json

解决方案

首先是遵循在 SQL Server 中运行逐行进程的基本最佳实践。这意味着使用真正的游标而不是奇怪的类似游标的循环，并将整个循环包装在事务中。

begin transaction

declare c cursor local for 
   select * from #temporganisations
open c

fetch next from c into @orgid, @name, ...
while @@fetch_status = 0
begin
  
  exec AddLocation...
  exec AddContact...
  etc
  .
  .
  .
  fetch next from c into @orgid, @name, ...
end

commit transaction

如果没有事务，则必须在每次 INSERT 之后物理刷​​新事务日志。

第二件事是将其转换为面向批处理的过程。您可以重写每个存储过程以接受多个输入行（TVP、JSON 或 XML），或者只对每个存储过程进行编码以从 #temporganisations 读取。存储过程可以使用会话中声明的任何临时表，因此您可以通过在调用 proc 之前插入临时表将批量数据传递到存储过程。

例如

create or alter procedure AddLocation
as
begin
   insert into Location (Name, Address)
   select distinct Name, Address
   from #temporganisations
end

推荐阅读