bezpłatny biuletyn SQL Server firmy TechRepublic, dostarczany w każdy wtorek, zawiera praktyczne wskazówki, które pomogą Ci stać się bardziej biegłym w tym potężnym systemie zarządzania relacyjnymi bazami danych. Automatycznie Subskrybuj już dziś!
ogólnie przyjęta mądrość o procedurach składowanych (lub zębach) polega na tym,że ponieważ SQL może je optymalizować i kompilować, działają one szybciej niż równoważne instrukcje SQL wykonane z QueryAnalyzer (lub być może przekazane z jakiejś aplikacji front-end, takiej jak strona internetowa lub VBprogram). Jest to do pewnego stopnia prawdą.
najpierw musisz wiedzieć, co SQL Server robi z nowym sproc. Podczas tworzenia sprawdza składnię. Jeżeli nie znajdzie żadnych błędów, wtedy doda sproc do tabel systemowych: sysobjects, sysdepends oraz syscomments (te ostatnie przechowują treść sproc). Domyślnie nie kompiluje sproc w czasie tworzenia.
po pierwszym uruchomieniu sproc,SQL Server optymalizuje i kompiluje go. Dzieje się tak, gdy SQL Server tworzy queryplan i przechowuje go w pamięci podręcznej procedury. Przy kolejnych wywołaniach SQL najpierw szuka w pamięci podręcznej, znajduje tam sproc i nie kompiluje go. Jeśli sproc nie znajduje się w pamięci podręcznej,SQL Server kompiluje go i umieszcza w pamięci podręcznej.
moje doświadczenie z opcją z REKOMPILACJĄ
jakiś czas temu wspierałem stronę wyszukiwania, która pozwalała użytkownikom wyszukiwać po dowolnej z kilku kolumn. Następnie strona nazywa się sproc, przekazując parametr wskazujący, którą kolumnę wyszukać. Zbadałem parametr za pomocą bloku CASE, a następnie wykonałem jedno z kilku zapytań, w zależności od kolumny do przeszukania.
wiedziałem, że coś jest nie tak, Kiedy zacząłem testować moją rzekomo przechowywaną procedurę. Teoretycznie wydajność każdego wyszukiwania powinna być przynajmniej w przybliżeniu taka sama, ale tak się nie stało. Kiedy wykonywałem wiele wyszukiwań, niezależnie od kolejności, pierwsze byłoby szybkie, a kolejne wyszukiwania były znacznie wolniejsze.
w końcu zdałem sobie sprawę, że przy pierwszym wywołaniu procedury opracowano plan zapytań i zapisano go w pamięci podręcznej. Tak długo, jak szukałem w tej kolumnie, wszystko działało tak, jak oczekiwano. W momencie, gdy zamieniłem kolumny, wydajność spadła. Dlaczego to się stało?
pierwsze wyszukiwanie, które wykonałem, stworzyło Plan zapytań i storedit w pamięci podręcznej. Na przykład, powiedzmy, że szukałem w kolumnie OrderDate. Jeśli przełączyłem wyszukiwanie na kolumnę CompanyName, SQL ślepo użyłby buforowanego queryplan, szukając docelowej nazwy firmy za pomocą OrderDateindex. Nic dziwnego, że wydajność tak dramatycznie spadnie.
poprawka jest dość prosta. Wykonałem sprocsupplying z opcji rekompilacji:
EXEC MySproc_Select '12/31/2004' WITH RECOMPILE
To mówi SQL Server, aby wyrzucić istniejący plan zapytań i zbudować kolejny-ale tylko ten jeden raz.
Możesz również dodać z przekompilować bezpośrednio do procedury storedprocedure tuż przed słowem kluczowym AS. To mówi SQL Server, aby wyrzucił plan query przy każdym wykonaniu sproc.
istnieje również trzecia opcja. Mogłem utworzyć oddzielny sproc dla każdej metody wyszukiwania, a następnie zdecydować, który z nich wykonać w bloku CASE. W ten sposób plan zapytania związany z pod-zębami pozostaje w pamięci podręcznej,gdzie SQL może z nich skorzystać. Ponieważ każda z zębatychwyszukiwała dokładnie jedną kolumnę, nie ma potrzeby rekompilacji.
zdolność SQL Server do optymalizacji i kompilacji procedury storedprocedure jest świetna, ale jeśli nie jesteś ostrożny, może cię ugryźć, gdy przynajmniej go wyczujesz. Teraz, gdy wiesz, jak radzić sobie z tym problemem, być może istnieje kilka sytuacji we własnej bazie danych, do których warto wrócić.