Paralelní spouštění SQL příkazů

Paralelní spouštění SQL příkazů

Pomocí CLR procedury ExecSQL lze paralelně spouštět zadané SQL příkazy. Vhodné jako náhrada hromadně prováděných akcí nad izolovanými řádky, jejichž výsledky se navzájem neovlivňují (např. rozúčtování dokladů, generování odpisů majetku apod.). Hlavička procedury:

CREATE PROCEDURE [dbo].[ExecSQL]
    @sXMLinput NVARCHAR(MAX),
    @maxDegreeOfParallelism INT = 16,
    @ResultXml NVARCHAR(MAX) OUTPUT
AS EXTERNAL NAME [ParallelExecSQL].[ParallelExecSQL.CParallelExecSQL].ExecSQL;

Vstupní SQL příkazy

Jednotlivé paralelně spouštěné SQL příkazy se zadávají ve vstupním parametru @sXMLinput ve formátu:

<?xml version="1.0" encoding="utf-8"?>
<dataset>
  <config>
    <DbServer>server_name</DbServer>
    <DbName>database_name</DbName>
  </config>
<SqlCmds>
  <SqlCmd><Id>1</Id><ExecSQL>SQL_command 1</ExecSQL></SqlCmd>
  <SqlCmd><Id>2</Id><ExecSQL>SQL_command 2</ExecSQL></SqlCmd>
...
  <SqlCmd><Id>1000</Id><ExecSQL>SQL_command 1000</ExecSQL></SqlCmd>
</SqlCmds>
</dataset>

Konkrétní příklad:

-- příklad použití (hromadné rozúčtování dokladů)
DECLARE @xml NVARCHAR(MAX) =
N'<?xml version="1.0" encoding="utf-8"?>
<dataset>
  <config>
    <DbServer>server_name</DbServer>
    <DbName>database_name</DbName>
  </config>
<SqlCmds>
  <SqlCmd><Id>1</Id><ExecSQL>exec Proved_Rozuctovani @idHdok = 488812</ExecSQL></SqlCmd>
  <SqlCmd><Id>2</Id><ExecSQL>exec Proved_Rozuctovani @idHdok = 488810</ExecSQL></SqlCmd>
...
  <SqlCmd><Id>99</Id><ExecSQL>exec Proved_Rozuctovani @idHdok = 488677</ExecSQL></SqlCmd>
  <SqlCmd><Id>100</Id><ExecSQL>exec Proved_Rozuctovani @idHdok = 488676</ExecSQL></SqlCmd>
</SqlCmds>
</dataset>';

DECLARE @ResultXml NVARCHAR(MAX);
EXEC dbo.ExecSQL @sXMLinput = @xml, @maxDegreeOfParallelism = 16, @ResultXml = @ResultXml OUTPUT;
SELECT @ResultXml;

Výstup - výsledky

Výsledky volání jednotlivých SQL příkazů jsou k dispozici ve výstupní proměnné @ResultXml v syntaxi:

<Results>
    <Result>
        <Id>3</Id>
        <Status>0</Status>
        <Text></Text>
    </Result>
    <Result>
        <Id>2</Id>
        <Status>0</Status>
        <Text></Text>
    </Result>
    <Result>
        <Id>1</Id>
        <Status>0</Status>
        <Text></Text>
    </Result>
</Results>

Podle elementu Id lze spárovat volaný SQL příkaz s jeho výsledkem. V položce Status je číslo chyby, v položce Text pak její popis.

Hromadné rozúčtování dokladů

Dynamický příklad na hromadné paralelní rozúčtování dokladů.

-- výběr dokladů, odpovídají jim IHDOKy níže
declare @pocHDOK int = 5000;  -- počet dokladů, které se mají zpracovat
drop table if exists #H;
select top (@pocHDOK ) idHdok 
    into #H
  from (
         select distinct idhdok 
          from qUCETZAP
          where UCET_OBD not like '%.00'
            and UCET_OBD not like '%.99'
       ) HDOK
order by idhdok desc;

SELECT 'SELECT * FROM #H;' inf, count(*) as pocetHDOK  FROM #H;

alter table UCETZAP disable trigger all;
     update UCETZAP set UZCISDOK = CASE charindex( 'test', UZCISDOK)
                             	      WHEN 0 THEN UZCISDOK + 'test'
                             	      WHEN 1 THEN replace(UZCISDOK, 'test','')
                                   END
       where IDHDOK in (SELECT idHdok from #H)
         and SALDO_PRIPAD = 0
         and VLTYPUCETZAP = 0;
alter table UCETZAP enable trigger all;

--## priprava vstupního XML pro volani ExecSQL
declare @xml     nvarchar(max) 
declare @config  xml = ( select @@servername as [DbServer], db_name() as [DbName] for xml path('config'), type )   /*neni treba upravovat*/   select @config  [@config ]
declare @sqlCmds xml = 
(
    select
      row_number() over (order by idHdok asc )             as [Id]
    , concat('exec Proved_Rozuctovani @idHdok = ', idHdok) as [ExecSQL]
    from #H
    for xml path('SqlCmd'), root('SqlCmds'), type
);
set @xml = 
( 
    select
        @config   as [*]
      , @sqlCmds  as [*]
    for xml path(''), root('dataset')
) 
-- debug výpis
--select @xml as [XML];

select concat('exec Proved_Rozuctovani @idHdok = ', idHdok) as [ExecSQL] from #H

declare @ResultXml nvarchar(max);

/* 
  Dynamické načítání počtu vláken dostupných procesu SQL Serveru.
  Tento počet je omezen menší z následujících hodnot:
    1. Počet logických procesorů dostupných operačnímu systému** – to závisí na hardwaru a konfiguraci operačního systému.
    2. Počet logických procesorů podporovaných edicí SQL Serveru** – různé edice SQL Serveru (např. Standard, Enterprise) mají různá omezení na počet podporovaných procesorů.

Například:
- SQL Server Standard Edition podporuje maximálně 24 logických procesorů.
- SQL Server Enterprise Edition podporuje všechny dostupné procesory, které operační systém umožňuje.
*/
declare @UsableCPUs int = 
( 
    select count(*)
    from sys.dm_os_schedulers
    where status = 'VISIBLE ONLINE'
      and scheduler_id < 255
);

-- vlastní volání paralelního zpracování SQL příkazů
exec dbo.ExecSQL @sXMLinput = @xml
               , @maxDegreeOfParallelism = @UsableCPUs
               , @ResultXml = @ResultXml output;

-- výsledek volání je v @ResultXml, lze ulozit do tabulky, nebo vypsat
select @ResultXml;
go