Производительность хранимых процедур MS SQL Server 2000
Руслан Разбежкин
13.06.2005
При компиляции хранимых процедур в MS SQL Server 2000 хранимые процедуры помещаются в процедурный кэш, что может способствовать увеличению производительности при их выполнении за счет исключения необходимости в синтаксическом разборе, оптимизации и компиляции кода хранимых процедур.
С другой стороны в хранении откомпилированного кода хранимой процедуры кроются подводные камни, которые могут иметь обратный эффект.
Дело в том, что при компиляции хранимой процедуры компилируется план выполнения тех операторов, которые составляют код процедуры, соответственно, если откомпилированная хранимая процедура кэшируется, то кэшируется и ее план выполнения, а следовательно, хранимая процедура не будет оптимизироваться под конкретную ситуацию и параметры запросов.
Проведет небольшой эксперимент, чтобы продемонстрировать это.
ШАГ 1. Создание БД.
Для эксперимента создадим отдельную базу данных.
CREATE DATABASE test_sp_perf
ON (NAME='test_data', FILENAME='c:\temp\test_data', SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME='test_log', FILENAME='c:\temp\test_log', SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
ШАГ 2. Создание таблицы.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))
ШАГ 3. Заполнение таблицы тестовыми строками. В таблицу намеренно добавляются дублирующиеся строки. 10000 строк с номерами от 1 до 10000, и 10000 строк с номерами 50000.
DECLARE @i int
SET @i=1
WHILE(@i<10000)
BEGIN
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,'Test string #'+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,'Test string #'+CAST(@i as char(8)))
SET @i=@i+1
END
SELECT COUNT(*) FROM sp_perf_test
GO
ШАГ 4. Создание некластерного индекса. Поскольку план выполнения кэшируется вместе с процедурой, индекс будет при всех вызовах использоваться одинаково.
CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
GO
ШАГ 5. Создание хранимой процедуры. Процедура просто выполняет оператор SELECT с условием.
CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE column1=@param
GO
ШАГ 6. Запуск хранимой процедуры. При запуске ранимой процедуры специально используется селективный параметр. В результате выполнения процедуры получаем 1 строку. План выполнения показывает на использование некластерного индекса, т.к. запрос селективный и это наилучший способ извлечь строку. Процедура, оптимизированная на выборку одной строки, сохраняется в процедурном кэше.
EXEC proc1 1234
GO
ШАГ 7. Запуск хранимой процедуры с неселективным параметром. В качестве параметра используется значение 50000. Строк с таким значением первого столбца около 10000, соответственно, пользоваться некластерным индексом и операцией bookmark lookup неэффективно, но поскольку откомпилированный код с планом выполнения храниться в процедурном кэше, именно он и будет использоваться. План выполнения показывает это, а так же то, что для 9999 строк выполнялась операция bookmark lookup.
EXEC proc1 50000
GO
ШАГ 8. Выполнение выборки строк с первым полем равным 50000. При выполнении отдельного запроса выполняется оптимизация и компиляция запроса с конкретным значением первого столбца. В результате оптимизатор запросов определяет, что поле много раз дублируется и принимает решение использовать операцию table scan, что в данном случае намного эффективнее, чем использование некластерного индекса.
SELECT column1, column2 FROM sp_perf_test WHERE column1=50000
GO
Таким образом, можно заключить, что использование хранимых процедур не всегда может повысить производительность запросов. Следует крайне внимательно подходить к тем хранимым процедурам, которые работают с результатами с переменным количеством строк и использующие различные планы выполнения.
Можете использовать скрипт для повторения эксперимента на своем MS SQL сервере.
