Kursory mogą wyglądać jak skróty dla programisty. Gdy masz złożone zadanie do wykonania i musisz manipulować wierszami w tabeli, najszybszy sposób może wydawać się iteracją wierszy jeden po drugim za pomocą kursora Transact-SQL. W końcu, ponieważ musisz iterację poprzez struktury danych w swoim własnym kodzie po stronie klienta, możesz się skusić, aby zrobić to samo, gdy masz do czynienia z danymi SQL Server., Ale iteracja danych za pomocą kursorów Transact-SQL często nie skaluje się dobrze i mam nadzieję, że przekonam Cię, że nie jest to również dobra praktyka projektowa lub architektoniczna.
a cursor Experience
przywołuję to, ponieważ kilka miesięcy temu miałem do czynienia ze skryptem Transact-SQL dostawcy, który zaktualizował komponent bazy danych do nowej wersji aplikacji dostawcy. Skrypt został zaprojektowany tak, aby obracać bardzo dużą tabelę i przechowywać odpowiednie dane w nowej tabeli poziomo, jako połączone ciągi znaków., Producent chciał poprawić wydajność poprzez zmniejszenie tabeli, więc zdecydował się przechowywać dane szczegółowe w poziomie, jako rozdzielane przecinkami ciągi znaków dla każdego identyfikatora nadrzędnego. Aplikacja kliencka mogła odpytywać wynikowe ciągi rozdzielane przecinkami szybciej niż uzyskiwanie każdego z nich jako pojedynczych wierszy, a w kontekście zmiana miała sens i poprawiła wydajność aplikacji.
jednak skrypt Transact-SQL sprzedawcy do zmiany danych podczas aktualizacji trwał 16 godzin, a Klient nie mógł sobie pozwolić na więcej niż kilka godzin przestoju na aktualizację., Kiedy zbadaliśmy skrypt dostawcy, zauważyliśmy, że programista zakodował proces obracania w dwóch krokach: kursor do iteracji wszystkich identyfikatorów tabeli nadrzędnej w celu zbudowania pustej, wstępnie sformatowanej tabeli, a następnie inny skrypt do łączenia łańcuchów, ponownie za pomocą kursora.
dzięki zastosowaniu podejścia set-based udało nam się skrócić czas przetwarzania z ponad 16 godzin do mniej niż pięciu minut. Podążaliśmy za oryginalną strategią dewelopera, budując pustą tabelę za pomocą instrukcji SELECT i skróciliśmy czas na ten krok do mniej niż dwóch minut., Następnie połączyliśmy łańcuchy za pomocą instrukcji UPDATE, wykonanej dla identyfikatora nadrzędnego. Nasza iteracja poprzez identyfikatory rodzica używała pętli WHILE I zakończyła się w mniej niż trzy minuty.
nieuchronność iteracji
wiele dostępów do danych bazy danych musi być w pewien sposób iteracyjny, aby przygotować dane do dalszej manipulacji. Nawet silnik SQL Server przetwarza dane podczas skanowania lub łączenia danych przy użyciu różnych dostępnych dla niego typów połączeń. Można to zobaczyć podczas sprawdzania planu zapytań SQL Server pod kątem zapytania, które zwraca wiele wierszy z dużego zestawu danych., W przypadku połączenia najczęściej zobaczysz zagnieżdżoną pętlę, ale czasami także połączenie scalające lub hashowe. W przypadku prostszych zapytań możesz zobaczyć skan indeksu klastrowego lub nieklastrowego. Tylko w przypadkach, gdy SQL Server może zwrócić pojedynczy wiersz lub mały zestaw wierszy, a tabela ma odpowiedni indeks, zobaczysz wyszukiwanie za pomocą indeksu.
pomyśl o tym: Microsoft od lat optymalizuje i dostraja silnik SQL Server, aby jak najefektywniej przeglądać dostępne dane., Wyobraź sobie, że gdybyś miał czas i był gotów poświęcić energię, prawdopodobnie mógłbyś napisać dostęp niskiego poziomu do plików danych bazy danych, który byłby całkiem wydajny. Byłoby to jednak skuteczne tylko dla poszczególnych zadań przed tobą i musiałbyś je debugować i być może musiał całkowicie przepisać je, gdyby zakres dostępu do danych miał się zmienić. Prawdopodobnie zajmie ci lata, aby naprawdę uzyskać kod w pełni zoptymalizowany i uogólniony, a nawet wtedy nie byłoby blisko wydajności kodu wewnątrz silnika pamięci masowej SQL Server.,
więc gdzie zysk w ponownym wynalezieniu koła? Tylko dlatego, że silnik SQL Server jest tak dobrze zoptymalizowany i debugowany, lepiej pozwolić mu wykonać iterację za Ciebie i skorzystać z rozległego rozwoju i testów, które są już osadzone w bazie danych.
jeśli przyjrzysz się dokładniej zadaniom przetwarzania danych, myślę, że przekonasz się, że naprawdę niewiele jest sytuacji, w których wymagane są Kursory. Po pierwsze, często można osiągnąć swój cel polegając na poleceniach SQL opartych na zestawach w Transact-SQL i ignorując kolejność wierszy tabeli., Po drugie, Kursory Transact-SQL są tylko jednym ze sposobów iteracji tabeli wiersz po wierszu. Jeśli możesz jednoznacznie zidentyfikować każdy wiersz tabeli, który musisz iterować, możesz użyć pętli WHILE zamiast kursora i potencjalnie uzyskać lepszą wydajność. Pozwól, że przedstawię ci przykład, aby pokazać, dlaczego.
porównywanie strategii iteracji
Załóżmy, że możesz jednoznacznie zidentyfikować każdy wiersz tabeli, ponieważ tabela ma unikalny klucz lub unikalną grupę kolumn., W pętli WHILE, wszystko, co musisz zrobić, to znaleźć najniższą wartość unikalnego warunku, a następnie znaleźć następną najwyższą wartość za każdym razem, gdy iteracja. Oto przykład z przykładowej produkcji baz danych SQL Server 2005 AdventureWorks.Tabela transakcji. Posiada klastrowy indeks na klawiszu głównym, a pętla WHILE może za każdym razem szukać w wierszu.,
USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)ENDOto ta sama pętla za pomocą szybkiego kursora do przodu, który jest najbardziej efektywnym typem kursora Transact-SQL do odczytu danych:
DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursorna moim laptopie, po uruchomieniu go kilka razy, aby upewnić się, że dane są w pamięci podręcznej, pętla WHILE trwa dziewięć sekund, a kursor trwa 17 sekund. Twój własny czas trwania może się różnić. Zauważ, że chociaż przykład naprawdę nic nie robi z danymi, pętla WHILE jest szybsza. Kursor widocznie dodaje więcej kosztów.,
kursor wymaga również dodatkowych poleceń, które sprawiają, że kod wygląda na zaśmiecony. Nie wchodząc w szczegóły działania kursorów, co Microsoft wyjaśnia w pełni w Microsoft SQL Server 2005 Books Online, zauważ, że gdy używasz pętli WHILE, nie ma wymogu deklarowania, otwierania, zamykania i dealokacji czegokolwiek. Logika jest prostsza, a po drodze można nawet dowolnie aktualizować wiersze. Aby zaktualizować wiersze za pomocą kursora, musisz zmienić typ kursora.
nawet pętla WHILE dodaje narzut iteracji., Możesz zastąpić go poleceniem SELECT opartym na zestawie lub zastąpić wszelkie aktualizacje, które chcesz wykonać w pętli poleceniem UPDATE opartym na zestawie, i pozostawić iterację silnikowi SQL Server. Prosta instrukcja SELECT, aby uzyskać te same dane, co nasz kursor i pętla WHILE, zajmuje mniej niż 3 sekundy i zwraca wiersze do klienta, co jest więcej pracy niż dwie poprzednie pętle.
SELECT *FROM Production.TransactionHistoryto SELECT polega na serwerze SQL do iteracji danych i jest zdecydowanie najszybszą z trzech metod dostępu do danych, na które patrzyliśmy.,
od worków do zestawów
czasami Kursory mogą wydawać się konieczne. Gdy po prostu musisz iterację danych bazy danych, wiersz po wierszu, w ich fizycznej kolejności, czasami tylko kursor będzie działać. Najczęściej dzieje się tak, gdy masz zduplikowane wiersze i nie ma możliwości jednoznacznej identyfikacji danego wiersza w tabeli. Tabele te są workami, a nie zestawami danych, ponieważ „worek” nie eliminuje zduplikowanych wartości, tak jak zestaw.
takie worki danych zwykle występują, gdy importujesz dane z zewnętrznego źródła i nie możesz całkowicie zaufać danym., Na przykład, jeśli nasza tabela historii transakcji AdventureWorks nie miała grupy kolumn, które można nazwać unikalnymi i / lub miały zduplikowane wiersze, możesz pomyśleć, że musisz użyć kursora.
jednak zawsze możesz zamienić worek wierszy w znormalizowaną tabelę. Nawet jeśli masz zduplikowane wiersze w tabeli lub nie masz zestawu kolumn, na których możesz polegać, aby uzyskać unikalność, możesz dodać kolumnę tożsamości do tabeli i zalążek tożsamości, aby rozpocząć numerowanie od 1. Dodaje to unikalny klawisz do tabeli, umożliwiając użycie pętli WHILE zamiast kursora., Gdy masz unikalny klucz, możesz usunąć duplikaty za pomocą polecenia aktualizacji opartego na zestawach Transact-SQL.
logiczne API do danych bazy danych
za pomocą operacji set-base jest lepsze niż iteracja danych na co najmniej dwa sposoby.
Po pierwsze, polecenia SQL oparte na zestawach są bardziej wydajne, ponieważ używasz wysoce zoptymalizowanego silnika SQL Server do wykonywania iteracji. Jeśli samodzielnie iterujesz dane, nie używasz silnika pamięci masowej SQL Server optymalnie. Zamiast tego przesyłasz go poleceniami, aby pobrać tylko jeden wiersz na raz., Za każdym razem, gdy żądasz pojedynczego wiersza, Twoje polecenie musi przejść przez optymalizator SQL Server, zanim dotrze do silnika pamięci masowej, a w końcu nie używasz zoptymalizowanego kodu silnika pamięci masowej SQL Server. Jeśli iterujesz samodzielnie, podczas przetwarzania danych polegasz również na obcych fizycznych informacjach o tabeli, a mianowicie na kolejności wierszy. Polecenia set-base Transact – SQL SELECT, UPDATE I DELETE pozwalają ignorować kolejność wierszy i wpływać na nie w oparciu o charakterystykę danych-i są szybsze.,
Po Drugie, polecenia oparte na zestawach są bardziej logiczne, ponieważ myślenie o danych w zestawach abstrahuje od zbędnych szczegółów, które są bardziej związane z tym, jak dane są faktycznie uporządkowane. W rzeczywistości polecenia oparte na zestawach, takie jak SELECT, UPDATE I DELETE, stosowane bezpośrednio do tabel, a nie w pętli kursora lub WHILE, zbliżają cię logicznie do danych, właśnie dlatego, że możesz ignorować kolejność danych.,
oto inny sposób myślenia o tym drugim punkcie-tak jak procedury składowane są najbardziej naturalnym API dla aplikacji do programowania interfejsu SQL Server, tak polecenia SQL oparte na zestawach są odpowiednim API do dostępu do danych relacyjnych. Procedury przechowywane oddzielają aplikację od wewnętrznych baz danych i są bardziej wydajne niż zapytania ad hoc. Podobnie polecenia SQL set-base wewnątrz Transact-SQL zapewniają logiczny interfejs do danych relacyjnych i są bardziej wydajne, ponieważ polegasz na silniku pamięci masowej SQL Server do iteracji danych.,
najważniejsze nie jest to, że iteracja danych jest zła. Właściwie, często jest to nieuniknione. Chodzi raczej o to, aby silnik pamięci masowej zrobił to za Ciebie i zamiast tego polegał na logicznym interfejsie poleceń Transact-SQL opartych na zestawach. Myślę, że znajdziesz kilka sytuacji, w których musisz użyć kursora Transact-SQL.