Najlepszy sposób na rozdrabnianie danych XML do kolumn bazy danych SQL Server

W moim przypadku używam SQL 2005 SP2 (9.0).

Pomogło tylko dodanie opcji (OPTIMIZE FOR (@your_xml_var = NULL)). Wyjaśnienie znajduje się pod linkiem poniżej.

Przykład:

INSERT INTO @tbl (Tbl_ID, Name, Value, ParamData)
SELECT     1,
    tbl.cols.value('name[1]', 'nvarchar(255)'),
    tbl.cols.value('value[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]')
FROM @xml.nodes('//root') as tbl(cols) OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

Https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

Nie wiem, jaka jest najlepsza metoda. Użyłem konstrukcji OPENXML:

INSERT INTO Test
SELECT Id, Data 
FROM OPENXML (@XmlDocument, '/Root/blah',2)
WITH (Id   int         '@ID',
      Data varchar(10) '@DATA')

Aby go przyspieszyć, możesz tworzyć indeksy XML. Możesz ustawić indeks specjalnie dla wartość optymalizacja wydajności funkcji. Możesz również użyć wpisanych kolumn xml, które działają lepiej.

Mieliśmy podobny problem tutaj. Nasz DBA (SP, you the man) przyjrzał się mojemu kodowi, trochę poprawił składnię i uzyskaliśmy prędkość, której oczekiwaliśmy. To było niezwykłe, ponieważ mój select z XML był dużo szybki, ale insert był bardzo powolny. Więc spróbuj zamiast tego tej składni:

INSERT INTO some_table (column1, column2, column3)
    SELECT 
        Rows.n.value(N'(@column1/text())[1]', 'varchar(20)'), 
        Rows.n.value(N'(@column2/text())[1]', 'nvarchar(100)'), 
        Rows.n.value(N'(@column3/text())[1]', 'int')
    FROM @xml.nodes('//Rows') Rows(n) 

Więc podanie parametru text() naprawdę wydaje się mieć wpływ na wydajność. Wzięliśmy naszą wstawkę z 2K wierszy z "musiałem napisać, że źle-pozwól mi to zatrzymać" do około 3 sekund. Które był 2x szybszy niż surowe instrukcje insert, które były uruchamiane przez połączenie.

Nie twierdziłbym, że jest to "najlepsze" rozwiązanie, ale napisałem ogólną procedurę SQL CLR w tym celu-pobiera ona "tabelaryczną" strukturę Xml (taką jak ta zwracana przez XML RAW) i wyświetla resultset.

Nie wymaga dostosowania / znajomości struktury" tabeli " w Xml, a okazuje się być niezwykle szybki / wydajny (choć nie był to cel projektowy). Właśnie zniszczyłem zmienną xml 25MB (untyped) w niecałe 20 sekund, zwracając 25000 rzędy dość szerokiego stołu.

Mam nadzieję, że to komuś pomoże: http://architectshack.com/ClrXmlShredder.ashx

To nie jest odpowiedź, bardziej dodatek do tego pytania - właśnie natknąłem się na ten sam problem i mogę podać liczby, o które prosi edg w komentarzu.

Mój test ma xml, w wyniku którego wstawiono 244 rekordy - czyli 244 węzły.

Kod, który przepisuję, trwa średnio 0,4 sekundy.(10 testów uruchomionych, rozłożonych od .56 SEK .do344 sekund) wydajność nie jest głównym powodem przepisywania kodu, ale nowy kod musi działać równie dobrze lub lepiej. To stary kod zapętla węzły xml, wywołując sp, aby wstawić raz na pętlę

Nowy kod jest prawie tylko jednym sp; podaj xml w; zniszcz go.

Testy z włączonym kodem pokazują, że nowy sp trwa średnio 3,7 sekundy - prawie 10 razy wolniej.

Moje zapytanie jest w formularzu zamieszczonym w tym pytaniu;

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

Wydaje się, że plan wykonania pokazuje, że dla każdej kolumny sql server wykonuje oddzielną "funkcję wartości tabeli [XMLReader]" zwracającą wszystkie 244 wiersze, łączenie wszystkich kopii zapasowych za pomocą zagnieżdżonych pętli (Inner Join). Tak więc w moim przypadku, gdzie jestem rozdrabniania z / wstawianie do około 30 kolumn, wydaje się, że dzieje się to osobno 30 razy.

Będę musiał zrzucić ten kod, nie sądzę, że żadna optymalizacja nie przejdzie przez tę metodę, która jest z natury powolna. Zamierzam wypróbować metodę sp_xml_preparedocument/OPENXML i sprawdzić, czy wydajność jest lepsza. Jeśli ktoś natknie się na to pytanie z wyszukiwarki internetowej (tak jak ja) to bardzo zaleca się wykonanie testów wydajności przed użyciem tego typu rozdrabniania w SQL Server

Istnieje XML Bulk load COM object (. Net przykład )

From MSDN :

Możesz wstawić dane XML do SQL Bazy danych serwera za pomocą wstawki oświadczenie i funkcja OPENXML; jednak Narzędzie do masowego ładowania zapewnia lepszą wydajność, gdy potrzeba Wstawienia dużej ilości XML data.

Moim obecnym rozwiązaniem dla dużych zestawów XML (>500 węzłów) jest użycie SQL Bulk Copy (System.Data.SqlClient.SqlBulkCopy) za pomocą zestawu danych załadować XML do pamięci, a następnie przekazać tabelę do SqlBulkCopy (definiowanie schematu XML pomaga).

Oczywiście istnieją pułapki, takie jak niepotrzebne użycie zestawu danych i wczytywanie całego dokumentu do pamięci. Chciałbym pójść dalej w przyszłości i zaimplementować własny IDataReader, aby ominąć metodę DataSet, ale obecnie DataSet jest " dobry wystarczy" do pracy.

Zasadniczo nigdy nie znalazłem rozwiązania mojego pierwotnego pytania dotyczącego powolnej wydajności dla tego typu rozdrabniania XML. Może to być powolne z powodu wpisywanych zapytań xml, które są z natury powolne lub mają coś wspólnego z transakcjami i logiem serwera SQL. Domyślam się, że wpisane funkcje xml nigdy nie były przeznaczone do pracy na nietrywialnych rozmiarach węzłów.

XML Bulk Load: próbowałem tego i tobyło szybko ale miałem problem z uruchomieniem COM dll pod Środowiska 64-bitowe i generalnie staram się unikać bibliotek DLL COM, które nie wydają się już obsługiwane.

Sp_xml_preparedocument / OPENXML: nigdy nie szedłem tą drogą, więc byłbym zainteresowany, aby zobaczyć, jak to działa.