Jak uniknąć używania zapytania scalającego podczas wstawiania wielu danych za pomocą parametru xml?

Próbuję zaktualizować tabelę o tablicę wartości. Każdy element w tablicy zawiera informacje pasujące do wiersza w tabeli w bazie danych SQL Server. Jeśli wiersz już istnieje w tabeli, aktualizujemy ten wiersz o informacje w podanej tablicy. W przeciwnym razie wstawiamy nowy wiersz do tabeli. Zasadniczo opisałem upsert.

Teraz staram się to osiągnąć w procedurze przechowywanej, która przyjmuje parametr XML. Powodem, dla którego używam XML, a nie parametrów o wartościach przechowywanych w tabeli, jest to, że robiąc to drugie, będę musiał utworzyć niestandardowy typ w SQL i skojarzyć ten typ z procedurą przechowywaną. Jeśli kiedykolwiek zmieniłem coś w mojej procedurze składowanej lub schemacie db w dół drogi, musiałbym powtórzyć zarówno procedurę przechowywaną, jak i typ niestandardowy. Chcę uniknąć tej sytuacji. Poza tym przewaga TVP nad XML nie jest przydatna w mojej sytuacji, ponieważ rozmiar mojej tablicy danych nigdy nie przekroczy 1000. Oznacza to, że nie mogę użyć zaproponowanego rozwiązania: Jak wstawić wiele rekordów przy użyciu XML w SQL Server 2008

Również podobna dyskusja tutaj ( UPSERT - czy istnieje lepsza alternatywa dla MERGE lub @@ rowcount? ) Różni się od tego, o co proszę, ponieważ próbuję wstawić wiele wierszy do tabeli.

Miałem nadzieję, że użyję następującego zestawu zapytań, aby wstawić wartości z pliku XML. Ale to nie zadziała. To podejście powinno działać tylko wtedy, gdy dane wejściowe są jednym wierszem.

begin tran
   update table with (serializable) set select * from xml_param
   where key = @key

   if @@rowcount = 0
   begin
      insert table (key, ...) values (@key,..)
   end
commit tran

Kolejną alternatywą jest użycie wyczerpującego JEŚLI ISTNIEJE lub jednej z jego odmian poniższej formy. Ale odrzucam to z powodu nieoptymalnej wydajności:

IF (SELECT COUNT ... ) > 0
    UPDATE
ELSE
    INSERT

Następną opcją było użycie instrukcji Merge, jak opisano tutaj: http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html . Ale potem przeczytałem o problemach z kwerendą scalania tutaj: http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ . Z tego powodu staram się unikać scalania.

Więc teraz moje pytanie brzmi: czy jest jakaś inna opcja lub lepszy sposób na uzyskanie wielu upsert za pomocą parametru XML w procedurze przechowywanej SQL Server 2008?

Należy pamiętać, że dane w parametrze XML mogą zawierać niektóre rekordy, których nie należy UPSERTED, ponieważ są starsze niż bieżący rekord. W ModifiedDatetabeli XML i tabeli docelowej znajduje się pole, które należy porównać, aby ustalić, czy rekord powinien zostać zaktualizowany, czy odrzucony.

To, czy źródłem jest XML, czy TVP, nie robi dużej różnicy. Ogólna operacja jest zasadniczo:

1. AKTUALIZUJ istniejące wiersze
2. WSTAW brakujące wiersze

Robisz to w tej kolejności, ponieważ jeśli wpiszesz najpierw, wówczas wszystkie wiersze istnieją, aby uzyskać AKTUALIZACJĘ, i będziesz powtarzał pracę dla wszystkich właśnie wstawionych wierszy.

Poza tym istnieją różne sposoby na osiągnięcie tego i różne sposoby na zwiększenie dodatkowej wydajności.

Zacznijmy od absolutnego minimum. Ponieważ wyodrębnianie XML jest prawdopodobnie jedną z droższych części tej operacji (jeśli nie najdroższą), nie chcemy tego robić dwa razy (ponieważ mamy do wykonania dwie operacje). Tak więc tworzymy tabelę tymczasową i wyodrębniamy do niej dane z pliku XML:

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

INSERT INTO #TempImport (Field1, Field2, ...)
  SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
         tab.col.value('XQueryForField2', 'DataType') AS [Field2],
         ...
  FROM   @XmlInputParam.nodes('XQuery') tab(col);

Stamtąd wykonujemy AKTUALIZACJĘ, a następnie WSTAWIĆ:

UPDATE tab
SET    tab.Field1 = tmp.Field1,
       tab.Field2 = tmp.Field2,
       ...
FROM   [SchemaName].[TableName] tab
INNER JOIN #TempImport tmp
        ON tmp.IDField = tab.IDField
        ... -- more fields if PK or alternate key is composite

INSERT INTO [SchemaName].[TableName]
  (Field1, Field2, ...)
  SELECT tmp.Field1, tmp.Field2, ...
  FROM   #TempImport tmp
  WHERE  NOT EXISTS (
                       SELECT  *
                       FROM    [SchemaName].[TableName] tab
                       WHERE   tab.IDField = tmp.IDField
                       ... -- more fields if PK or alternate key is composite
                     );

Teraz, gdy mamy już podstawową operację, możemy zrobić kilka rzeczy, aby zoptymalizować:

1. przechwytuj @@ ROWCOUNT wkładki do tabeli temp i porównaj z @@ ROWCOUNT z AKTUALIZACJI. Jeśli są takie same, możemy pominąć WSTAW

2. przechwytywanie wartości identyfikatorów zaktualizowanych za pomocą klauzuli OUTPUT i usuwanie tych z tabeli temp. WSTAWKA nie potrzebujeWHERE NOT EXISTS(...)

3. JEŻELI w przychodzących danych są wiersze, których nie należy synchronizować (tzn. Nie wstawiać ani nie aktualizować), należy je usunąć przed wykonaniem UPDATE

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

DECLARE @ImportRows INT;
DECLARE @UpdatedIDs TABLE ([IDField] INT NOT NULL);

BEGIN TRY

  INSERT INTO #TempImport (Field1, Field2, ...)
    SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
           tab.col.value('XQueryForField2', 'DataType') AS [Field2],
           ...
    FROM   @XmlInputParam.nodes('XQuery') tab(col);

  SET @ImportRows = @@ROWCOUNT;

  IF (@ImportRows = 0)
  BEGIN
    RAISERROR('Seriously?', 16, 1); -- no rows to import
  END;

  -- optional: test to see if it helps or hurts
  -- ALTER TABLE #TempImport
  --   ADD CONSTRAINT [PK_#TempImport]
  --   PRIMARY KEY CLUSTERED (PKField ASC)
  --   WITH FILLFACTOR = 100;


  -- optional: remove any records that should not be synced
  DELETE tmp
  FROM   #TempImport tmp
  INNER JOIN [SchemaName].[TableName] tab
          ON tab.IDField = tmp.IDField
          ... -- more fields if PK or alternate key is composite
  WHERE  tmp.ModifiedDate < tab.ModifiedDate;

  BEGIN TRAN;

  UPDATE tab
  SET    tab.Field1 = tmp.Field1,
         tab.Field2 = tmp.Field2,
         ...
  OUTPUT INSERTED.IDField
  INTO   @UpdatedIDs ([IDField]) -- capture IDs that are updated
  FROM   [SchemaName].[TableName] tab
  INNER JOIN #TempImport tmp
          ON tmp.IDField = tab.IDField
          ... -- more fields if PK or alternate key is composite

  IF (@@ROWCOUNT < @ImportRows) -- if all rows were updates then skip, else insert remaining
  BEGIN
    -- get rid of rows that were updates, leaving only the ones to insert
    DELETE tmp
    FROM   #TempImport tmp
    INNER JOIN @UpdatedIDs del
            ON del.[IDField] = tmp.[IDField];

    -- OR, rather than the DELETE, maybe add a column to #TempImport for:
    -- [IsUpdate] BIT NOT NULL DEFAULT (0)
    -- Then UPDATE #TempImport SET [IsUpdate] = 1 JOIN @UpdatedIDs ON [IDField]
    -- Then, in below INSERT, add:  WHERE [IsUpdate] = 0

    INSERT INTO [SchemaName].[TableName]
      (Field1, Field2, ...)
      SELECT tmp.Field1, tmp.Field2, ...
      FROM   #TempImport tmp
  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK;
  END;

  -- THROW; -- if using SQL 2012 or newer, use this and remove the following 3 lines
  DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
  RETURN;
END CATCH;

Użyłem tego modelu kilka razy w przypadku importów / ETL, które albo mają znacznie ponad 1000 wierszy, a może 500 w partii z całego zestawu 20 tys. - ponad milion wierszy. Jednak nie przetestowałem różnicy wydajności między USUŃ zaktualizowanych wierszy z tabeli tymczasowej a jedynie aktualizacją pola [IsUpdate].

Proszę zwrócić uwagę na decyzję o użyciu XML zamiast TVP ze względu na możliwość zaimportowania maksymalnie 1000 wierszy jednocześnie (wspomniane w pytaniu):

Jeśli jest to wywoływane kilka razy tu i tam, to całkiem możliwe, że niewielki wzrost wydajności w TVP może nie być wart dodatkowych kosztów utrzymania (trzeba zrezygnować z proc przed zmianą typu tabeli zdefiniowanej przez użytkownika, zmian kodu aplikacji itp.) . Ale jeśli importujesz 4 miliony wierszy, wysyłając 1000 na raz, to jest 4000 wykonań (i 4 miliony wierszy XML do parsowania bez względu na to, jak to jest podzielone), a nawet niewielka różnica w wydajności, jeśli zostanie wykonana tylko kilka razy, sumują się do zauważalnej różnicy.

Biorąc to pod uwagę, metoda, którą opisałem, nie zmienia się poza zastąpieniem SELECT FROM @ XmlInputParam na SELECT FROM @ TVP. Ponieważ TVP są tylko do odczytu, nie można ich usunąć. Myślę, że możesz po prostu dodać WHERE NOT EXISTS(SELECT * FROM @UpdateIDs ids WHERE ids.IDField = tmp.IDField)do tego ostatecznego SELECT (powiązanego z INSERT) zamiast prostego WHERE IsUpdate = 0. Jeśli użyjesz @UpdateIDszmiennej tabeli w ten sposób, możesz nawet uciec bez zrzucania przychodzących wierszy do tabeli tymczasowej.