Temat: Szybkość procedury vs. adhoc query

mam pytanie do Przemka wzięte z innego wątku
w związku z tą wypowiedzią:

Przemysław R.:
co do szybkości wykonywania poszczególnych operacji w porównaniu z procedurami mógł bym polemizować

to ja poproszę o jakiś przykład, w którym procedura szybciej się wykona niż zapytanie dynamiczne sp_executesql.

Ja twierdzę, że od czasów SQL Servera 2005 włącznie (czyli przeszło 5 lat) nie ma znaczenia, czy zapytanie jest skompilowane w procedurze, czy normalnie jej query plan siedzi w cache'u (sys.syscacheobjects) - w obu przypadkach wykona się tak samo. No i aby obalić moje twierdzenie proszę o kontrprzykład możliwy do odtworzenia na powiedzmy AdventureWorks2008LT.

Możesz Przemek odpowiedzieć zamieszczając dowód na swoim blogu.

EDIT: dodałem link do oryginalnego wątkumaciek kański edytował(a) ten post dnia 19.07.10 o godzinie 15:50

Temat: Szybkość procedury vs. adhoc query

maciek kański:
mam pytanie do Przemka wzięte z innego wątku
w związku z tą wypowiedzią:

Przemysław R.:
co do szybkości wykonywania poszczególnych operacji w porównaniu z procedurami mógł bym polemizować

to ja poproszę o jakiś przykład, w którym procedura szybciej się wykona niż zapytanie dynamiczne sp_executesql.

Ja twierdzę, że od czasów SQL Servera 2005 włącznie (czyli przeszło 5 lat) nie ma znaczenia, czy zapytanie jest skompilowane w procedurze, czy normalnie jej query plan siedzi w cache'u (sys.syscacheobjects) - w obu przypadkach wykona się tak samo. No i aby obalić moje twierdzenie proszę o kontrprzykład możliwy do odtworzenia na powiedzmy AdventureWorks2008LT.

Możesz Przemek odpowiedzieć zamieszczając dowód na swoim blogu.

cóż czasem trzeba się się poświęcić dla dobra nauki :)
nie dziś nie teraz, ale będzie

oczywiście mówimy tu o adhoc generowanym z z poziomu ORM-a vs. dowolny SQL realizujący tą samą funkcję zamknięty w spPrzemysław R. edytował(a) ten post dnia 19.07.10 o godzinie 16:07

Temat: Szybkość procedury vs. adhoc query

maciek kański:
Ja twierdzę, że od czasów SQL Servera 2005 włącznie (czyli przeszło 5 lat) nie ma znaczenia, czy zapytanie jest skompilowane w procedurze, czy normalnie jej query plan siedzi w cache'u (sys.syscacheobjects) - w obu przypadkach wykona się tak samo.

Wszyscy guru od SQL servera twierdzą, że dynamiczne query jest zawsze wolnijeszy a wyjątki (execution plan do identycznego zapytania siedzący w cache) potwierdzają regułę :)

Temat: Szybkość procedury vs. adhoc query

Bartosz Ślepowronski:

maciek kański:
Ja twierdzę, że od czasów SQL Servera 2005 włącznie (czyli przeszło 5 lat) nie ma znaczenia, czy zapytanie jest skompilowane w procedurze, czy normalnie jej query plan siedzi w cache'u (sys.syscacheobjects) - w obu przypadkach wykona się tak samo.

Wszyscy guru od SQL servera twierdzą, że dynamiczne query jest zawsze wolnijeszy a wyjątki (execution plan do identycznego zapytania siedzący w cache) potwierdzają regułę :)

clu problemu to nie konstrukcja SQL-a, lecz brak pewnych elementów które można wykorzystać z poziomu procedury - tam piszesz wszystko z palca i nie masz ograniczeń

np. tak myślałem i się zastawaniem czy się da w ORM-e dowolnym stworzyć tabelę tymczasową z wyniku jakiegoś Select-a - pewnie da, dodać indeksy i taki tymczasowy obiekt wykorzystywać do dalszej pracy

znając życie jak człowiek poszuka to znajdzie wiele takich elementów które standardowo nie są osiągalne z poziomu ORM-a lub wymagają uruchomienia SQL-a, a chyba nie o to chodzi

Temat: Szybkość procedury vs. adhoc query

Przemysław R.:
znając życie jak człowiek poszuka to znajdzie wiele takich elementów które standardowo nie są osiągalne z poziomu ORM-a lub wymagają uruchomienia SQL-a, a chyba nie o to chodzi

Tak tak, nie o to chodzi ale o to co napisałeś czyli szybkości wykonywania poszczególnych operacji w porównaniu z procedurami
Ja takiej różnicy nie potrafię zauważyć - czy statement adhoc, czy stored procedure, w obu przypadkach mam ten sam query plan/execution plan wykonujący się bez [b]statystycznie[b] istotnej różnicy. Dlatego skoro twierdzisz, że jest różnica to chciałem poznać twoje przesłanki, które Cię do tego wniosku doprowadziły - zważywszy twoje doświadczenie związane z MS SQLem ufam, że na opinii guru nie poprzestałeś (bo opinie są sprzeczne).

Nie poganiam z odpowiedzią, ale chętnie bym ten temat raz na zawsze rozwiązał, chyba nie ja jeden - użytkownicy ORMów to dość liczna grupa developerów:)

Temat: Szybkość procedury vs. adhoc query

maciek kański:

Przemysław R.:
znając życie jak człowiek poszuka to znajdzie wiele takich elementów które standardowo nie są osiągalne z poziomu ORM-a lub wymagają uruchomienia SQL-a, a chyba nie o to chodzi

Tak tak, nie o to chodzi ale o to co napisałeś czyli szybkości wykonywania poszczególnych operacji w porównaniu z procedurami
Ja takiej różnicy nie potrafię zauważyć - czy statement adhoc, czy stored procedure, w obu przypadkach mam ten sam query plan/execution plan wykonujący się bez statystycznie istotnej różnicy. Dlatego skoro twierdzisz, że jest różnica to chciałem poznać twoje przesłanki, które Cię do tego wniosku doprowadziły - zważywszy twoje doświadczenie związane z MS SQLem ufam, że na opinii guru nie poprzestałeś (bo opinie są sprzeczne).

tu nie chodziło o zapytania adhocowe tylko o to co generuje ORM vs to co można zrobić w procedurze

to że może być to wykonane w sposób jaki napisałeś to jedno, zupełnie czym innym jest fakt że pewne ograniczenia narzucane przez ORM-y nie pozwalają wykorzystać w pełni mechanizmów danego silnika bazodanowego

idąc dalej tym tropem wydajność SP wykorzystującej jakiś tam mechanizm, którego nie da się zrobić w czystej postaci w ORM-e jest wydajniejsza niż procedura napisana w jakimś języku

czy to wytłumaczenie kontekstu jest jasne

Nie poganiam z odpowiedzią, ale chętnie bym ten temat raz na zawsze rozwiązał, chyba nie ja jeden - użytkownicy ORMów to dość liczna grupa developerów:)

spoko, nie mam nic przeciwko

a teraz mam pytanie:

jak w ORM-e wydajnie wykonać następującą konstrukcję:
http://www.4guysfromrolla.com/webtech/071906-1.shtml

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS

(

   -- Base case

   SELECT

      EmployeeID,

      LastName,

      FirstName,

      ReportsTo,

      1 as HierarchyLevel

   FROM Employees

   WHERE ReportsTo IS NULL



   UNION ALL



   -- Recursive step

   SELECT

      e.EmployeeID,

      e.LastName,

      e.FirstName,

      e.ReportsTo,

      eh.HierarchyLevel + 1 AS HierarchyLevel

   FROM Employees e

      INNER JOIN EmployeeHierarchy eh ON

         e.ReportsTo = eh.EmployeeID

)



SELECT *

FROM EmployeeHierarchy

ORDER BY HierarchyLevel, LastName, FirstName

załóżmy że tabela ma wiele rekordówPrzemysław R. edytował(a) ten post dnia 19.07.10 o godzinie 19:45

Temat: Szybkość procedury vs. adhoc query

Przemysław R.:
tu nie chodziło o zapytania adhocowe tylko o to co generuje ORM vs to co można zrobić w procedurze

no a mi chodziło o e/w wyższość (czytaj: lepszą wydajność) SP nad takim samym zapytaniem adhoc - vide temat wątku:) Czy aby uniknąć nieporozumień możesz potwierdzić, że Ty również takiej różnicy nie widzisz, jeżeli zapytania są takie same, tylko mają różne źródła (ORM, adhoc, SP)?

jak w ORM-e wydajnie wykonać następującą konstrukcję:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS

(

rekursywne CTE, ciach....

)

SELECT *

FROM EmployeeHierarchy

ORDER BY HierarchyLevel, LastName, FirstName

Łączysz zalety elastyczności jaką Ci daje ORM, funkcjonalności jaką oferuje rekursja w SQL2005 ale bynajmniej nie pakujesz się w SP: całe CTE wrzucasz w widok lub table-valued-function i taką funkcję odpytujesz z ORMa, gdzie ciągle masz możliwość filtrować, page'ować oraz sortować po dowolnej kolumnie, w/g życzenia usera.
Jeżeli się ze mną nie zgadzasz to odpowiedz, w jaki sposób obsłużyłbyś w SP:
- filtrowanie po każdej z wielu kolumn
- sortowanie po każdej z wielu kolumn
- paging (to akurat najprostsze)

Wejdziesz w SP, stracisz możliwość kształtowania rezultatu z zapytania - procedura jest sztywna.

P.S. Uprzedzając e/w szczegóły nie wpływające na meritum, w twoim przykładzie filtrowanie po HierarchyLevel zapewne wymaga 'wejścia' z parametrem do środka CTE, ale TVF parametry obsługują tak samo jak SP; ta jedna kolumna wymaga więc specjalnego potraktowania, ale z przykładu nie wynika, aby autor martwił się o głębokość rekursji bo polega na defaultach.

Temat: Szybkość procedury vs. adhoc query

maciek kański:

Przemysław R.:
tu nie chodziło o zapytania adhocowe tylko o to co generuje ORM vs to co można zrobić w procedurze

no a mi chodziło o e/w wyższość (czytaj: lepszą wydajność) SP nad takim samym zapytaniem adhoc - vide temat wątku:) Czy aby uniknąć nieporozumień możesz potwierdzić, że Ty również takiej różnicy nie widzisz, jeżeli zapytania są takie same, tylko mają różne źródła (ORM, adhoc, SP)?

jest dokładnie tak jak piszesz

jak w ORM-e wydajnie wykonać następującą konstrukcję:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS

(

rekursywne CTE, ciach....

)

SELECT *

FROM EmployeeHierarchy

ORDER BY HierarchyLevel, LastName, FirstName

Łączysz zalety elastyczności jaką Ci daje ORM, funkcjonalności jaką oferuje rekursja w SQL2005 ale bynajmniej nie pakujesz się w SP: całe CTE wrzucasz w widok lub table-valued-function i taką funkcję odpytujesz z ORMa, gdzie ciągle masz możliwość filtrować, page'ować oraz sortować po dowolnej kolumnie, w/g życzenia usera.
Jeżeli się ze mną nie zgadzasz to odpowiedz, w jaki sposób obsłużyłbyś w SP:
- filtrowanie po każdej z wielu kolumn
- sortowanie po każdej z wielu kolumn
- paging (to akurat najprostsze)

to jest jakieś rozwiązanie
nagina to trochę zasady przenośności kodu aplikacji, ale czasem warto :) zresztą nie wolno być fanatykiem, każde rozwiązanie jeżeli jest skuteczne jest dobre

Wejdziesz w SP, stracisz możliwość kształtowania rezultatu z zapytania - procedura jest sztywna.

dynamiczny SQL na końcu można dowolnie modyfikować, a wynik zależy tylko od tego jak sobie skleimy zapytanie. W niektórych przypadkach to dosyć użyteczna funkcjonalność, a metod żeby to osiągnąć jest kilka

Temat: Szybkość procedury vs. adhoc query

czy przypadkiem jeśli z ORMa podstawiasz parametry to nie generujesz nowych planów ? wręcz jestem przekonany, że tak dlatego, że plan jest case sensitive
jeśli masz SP to masz plan reuse.

więc coś takiego

select * from mojatab where col1=1
i
select * from mojatab where col1 = 1

to są 2 plany wykonania analogicznie
select * from mojatab where col1 = 3
to już 3 plan

a:

create mojaproc @par int
as
select * from mojatab where col1 = @par

daje nam 1 plan ?
Jaki z tego wniosek ? pewnie ten plan musi być stworzony więc jest to bardziej kosztowne odwołując się do sp_executesql oczywiście on daje większe szanse na plan reuse niż samo exec

Temat: Szybkość procedury vs. adhoc query

Krzysztof Stachyra:

Jest dokładnie tak jak piszesz Krzysztof, do każdego zapytania wykonywanego przez execute SQL server utworzy nowy plan. Sprawdziłem to na swojej bazie produkcyjnej, więc dokładnego kodu nie wkleję, ale łatwo to sprawdzić przy użyciu DMV przykład przeczesując plany wykonania:

SELECT query_plan, text FROM sys.dm_exec_cached_plans

INNER JOIN sys.dm_exec_query_stats ON sys.dm_exec_query_stats.plan_handle=sys.dm_exec_cached_plans.plan_handle

CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_cached_plans.plan_handle)

CROSS APPLY sys.dm_exec_sql_text (sql_handle)

WHERE text like 'poczatek waszego kodu %'

Odpalenie testowej procedury spowodowalo utworzenie tylko jednego planu, niezaleznie od uzytego parametru. Procedura maksymalnie prosta:

CREATE procedure Test_sp @parametr nvarchar(1)

as

(select z joinami where kolumna = @parametr)

Potem spróbowałem dynamicznego SQL:

DECLARE @query nvarchar(500)

DECLARE  @parametr nvarchar(1)

SET @parametr = ..

SET @query = 'select z joinami where kolumna = ' + @parametr 

EXECUTE(@query)

Odpalenie dynamicznego SQL IDENTYCZNEGO z tekstem SQL procedury powoduje zapisanie nowego planu. Każdy nowy parametr powoduje wyliczenie i zapisanie nowego planu.

Wniosek - SP są szybsze o wyliczenie planu od ORM odpalacych DSQL:) Z ciekawych rzeczy, dla dynamicznego SQL na 10 prób w 9 wyliczony został taki sam plan jak dla procedury a w jednym przypadku (zwracana mała liczba rekordów) inny. Różnic w czasie wykonania nie zaobserwowałem, ale pewnie jakieś by były dla bardziej skomplikowanych zapytań.

Jedyny przypadek dla którego plan utworzony przez procedurę był faktycznie używane przez zapytanie z dynamicznego SQL to taki w którym procedura nie miała żadnych parametrów, tylko czysty select. Odpalenie DSQL z tym samym selectem używało planu stworzonego dla SP.

Jak przyjdę do domu to mogę spreparować konkretny przykład na AdventureWorks.Bartosz Ślepowronski edytował(a) ten post dnia 21.07.10 o godzinie 00:13

Temat: Szybkość procedury vs. adhoc query

otóż Bartku wniosek jeszcze jest z tego jeden niestety generowanie nowych planów niesie za sobą konsekwencje pazerności na pamięć, zwróć uwagę na ilość pamięci zjadaną przez każdy plan (zpaytania z cross apply do DMV).
W testach dla 10 zapytań nie robi to różnicy ale w produkcji wszystko co zjada ProcCache zmniejsza ilość danych, które mogą być zbuforowane co za tym idzie co jest ważniejsze ?
Ilość planów w pamięci czy duża ilość danych, z których korzystają zapytania moim zdaniem to drugie, oczywiście nie ma możliwości również określenia maksymalnej ilości pamięci pod ProcCache.
Co więcej SQL Server daje nam zabawki pozwalające to niwelowac w jakiś sposób po pierwsze forced parametrization oraz w 2008 Adhoc queries workload optimization czy jak to sie tam zwało.

Oczywiście generowanie nowego planu niesie za sobą konsekwencje obciążenia CPU co przy ogromnej liczbie zapytań z ORMa na sekundę powoduje kosmiczne ilości rekompilacji planów a to z kolei przy przekroczeniu limitu powoduje wypróżnianie buforów w celu zwolnienia miejsca pod nowe generowane plany - ogólnie rzecz ujmując błędne koło :)

Wniosek
Korzystajmy z procedur składowanych ;)

Temat: Szybkość procedury vs. adhoc query

Po przeczytaniu waszych opinii myślę że:

ORMy dają dużą elastyczność i obiektywność.
SP jak wykazaliście lepszą wydajność techniczną, większą kontrolę nad bezpieczeństwem, możliwość wykorzystania specyficznych dla konkretnego silnika mechanizmów i zaszycia logiki biznesowej po stronie bazy danych.

Największy zarzut dla sp to fakt, że są kolejną warstwą konieczną do stworzenia i utrzymania. Czasem warto z niej korzystać, czasem nie.

Jeśli zatem tworzymy aplikację, która wykona 2 operacje odczytu i zapisu dziennie, warto do niej wykorzystac ORMa. Jeśli tworzymy coś odpytującego bazę co sekundę z innym parametrem, lepiej skorzystac z procedury.
Jeśli nie wiemy jak będzie, obstawiamy i strzelamy ;).

Temat: Szybkość procedury vs. adhoc query

Są tasaki i są noże. Tasakiem nie kroimy chleba, nożem do chleba nie ćwiartujemy mięsa, a zatem - jak mówisz :) Chociaż z tym "strzelamy" nie do końca bym się zgodził, bo jednak jakieś szacunki tego co nas w projekcie czeka zwykle się ma...

Temat: Szybkość procedury vs. adhoc query

Nie wiem czy moje wnioski mają sens dla sp_executesql, sprawdzałem dla execute. Wydawało mi się, że komenda nie ma wpływu na użycie planów wykonania, wychodzi na to, że ma. ORMy korzystają raczej z sp_executesql, prawda?

Polecam też poczytać TO na msdn.

Jak słusznie zauważyli Adrian i Krzysztof, należy używać tego czego używanie w danej sytuacji ma sens, czasem bedzie to DSQL z ORM a czasem SP.Bartosz Ślepowronski edytował(a) ten post dnia 20.07.10 o godzinie 18:41

Temat: Szybkość procedury vs. adhoc query

Bartosz Ślepowronski:
Nie wiem czy moje wnioski mają sens dla sp_executesql, sprawdzałem dla execute.

W porę się uratowałeś:) Wystarczy odpalić profilera i zobaczyć co produkuje Linq2SQL - produkuje on sparametryzowane zapytania, czyli odpowiednik sp_executesql.

Natomiast moja bardzo wielka wina, że w tytule użyłem mylnie słowa adhoc, bo adhoc oznacza kompletnie statyczne zapytanie.
Ale uczciwe porównanie procedur (sparametryzowanych) dotyczy zapytań (sparametyzowanych), zapytanie adhoc można porównywać do procedury bez parametrów.

Każdy, kto się obawia ilości wygenerowanych planów niech sobie sprawdzi odpowiednie liczniki w SQL Serwerze - wpierw niech sprawdzi, a potem się martwi, nie na odwrót:)

Obiecuję, że w wolnej chwili zamieszczę prosty do samodzielnego odtworzenia dowód - na bazie AdventureWorks2008LT - że zapytania wygenerowane przez mapper Linq2SQL (innego nie znam) używają tego samego planu i tym samym w niczym nie są szybsze od SP.

Jest jeszcze to co Krzysiek wspomniał optimize for ad hoc workloads ale ja nie miałem potrzeby tego używać więc się nie wypowiadam.

Temat: Szybkość procedury vs. adhoc query

maciek kański:

Bartosz Ślepowronski:
Nie wiem czy moje wnioski mają sens dla sp_executesql, sprawdzałem dla execute.

W porę się uratowałeś:)

Połowę swojej wiedzy o SQL zdobyłem sprawdzając w dokumentacji czy odpowiedzi których udzieliłem na forum mają sens. Może powinien to sprawdzać przed wysłaniem posta ;)

Mam jeszcze dwie uwagi do tematu
1) Z MSDN, o sp_executesql "While the goal is for SQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL Server sometimes does not detect this in complex SQL statements."
Czyli im bardziej skomplikowane zapytanie wysyłane z ORMa tym większa szansa że zostanie stworzony nowy plan, bo serwer nie znajdzie pasującego. SP ma plan przypisany na stałe (o ile nie wystąpią warunki wymuszające jego rekompilację)do objectid, więc serwer nie traci czasu na szukanie. Moja uwaga - to że SP zawsze używa tego samego planu też nie zawsze jest zaletą.

2) Nigdzie nie znalazłem tego zapisanego wprost, ale wydaje mi się, że plany zapisane z sp_executesql mają przypisany niższy koszt wykonania niż te z SP, czyli mają większą szansę na wylecenie z pamięci (czyli ponowne liczenie planu) w obciązonych systemach.

Ale summa summarum po przeczytaniu kilkudziesieciu stron dokumentacji wydaje mi się, że to co pisze Maciek ma ręce i nogi. Czas na dowody empiryczne :)Bartosz Ślepowronski edytował(a) ten post dnia 20.07.10 o godzinie 21:07

Temat: Szybkość procedury vs. adhoc query

maciek kański:

Każdy, kto się obawia ilości wygenerowanych planów niech sobie sprawdzi odpowiednie liczniki w SQL Serwerze - wpierw niech sprawdzi, a potem się martwi, nie na odwrót:)

Nie no z tym się nie zgodzę na pewno ;)
Faza projektu wymusza na nas zbudowanie aplikacji optymalnie na tyle ile się da a nie sprzedać komuś coś co generuje problemy i później je łatać czasami jest to zbyt kosztowne ;)
Chyba, że chcemy zarobić dodatkowo na poprawianiu swoich błędów :D

Temat: Szybkość procedury vs. adhoc query

Bartosz Ślepowronski:
Moja uwaga - to że SP zawsze używa tego samego planu też nie zawsze jest zaletą.

cenna uwaga i fajnie, że ja nie musiałem tego napisać. W takich przypadkach używamy with recompile:)

Czas na dowody empiryczne :)

Obiecałem i zamieszczę case'a, głównie na podstawie przykładów z SQL Server 2008 Internals

Ogólnie chciałbym, aby dyskusja w kwestiach technicznych zawsze była oparta na empirii i aby eksperyment był jedynym źródłem racji:) W branży IT koszt eksperymentu jest raczej niski więc IMHO każdy może sobie na to pozwolić.

Temat: Szybkość procedury vs. adhoc query

maciek kański:

Bartosz Ślepowronski:
Moja uwaga - to że SP zawsze używa tego samego planu też nie zawsze jest zaletą.

cenna uwaga i fajnie, że ja nie musiałem tego napisać. W takich przypadkach używamy with recompile:)

Ja znalazłem kiedyś jeszcze opcję o redefiniowaniu parametrów procedury wewnątrz. Jakoś w ten deseń: http://www.sqlpointers.com/2006/11/parameter-sniffing-...

Temat: Szybkość procedury vs. adhoc query

to akurat można rozwiązać poprzez optimize for, ale jest to oczywiście kwestia poprawności budowania planu wykonania względem selektywności wartości parametrów.