Готовые работы / Практическая работа 6 (искусственная тема, оценка 1)

Таблица 1. Скрипт генерации таблиц с данными (MS SQL Server 2016) generate_script.sql

[id] [bigint] IDENTITY(1,1) NOT NULL, [name] [varchar](100) NOT NULL,

CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Books](

[id] [bigint] IDENTITY(1,1) NOT NULL, [title] [varchar](200) NOT NULL, [author_id] [bigint] NOT NULL, [description] [text] NULL, [book_open_count] [bigint] NOT NULL,

CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

SET IDENTITY_INSERT [dbo].[Authors] ON

INSERT [dbo].[Authors] ([id], [name]) VALUES (1, N'User1')

INSERT [dbo].[Authors] ([id], [name]) VALUES (2, N'User2')

INSERT [dbo].[Authors] ([id], [name]) VALUES (3, N'User3')

INSERT [dbo].[Authors] ([id], [name]) VALUES (4, N'User4')

INSERT [dbo].[Authors] ([id], [name]) VALUES (5, N'User5')

SET IDENTITY_INSERT [dbo].[Authors] OFF

GO

SET IDENTITY_INSERT [dbo].[Books] ON

INSERT [dbo].[Books] ([id], [title], [author_id], [description], [book_open_count]) VALUES (1, N'Book 1', 1, N'Description 1', 0)

INSERT [dbo].[Books] ([id], [title], [author_id], [description], [book_open_count]) VALUES (2, N'Book 2', 2, N'Description 2', 0)

INSERT [dbo].[Books] ([id], [title], [author_id], [description], [book_open_count]) VALUES (3, N'Book 3', 3, N'Description 3', 0)

INSERT [dbo].[Books] ([id], [title], [author_id], [description], [book_open_count]) VALUES (4, N'Book 4', 4, N'Description 4', 0)

INSERT [dbo].[Books] ([id], [title], [author_id], [description], [book_open_count]) VALUES (5, N'Book 5', 5, N'Description 5', 0)

SET IDENTITY_INSERT [dbo].[Books] OFF GO

ALTER TABLE [dbo].[Books] WITH CHECK ADD CONSTRAINT [FK_Books_Authors] FOREIGN KEY([author_id])

REFERENCES [dbo].[Authors] ([id]) ON UPDATE CASCADE

ON DELETE CASCADE GO

ALTER TABLE [dbo].[Books] CHECK CONSTRAINT [FK_Books_Authors] GO

2

SQL транзакция включает в себя 2 основные операции:

UPDATE Books SET book_open_count = book_open_count - 1 WHERE id = ?; UPDATE Books SET book_open_count = book_open_count + 1 WHERE id = ?;

Пока сайт не особо популярен проблемы не видны, но, когда им станут пользоваться десятки тысяч людей и более, страницы будут загружаться медленно, а может и вообще будут падать с таймаутом. Код для эмуляции приведен в табл. 2.

Таблица 2. Эмулятор обновлений счетчика на Python 3.7 test.py

import pyodbc import random import time

import multiprocessing

server = 'localhost' database = 'TestDB' username = 'MainUser'

password = 'SpecPassword123!'

cnxn_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password number_of_processes = 200

number_of_queries_per_processes = 100 min_id = 1

max_id = 6

def update_pages(q): global min_id, max_id

global cnxn_string, number_of_queries_per_processes prev_id = None

for i in range(number_of_queries_per_processes): start = time.time()

cnxn = pyodbc.connect(cnxn_string, autocommit=False) cursor = cnxn.cursor()

next_id = random.randint(min_id, max_id) if prev_id is not None:

tsql = "UPDATE Books SET book_open_count = book_open_count - 1 WHERE id = ?;" cursor.execute(tsql, prev_id)

if i < number_of_queries_per_processes - 1:

tsql = "UPDATE Books SET book_open_count = book_open_count + 1 WHERE id = ?;" cursor.execute(tsql, next_id)

prev_id = next_id cnxn.commit() end = time.time()

q.put(end - start)

if __name__ == '__main__': start = time.time() processes = []

q = multiprocessing.Manager().Queue() for i in range(number_of_processes):

processes.append(multiprocessing.Process(target=update_pages, args=(q,))) for i in range(number_of_processes):

processes[i].start() try:

for i in range(number_of_processes): processes[i].join()

except:

for i in range(number_of_processes): processes[i].terminate()

end = time.time() max_time = 0. mean_time = 0.

3

q_len = 0

while not q.empty(): item = float(q.get())

max_time = max(max_time, item) mean_time += item

q_len += 1 mean_time /= q_len

print(f'Число запрашиваемых ресурсов: {max_id - min_id + 1}')

print(f'Число запросов: {number_of_processes * number_of_queries_per_processes}') print(f'Среднее время ожидания: {mean_time} сек.')

print(f'Максимальное время ожидания: {max_time} сек.') print(f'Время тестирования: {end - start} сек.')

Результат выполнения представлен на рис. 2.

Рисунок 2. Результат выполнения

В данном случае работают только запросы UPDATE. У клиентов,

которые будут получать статистику по всем книгам (запросы SELECT),

результаты могут быть хуже из-за блокировки нескольких строк (запросами

UPDATE), а не только двух (рис. 3).

Рисунок 3. Результат запроса «SELECT * FROM Books» в DBeaver

Вариант решения этой проблемы.

Обрабатывать не по одному, а по несколько запросов. Например, можно добавить промежуточную таблицу, в которую вставляются новые записи с помощью оператора INSERT, а фоновая задача время от времени агрегирует эти данные и делает UPDATE основной таблицы.

Вставка 20000 записей занимает около 16 секунд.

UPDATE запроса основной таблицы Books по промежуточной

CounterUpdates (id, book_id, diff_value=-1/+1) занимает <50 мс:

4

UPDATE b SET b.book_open_count += ds FROM Books b INNER JOIN (SELECT cu.book_id, SUM(cu.diff_value) ds FROM CounterUpdates cu GROUP BY cu.book_id) AS tt ON b.id = tt.book_id;

Очистка таблицы CounterUpdates занимает <16 мс:

TRUNCATE TABLE CounterUpdates;

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

очистки таблицы CounterUpdates.

Кроме того, необязательно добавлять внешнее связывание

(CounterUpdates.book_id → Books.id), т.к. целостность все равно не нарушится,

а UPDATE запрос не завершится с ошибкой.

Результат выполнения измененной версии представлен на рис. 4.

Рисунок 4. Результат выполнения измененной версии

Общее время тестирования сократилось с 23 до 12 сек.

Среднее время ожидания уменьшилось примерно в 2 раза.

Такая функциональность для сайтов довольно избыточна и вряд ли будет когда-либо применяться.

5