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