|
|
По материалам статьи G. Vijayakumar Pros
& Cons of Using SELECT, Views, and Stored Procedures in
SQL Server Когда я начинал изучать SQL Server, я вначале не мог понять
разницы между командой SELECT, представлением и хранимой
процедурой. Казалось, они все выполняют практически одинаковую
задачу (получение данных) и мне хотелось узнать все за и
против их использования.
Чтобы начать рассмотрение различий между командой SELECT, представлением и хранимой процедурой, мне нужно напомнить о системной таблице syscacheobjects. Она используется для хранения информации о скомпилированных объектах и их планах выполнения. Причиной напоминания об этой таблице являются хранящиеся в ней скомпилированные команды SELECT, представления и хранимые процедуры, и я использовал эту таблицу, чтобы экспериментировать и узнать больше о том, как эти 3 разных объекта хранятся и используются в SQL Server. Если вы не знаете эту системную таблицу, то вам будет полезно взглянуть на нее. Она хранится в базе данных master и может быть просмотрена при помощи Enterprise Manager или Query Analyzer. Если вы будете выполнять примеры из этой статьи, то вам нужно будет выполнять команду DBCC FREEPROCCACHE перед каждым запуском. Эта команда удаляет в таблице syscacheobjects все кэшированные объекты и позволяет проводить более аккуратные тесты. Теперь давайте создадим таблицу и добавим в нее несколько строк перед рассмотрением различий между командой SELECT, представлением и хранимой процедурой. Я предполагаю, что у вас есть база данных для создания таблицы. Если нет, то вам нужно создать базу данных. После этого нам нужно создать таблицу для экспериментирования.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
Теперь давайте добавим несколько записей в эту таблицу с помощью следующего скрипта:
Insert Into DummyTable1 Values (1, Replicate ('a',20))
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
GO
Таблица DummyTable1 теперь содержит достаточное количество строк для экспериментирования с различиями между командой SELECT, представлением и хранимой процедурой. Давайте начнем с команды SELECT и посмотрим, чем она отличается от представления или хранимой процедуры. Теперь давайте взглянем на содержимое таблицы, выполнив следующую команду в Query Analyzer для новой таблицы. SELECT EmpId, EmpName FROM DummyTable1 GO
Как предолагается, данные были введены перед тем, как была выполнена эта команда. Теперь давайте запустим следующую команду для очистки кэша. DBCC FREEPROCCACHE GO Очистка процедурного кэша предотвращает повторное использование скомпилированной команды SQL. Это значит, что когда в следующий раз мы запустим ту же команду, она будет снова скомпилирована. Теперь давайте выполним следующие команды для вывода информации кэша о созданной таблице. Эта информация хранится в системной таблице SQL Server syscacheobjects. SELECT EmpId, EmpName FROM DummyTable1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects GO В результате мы получим много столбцов, но нас пока интересуют только 4 из них, которые показаны ниже.
Вот что означает эта информация: Cacheobjtype: тип хранимого в кэше объекта, который может включать:
Мы в основном сконцентрируемся на скомпилированном и выполненном планах. Refcounts: количество других кэшированных объектов, ссылающихся на этот кэшированный объект. Количество, равное 1, относится к базе. Usecounts: количество раз, которое этот кэшированный объект использовался со времени его появления в кэше. Sql: текст команды. Теперь давайте выполним ту же команду SELECT: SELECT EmpId, EmpName FROM DummyTable1 GO
Значение Usecounts увеличилось. SQL Server использовал тот же скомпилированный план выполнения для команды SELECT и увеличил значение Usecounts выполненного плана. SQL Server будет использовать тот же скомпилированный план выполнения, когда мы выполняем ту же команду SELECT. Теперь давайте добавим выражение 'WHERE' в команду SELECT и посмотрим результат в таблице master.dbo.Syscacheobjects. SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO SQL Server не использовал существующий кэшированный план из-за изменения команды SELECT. SQL Server сгенерирует новый план в кэше для команды SELECT, оставив старый план.
Давайте выполним ту же команду SELECT с разным empid и проверим результат. SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Хотя мы передали разные значения empid, SQL Server использовал тот же скомпилированный план и увеличил значение Usecounts выполненного плана. Даже если другой пользователь выполнит команду SELECT с другими значениями empid, то все равно при этом будет использован тот же скомпилированный план и увеличено значение Usecounts выполненного плана. Теперь давайте выполним ту же команду с именем пользователя в команде SELECT и проверим результаты. SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь у нас в кэше есть еще 2 строки, потому что мы использовали разные имена пользователей в команде SELECT. SQL Server генерирует новые скомпилированный план и выполненный план для разных пользователей. Если же этот пользователь выполнит команду SELECT больше одного раза, то SQL Server использует тот же скомпилированный план и только увеличит значение Usecounts выполненного плана. Давайте выполним ту же команду SELECT с другим empid и проверим результаты. SELECT EmpId, EmpName FROM dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь давайте выполним ту же команду с указанием базы данных и владельца в той же команде SELECT и посмотрим результаты. SELECT EmpId, EmpName FROM vijay.dbo.DummyTable1 WHERE EmpId = 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Как администраторы или разработчики баз данных, мы можем минимизировать количество созданных скомпилированных планов, если будем добавлять имена базы данных и владельца и выражение WHERE в команду SELECT. Не изменяйте состав команды SELECT, чтобы не создавать новый план выполнения, без особой необходимости. Если вы это сделаете, то SQL Server создаст новый план выполнения команды SELECT. Мы можем минимизировать количество планов выполнения, чтобы повысить производительность системы. Теперь давайте выполним следующую команду для очистки кэша перед экспериментированием с хранимыми процедурами. DBCC FREEPROCCACHE GO Мы создадим хранимую процедуру с одним параметром и посмотрим, чем она отличается от команды SELECT и представления. CREATE PROC spDummyTable1 (@EmpID Int) AS SELECT EmpID, EmpName FROM DummyTable1 WHERE EmpID = @EmpID Теперь давайте выполним следующие команды, чтобы увидеть данные и информацию из кэша для хранимой процедуры spDummyTable1. EXEC spDummyTable1 1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
SQL Server отображает скомпилированный и выполненный планы для хранимой процедуры spDummyTable1. Давайте снова выполним ту же команду и посмотрим информацию в кэше. EXEC spDummyTable1 1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Значение Usecounts увеличилось. SQL Server использовал тот же скомпилированный план для команды SELECT и увеличил значение Usecounts выполненного плана. Один и тот же скомпилированный план будет использоваться все время, пока будет выполняться одна и та же хранимая процедура. Давайте выполним ту же хранимую процедуру с другим значением параметра empid и взглянем на информацию в кэше. EXEC spDummyTable1 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Значение Usecounts увеличилось. Хотя мы задали другое значение empid, SQL Server использовал тот же скомпилированный план для хранимой процедуры и увеличил значение Usecounts выполненного плана. Теперь давайте выполним эту же хранимую процедуру с указанием владельца и взглянем на информацию в кэше. EXEC dbo.spDummyTable1 5 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Никаких изменений. SQL Server использовал тот же скомпилированный план для хранимой процедуры и увеличил значение Usecounts выполненного плана. Давайте выполним эту же хранимую процедуру другим пользователем. Я создал нового пользователя, 'user1', и дал ему права на выполнение хранимой процедуры spDummyTable1. Я открыл другой Query Analyzer и подсоединился, используя UID: user1; PWD : user1. После этого я выполнил следующую команду. EXEC dbo.spDummyTable1 3 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO Разные пользователи, выполняя хранимую процедуру с разным или одинаковым значением empid, используют тот же скомпилированный план и увеличивают значение Usecounts выполненного плана. Теперь давайте выполним эту же хранимую процедуру с указанием базы данных и владельца и взглянем на информацию в кэше. EXEC vijay.dbo.spDummyTable1 7 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Никаких изменений. SQL Server использовал тот же скомпилированный план для хранимой процедуры и увеличил значение Usecounts выполненного плана. В результате можно сделать вывод, что хранимая процедура компилируется один раз, использует один и тот же скомпилированный план и увеличивает значение Usecounts выполненного плана. Хранимая процедура уже предварительно загружена в память для быстрого выполнения, что повышает производительность системы. Это действительно показывает нам важность хранимой процедуры по сравнению с командой SELECT и представлением. Теперь давайте выполним следующую команду для очистки кэша перед экспериментированием с представлениями. DBCC FREEPROCCACHE GO Мы создадим представление и посмотрим, чем оно отличается от команды SELECT и хранимой процедуры. CREATE VIEW vwDummyTable1 AS SELECT EmpID, EmpName FROM DummyTable1 GO Теперь давайте выполним следующие команды для отображения данных и информации в кэше для созданного представления vwDummyTable1. SELECT EmpId, EmpName from vwDummyTable1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
SQL Server показывает скомпилированный план и выполненный план для представления vwDummyTable1. Теперь давайте снова выполним ту же команду и взглянем на информацию в кэше. SELECT EmpId, EmpName from vwDummyTable1 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Значение Usecounts увеличилось. SQL Server использовал тот же скомпилированный план для команды SELECT представления и увеличил значение Usecounts выполненного плана. Один и тот же скомпилированный план будет использоваться все время, пока мы будем выполнять ту же команду SELECT представления. Теперь давайте добавим выражение WHERE в команду SELECT представления и взглянем на результаты в таблице master.dbo.Syscacheobjects. SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO SQL Server не использовал существующий план в кэше из-за изменения команды SELECT представления. SQL Server сгененировал новый план кэша для команды SELECT представления, оставив там и старый план.
Давайте выполним ту же команду SELECT представления с другим empid и взглянем на результаты. SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Хотя мы задали другое значение empid, SQL Server использовал тот же скомпилированный план для команды SELECT представления и увеличил значение Usecounts выполненного плана. Разные пользователи могут выполнять команду SELECT представления с разными значениями empid, при этом будет использоваться тот же скомпилированный план и будет увеличиваться значение Usecounts выполненного плана. Теперь давайте выполним ту же команду представления с указанием владельца в SELECT и взглянем на результаты. SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь мы получили еще 2 строки в кэше, потому что использовали разные имена владельцев в команде SELECT. SQL Server генерирует новый скомпилированный план и выполненный план для другого пользователя. Если этот пользователь выполнит команду SELECT больше одного раза, то будет использован этот же скомпилированный план и будет увеличено значение Usecounts выполненного плана. Давайте выполним ту же команду SELECT представления с другим empid и проверим результат. SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 6 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Теперь давайте выполним ту же команду с указанием базы данных и владельца в команде SELECT представления и проверим результат. SELECT EmpId, EmpName from vijay.dbo.vwDummyTable1 WHERE EmpID = 10 GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Мы увидели, что можем минимизировать создание скомпилированных планов, если добавим имя базы данных, владельца и выражение WHERE в команду SELECT или в представление. Если вы измените что-либо из этого, то ваш код придется перекомпилировать, что уменьшит производительность системы. Так какая же разница между командой SELECT и представлением относительно производительности? Есть ли вообще между ними разница? Нет, скомпилированный план и выполненный план одинаковы и для команды SELECT, и для представления. Есть только одно различие - представление физически хранится в базе данных, а команда SELECT - нет. Преимуществом представлений является более легкое администрирование прав доступа к объектам. Например, вы создаете представление и даете права на выполнение команды SELECT определенному набору пользователей, что дает право этим пользователям видеть только определенные столбцы в таблице, а не все. В большинстве случаев, если вам не нужно обеспечивать с помощью представлений дополнительную безопасность, то в использовании представлений нет необходимости. G. Vijayakumar имеет опыт работы с клиент-серверными и web-приложениями. В настоящее время он работает в Transworld (Бангалор, Индия) и занимается e-banking продуктами. |
Перевод: Виталия Степаненко 2004г. |