Практикум по Базам Данных (Методические материалы) (1127013), страница 7
Текст из файла (страница 7)
Создать такой триггер можно, например, следующим образом:CREATE TRIGGER LimitLikesON [Like] AFTER INSERT ASIF EXISTS(SELECT [Like].UserIDFROM [Like]WHERE[Like].UserID IN (SELECT UserID FROM inserted) ANDCONVERT(VARCHAR, [Like].[Date], 105) IN(SELECT CONVERT(VARCHAR, inserted.[Date], 105)FROM insertedWHERE inserted.UserID = [Like].UserID)GROUP BY [Like].UserID, CONVERT(VARCHAR, [Like].[Date], 105)HAVING COUNT([Like].SongID) > 5)BEGINROLLBACKENDЭтот триггер будет срабатывать после вставки записей в таблицу Like (AFTER INSERT) и непозволит добавлять новые строки, если лимит в 5 оценок в сутки превышен. Проверим теперьработу триггера:INSERTINSERTINSERTINSERTINSERT[Like][Like][Like][Like][Like]([UserID],([UserID],([UserID],([UserID],([UserID],[SongID],[SongID],[SongID],[SongID],[SongID],[Score])[Score])[Score])[Score])[Score])VALUESVALUESVALUESVALUESVALUES(10,(10,(10,(10,(10,1,2,3,4,5,5)5)5)5)5)-- Шестая оценка превышает лимитINSERT [Like] ([UserID], [SongID], [Score]) VALUES (10, 6, 5)Msg 3609, Level 16, State 1, Line 7The transaction ended in the trigger.
The batch has been aborted.Примеры вопросов по обязательной части37Объяснить принцип работы написанного триггера.Какие бывают типы триггеров?Когда может срабатывать триггер?В каком порядке срабатывают триггеры?Можно ли менять порядок срабатывания триггеров?Сработает ли триггер, если оператор, выполненный пользователем, не затрагивает ни однустроку таблицы?Примеры дополнительных вопросов Исправить ошибки в работе триггера. Модифицировать триггер каким-либо образом.38Практическое задание №10. Использование индексов и средствоптимизации запросов в MS SQL ServerПостановка задачиДесятое практическое задание посвящено ускорению выполнения запросов при помощииспользования индексов. Для выполнения задания необходим достаточно большой объемданных, чтобы использование индексов было целесообразным (порядка 1000000 строк в каждойтаблице). Необходимо заполнить соответствующие таблицы и подготовить два запроса: Запрос к одной таблице, содержащий фильтрацию по нескольким полям. Запрос к нескольким связанным таблицам, содержащий фильтрацию по нескольким полям.Для каждого из этих запросов необходимо провести следующие шаги:Получить план выполнения запроса без использования индексов.Получить статистику (IO и Time) выполнения запроса без использования индексов.Создать нужные индексы, позволяющие ускорить запрос.Получить план выполнения запроса с использованием индексов и сравнить спервоначальным планом. Получить статистику выполнения запроса с использованием индексов и сравнить спервоначальной статистикой. Оценить эффективность выполнения оптимизированного запроса.Темы для проработки Понятие индекса, типы индексов, внутренняя организация индексов. Работа с индексами в среде MS SQL Server Management Studio. Использование индексов для повышения скорости выполнения запросов.ПримерыРассмотрим простейший пример использования индексов.
Для того чтобы использованиеиндексов было целесообразным, необходимо подготовить достаточно большое количестводанных. Создадим таблицы UserLarge и UserLargeIndex для этих целей:SELECT * INTO UserLarge FROM [User]SELECT * INTO UserLargeIndex FROM [User]Далее, увеличим количество данных в этих таблицах (может занять длительное время):INSERT INTO [UserLarge]SELECT FirstName, LastName, Sex, BirthDate, CityID, Email, PasswordHashFROM [User]INSERT INTO [UserLargeIndex]SELECT FirstName, LastName, Sex, BirthDate, CityID, Email, PasswordHashFROM [User]GO 10000039Рассмотрим теперь следующий простой запрос:SELECT FirstName, LastNameFROM [UserLarge]WHERE [FirstName] = 'John' AND [LastName] = 'Smith'Не создавая пока никаких индексов, оценим производительность и план выполнения запроса.Для того, чтобы оценить время выполнения и количество операций ввода-вывода, включимвывод статистики:SET STATISTICS TIME ONSET STATISTICS IO ONТакже необходимо включить отображение плана выполнения запроса.
Запрос безиспользования индексов дает следующие показатели и план выполнения:Table 'UserLarge'. Scan count 1, logical reads 11113, physical reads 0, read-ahead reads 11113,lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:CPU time = 188 ms, elapsed time = 6156 ms.Стоит заметить, что при оценке производительности запросов бывает полезным очищать кэшSQL Server:CHECKPOINTGODBCC DROPCLEANBUFFERSGOТеперь создадим индекс в таблице UserLargeIndex и сравним стоимость выполнения одного итого же запроса к таблицам UserLarge (без индексов) и UserLargeIndex (с индексом):CREATE NONCLUSTERED INDEX [IX_FirstNameLastName] ON [dbo].[UserLargeIndex]([FirstName] ASC,[LastName] ASC,)Выполним запрос к таблице UserLargeIndex и оценим статистику:SELECT FirstName, LastNameFROM [UserLargeIndex]WHERE [FirstName] = 'John' AND [LastName] = 'Smith'Table 'UserLargeIndex'.
Scan count 1, logical reads 327, physical reads 3, read-ahead reads 324,lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:CPU time = 47 ms, elapsed time = 2703 ms.Сравним теперь стоимость запросов к таблицам UserLarge и UserLargeIndex:SELECT FirstName, LastNameFROM [UserLarge]40WHERE [FirstName] = 'John' AND [LastName] = 'Smith'SELECT FirstName, LastNameFROM [UserLargeIndex]WHERE [FirstName] = 'John' AND [LastName] = 'Smith'GOИз этого примера видно, что индекс позволяет существенно ускорить выполнение запросов,позволяя избежать дорогостоящих операций сканирования таблицы.Примеры вопросов по обязательной частиЧто такое кластерный индекс? В чем его отличие от некластерного?Можно ли создать неуникальный кластерный индекс?В чем отличие первичного ключа и уникального индекса?В каких случаях имеет смысл создавать индексы? Какие колонки следует включать в индекси почему?Какие существуют способы внутренней организации индексов?Рассказать о проблеме фрагментации индексов.
Как бороться с фрагментацией?Имеет ли значение порядок указания колонок при создании индекса?В чем разница между Index Scan и Index Seek?Примеры дополнительных вопросов Исправить ошибки в подготовленных выборках. Могут ли индексы ухудшить производительность? Если да, то продемонстрировать это. На что влияет порядок сортировки (ASC\DESC) при создании индекса? Продемонстрироватьэто. Объяснить и продемонстрировать разницу в скорости выполнения запросов в зависимостиот того, входят ли все колонки из списка выборки в некластерный индекс.41Литература и материалы1.
Кузнецов С. Д. Основы баз данных. – М.: Бином. Лаборатория знаний, Интернет-университетинформационных технологий. 20072. Microsoft SQL Server 2005 Books Online. [HTML]http://msdn.microsoft.com/en-us/library/ms130214%28v=sql.90%29.aspx3. Кузнецов С. Д. Основы современных баз данных. [HTML]http://citforum.ru/database/osbd/contents.shtml4. Гарсиа-Молина Г., Ульман Дж., Уидом Д. Системы баз данных Полный курс. – М.: Вильямс, 20045.
Ульман Дж., Уидом Д. Основы реляционных баз данных. – М.: Лори. 2006.6. Коннолли Т., Бегг К. Базы данных: проектирование, реализация и сопровождение. Теория ипрактика. – М.: Вильямс. 2003.7. Станек У. Р. Microsoft SQL Server 2005. Справочник администратора – М.: Русская редакция.2008.8. Нильсен П. SQL Server 2005. Библия пользователя. – М.: Вильямс. 2008Web-ресурсы1. http://spprac.cs.msu.ru/2. http://bdis.umeta.ru/db/42.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
