Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 39: оптимизация производительности с помощью хранимых процедур

MS SQL Решение 5.2.2.a (код процедуры)

1CREATE PROCEDURE UPDATE_BOOKS_STATISTICS

2AS

3IF (NOT EXISTS(SELECT *

7BEGIN

8RAISERROR ('The [books_statistics] table is missing.', 16, 1);

9RETURN;

10END;

11

12UPDATE [books_statistics]

13SET

14[books_statistics].[total] = [src].[total],

15[books_statistics].[given] = [src].[given],

16[books_statistics].[rest] = [src].[rest]

17FROM [books_statistics]

18JOIN

19(SELECT ISNULL([total], 0) AS [total],

20ISNULL([given], 0) AS [given],

21ISNULL([total] - [given], 0) AS [rest]

27AS [prepared_data]

28) AS [src]

29ON 1=1;

30GO

Проверим работоспособность (предварительно можно выполнить отдельный запрос на обновление данных в таблице books_statistics и установить все значения в ноль).

MS SQL Решение 5.2.2.a (запуск и проверка работоспособности)

1 EXECUTE UPDATE_BOOKS_STATISTICS

Установка запуска полученной хранимой процедуры по расписанию в MS SQL Server не только выглядит куда более сложно, но и не даст эффекта, если вы используете Express Edition этой СУБД: в этой версии отсутствует компонент SQL Server Agent, который и отвечает за выполнение запланированных задач.

По каждой приведённой далее команде в официальной документации написано очень много (перед каждой командой специально приведены ссылка на соответствующий раздел документации), но если выразить простыми словами алгоритм, получится следующее. Необходимо:

•Создать задачу.

•Создать шаг задачи, описывающий запуск нашей хранимой процедуры.

•Создать расписание, в котором указать требуемую периодичность выполнения.

•Прикрепить ранее созданную задачу к только что созданному расписанию.

•Передать созданную задачу на обработку в SQL Server Agent.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 390/545

Пример 39: оптимизация производительности с помощью хранимых процедур

1USE msdb ;

2GO

3-- https://msdn.microsoft.com/en-us/library/ms182079.aspx

4EXEC dbo.sp_add_job

5@job_name = N'Hourly [books_statistics] update';

6GO

7-- https://msdn.microsoft.com/en-us/library/ms187358.aspx

8EXEC sp_add_jobstep

9@job_name = N'Hourly [books_statistics] update',

10@step_name = N'Execute UPDATE_BOOKS_STATISTICS stored procedure',

11@subsystem = N'TSQL',

12@command = N'EXECUTE UPDATE_BOOKS_STATISTICS',

13@database_name = N'library_ex_2015_mod';

14GO

15-- https://msdn.microsoft.com/en-us/library/ms187320.aspx

16EXEC dbo.sp_add_schedule

17@schedule_name = N'UpdateBooksStatistics',

18@freq_type = 4,

19@freq_interval = 4,

20@freq_subday_type = 8,

21@freq_subday_interval = 1,

22@active_start_time = 000100 ;

23USE msdb ;

24GO

25-- https://msdn.microsoft.com/en-us/library/ms186766.aspx

26EXEC sp_attach_schedule

27@job_name = N'Hourly [books_statistics] update',

28@schedule_name = N'UpdateBooksStatistics';

29GO

30-- https://msdn.microsoft.com/en-us/library/ms178625.aspx

31EXEC dbo.sp_add_jobserver

32@job_name = N'Hourly [books_statistics] update';

33GO

Убедиться, что соответствующая задача добавлена в планировщик, можно выполнив следующий запрос (он покажет список задач даже в MS SQL Server Express Edition):

MS SQL Решение 5.2.2.a (просмотр расписания)

1 SELECT * FROM msdb.dbo.sysschedules

В данном обсуждении24 представлен очень хороший готовый SQL-скрипт для получения в удобной форме информации обо всех запланированных в MS SQL Server задачах.

На этом решение для MS SQL Server завершено.

Переходим к Oracle. Логика работы хранимой процедуры здесь полностью эквивалентна решениям для MySQL и MS SQL Server: мы проверяем наличие таблицы books_statistics в строках 5-15 (и завершаем работу хранимой процедуры, если таблицы нет), а в строках 17-27 выполняем запрос, обновляющий данные.

24 http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspx

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 391/545

Пример 39: оптимизация производительности с помощью хранимых процедур

Oracle Решение 5.2.2.a (код процедуры)

1CREATE OR REPLACE PROCEDURE UPDATE_BOOKS_STATISTICS

2AS

3rows_count NUMBER;

4BEGIN

5SELECT COUNT(1) INTO rows_count

6FROM ALL_TABLES

7WHERE OWNER = USER

8AND TABLE_NAME = 'books_statistics';

9

10IF (rows_count = 0)

11THEN

12RAISE_APPLICATION_ERROR(-20001,

14RETURN;

15END IF;

16

17UPDATE "books_statistics"

18SET ("total", "given", "rest") =

19(SELECT NVL("total", 0) AS "total",

28END;

29/

Проверим работоспособность (предварительно можно выполнить отдельный запрос на обновление данных в таблице books_statistics и установить все значения в ноль).

1 EXECUTE UPDATE_BOOKS_STATISTICS

Установка запуска полученной хранимой процедуры по расписанию выглядит следующим образом.

Убедиться, что соответствующая задача добавлена в планировщик, можно выполнив следующий запрос:

Oracle Решение 5.2.2.a (просмотр расписания)

1 SELECT * FROM ALL_SCHEDULER_JOBS WHERE OWNER=USER

На этом решение данной задачи завершено.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 392/545

Пример 39: оптимизация производительности с помощью хранимых процедур

Решение 5.2.2.b{388}.

Решение этой задачи одновременно является очень простым и очень сложным, т.к. нам нужно получить список таблиц базы данных и… что-то с ними сделать.

Вопрос оптимизации производительности баз данных и СУБД заслуживает отдельной книги, потому здесь мы пойдём по пути наименьшего сопротивления и будем считать, что:

•Для MySQL будет достаточно выполнить OPTIMIZE для всех таблиц.

•Для MS SQL Server достаточно выполнить REORGANIZE или REBUILD для всех кластерных индексов (что приводит к оптимизации соответствующей таблицы, на которых построен индекс).

•Для Oracle будет достаточно выполнить SHRINK SPACE COMPACT CASCADE для всех таблиц.

Ещё раз особо подчеркнём: решение данной задачи носит исключительно демонстрационный характер и не должно рассматриваться как рекомендация по универсальной оптимизации производительности. В некоторых случаях выполнение показанных ниже действий может снизить производительность базы данных, потому обязательно внимательно изучите официальную документацию по соответствующей СУБД и профессиональные рекомендации по оптимизации производительности в той или иной реальной ситуации.

Традиционно начинаем с MySQL.

MySQL Решение 5.2.2.b (код процедуры)

1DELIMITER $$

2CREATE PROCEDURE OPTIMIZE_ALL_TABLES()

3BEGIN

4DECLARE done INT DEFAULT 0;

5DECLARE tbl_name VARCHAR(200) DEFAULT '';

6DECLARE all_tables_cursor CURSOR FOR

7SELECT `table_name`

15tables_loop: LOOP

16FETCH all_tables_cursor INTO tbl_name;

17IF done THEN

18LEAVE tables_loop;

19END IF;

20

21SET @table_opt_query = CONCAT('OPTIMIZE TABLE `', tbl_name, '`');

22PREPARE table_opt_stmt FROM @table_opt_query;

23EXECUTE table_opt_stmt;

24DEALLOCATE PREPARE table_opt_stmt;

25

26 END LOOP tables_loop;

27

28CLOSE all_tables_cursor;

29END;

30$$

31

32 DELIMITER ;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 393/545

Пример 39: оптимизация производительности с помощью хранимых процедур

Запрос в строках 7-10 позволяет получить список таблиц текущей базы данных (часть условия в строке 10 позволяет отличить таблицы от представлений), после чего:

•для полученного списка таблиц открывается курсор (строка 13);

•для всех строк курсора выполняется цикл (строки 15-26);

•в теле цикла формируется (строка 21) и выполняется (строки 22-23) запрос, реализующий оптимизацию таблицы.

Запустить полученную хранимую процедуру можно следующим образом.

1 CALL OPTIMIZE_ALL_TABLES

Теперь остаётся создать событие, запускаемое планировщиком задач MySQL по определённому расписанию. Это реализуется следующим кодом.

1 SET GLOBAL event_scheduler = ON;

2

3CREATE EVENT `optimize_all_tables_daily`

4ON SCHEDULE

5EVERY 1 DAY

6STARTS DATE(NOW()) + INTERVAL 1 HOUR

7ON COMPLETION PRESERVE

8DO

9CALL OPTIMIZE_ALL_TABLES;

Убедиться, что событие добавлено, можно следующим запросом.

MySQL Решение 5.2.2.b (просмотр расписания)

1 SELECT * FROM `information_schema`.`events`

Если рассмотреть избранные поля результата такого запроса, получается следующая картина. Здесь также представлено событие, активирующее раз в час хранимую процедуру, созданную в процессе решения{388} задачи 5.2.2.a{388}.

На этом решение для MySQL завершено.

Переходим к MS SQL Server. Концепция представленного ниже решения основана на этой рекомендации25.

Итак, мы должны будем получить список кластерных индексов текущей базы данных, выяснить степень их фрагментации (avg_fragmentation_in_percent)

и, в зависимости от этого значения, выполнить реорганизацию (REORGANIZE) или

перестроение (REBUILD) индекса.

Для начала напишем запрос, предоставляющий информацию по всем индексам, соответствующим таблицам, полям и т.д. К сожалению, готового решения MS SQL Server не предоставляет, потому придётся всё делать «вручную».

25 http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 394/545