Использование представлений для сокрытия промежуточных данных в Microsoft SQL Server.
Введение
При разработке различного рода коммерческих баз данных довольно часто встает задача обновления заранее просчитанных итоговых данных. Чаще всего это требуется для предоставления различного рода отчетности. Не всегда для этого используется технология OLAP, и поэтому в этой статье я приведу описание приема, который позволяет организовать незаметное для пользователя обновление таких данных.
Для начала сформулируем проблему, которую должен решить данный механизм. Проще всего сделать это на примере. Предположим, что используя базу Northwind, нам требуется предоставлять отчетность о кол-ве и объемах продаж за любой день по любому товару. Мы можем сделать это, создав View следующего вида:
CREATE VIEW [Sales by Product] AS SELECT Orders.OrderDate, [Order Details].ProductID, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, SUM([Order Details].Quantity) AS Quantity FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID GROUP BY Orders.OrderDate, [Order Details].ProductID |
Возможным решением проблемы могла бы быть замена view на таблицу с периодически обновляемыми данными:
CREATE TABLE [Sales by Product] ( OrderDate datetime NOT NULL , ProductID int NOT NULL , Amount money NOT NULL , Quantity int NOT NULL ) |
Например:
CREATE CLUSTERED INDEX [Index1] ON [Sales by Product] (OrderDate, ProductID) |
DELETE FROM [Sales by Product] INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity) SELECT Orders.OrderDate, [Order Details].ProductID, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, SUM([Order Details].Quantity) AS Quantity FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID GROUP BY Orders.OrderDate, [Order Details].ProductID |
Первая проблема: Очистка и обновление такой таблицы может происходить очень долго. В реальной практике расчет итоговых значений для такой таблицы может быть намного сложнее чем в данном примере. В нем могут участвовать множество таблиц с большим количеством записей в каждой, он может содержать сложные вычисления и т.п, так что он, например, может выполняться несколько часов.
Вторая проблема: В течении всего этого времени пользователи не смогу видеть данные о продажах, так как они были удалены первым запросом.
Описанный здесь механизм позволяет решить обе эти проблемы.
Описание механизма
Для решения первой проблемы нужно принять во внимание, что далеко не все строки таблицы Sales by Product нуждаются в обновлении. Заказы редко вписываются «задним числом», поэтому обычно в обновлении нуждается несколько последних дней и очень редко данные в далеком прошлом. Поэтому, для ускорения процесса расчета можно создать дополнительную вспомогательную таблицу, которая будет содержать даты, для которых требуется пересчет.
CREATE TABLE DatesForUpdates ( UpdateDate datetime NOT NULL ) |
DELETE [Sales by Product] FROM [Sales by Product], DatesForUpdates WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity) SELECT Orders.OrderDate, [Order Details].ProductID, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, SUM([Order Details].Quantity) AS Quantity FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate GROUP BY Orders.OrderDate, [Order Details].ProductID DELETE FROM DatesForUpdates |
Однако, это не решает второй проблемы – для конечного пользователя данные по прежнему пропадают на некоторое время.
Казалось бы, решением проблемы может быть отказ от удаления данных перед их вставкой. Мы могли бы подготовить временную таблицу с вновь рассчитанным набором данным, затем обновить строки, которые уже существуют в Sales by Product, вставить новые строки и удалить лишние (на тот случай если часть заказов была удалена). Однако, это не решит данную проблему, потому что даже в этом случае существует вероятность того, что пользователь получит некорректные данные, если, например, выполнит запрос на выборку между операциями обновления и удаления. Использование транзакции в этом случае тоже не является выходом, так как данные в этом случае будут заблокированы и недоступны для пользователя до момента завершения транзакции.
Для решения этой проблемы потребуется провести некоторые преобразования, которые как раз и отражают суть рассматриваемого механизма.
Во-первых: в таблицу Sales by Product добавим поле Version, которое означает версию строки таблицы и может содержать следующие значения:
- 1 – строка, доступная для пользователя 2 – строка, содержащая новые данные. Пользователю не доступна. 3 – строка, содержащая устаревшие данные. Пользователю также не доступна
CREATE VIEW dbo.[Report by Product] AS SELECT * FROM [Sales by Product] WHERE Version = 1 |
Процесс обновления, однако, по-прежнему будет работать с таблицей Sales by Product и будет происходить так, как описано ниже.
Первоначально все строки имеют версию равную 1 и доступны для пользователя.
OrderDate | ProductID | Amount | Quantity | Version |
1996-11-07 00:00:00.000 | 1 | 216.0000 | 15 | 1 |
1996-11-14 00:00:00.000 | 1 | 172.8000 | 12 | 1 |
1996-12-03 00:00:00.000 | 1 | 216.0000 | 15 | 1 |
SELECT * FROM [Report by Product] |
OrderDate | ProductID | Amount | Quantity |
1996-11-07 00:00:00.000 | 1 | 216.0000 | 15 |
1996-11-14 00:00:00.000 | 1 | 172.8000 | 12 |
1996-12-03 00:00:00.000 | 1 | 216.0000 | 15 |
INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity, Version) SELECT Orders.OrderDate, [Order Details].ProductID, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, SUM([Order Details].Quantity) AS Quantity, 2 as Version FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate GROUP BY Orders.OrderDate, [Order Details].ProductID |
OrderDate | ProductID | Amount | Quantity | Version |
1996-11-07 00:00:00.000 | 1 | 216.0000 | 15 | 1 |
1996-11-14 00:00:00.000 | 1 | 172.8000 | 12 | 1 |
1996-12-03 00:00:00.000 | 1 | 216.0000 | 15 | 1 |
1996-11-07 00:00:00.000 | 1 | 300.0000 | 20 | 2 |
1996-11-14 00:00:00.000 | 2 | 172.8000 | 12 | 2 |
1996-12-03 00:00:00.000 | 2 | 216.0000 | 15 | 2 |
OrderDate | ProductID | Amount | Quantity |
1996-11-07 00:00:00.000 | 1 | 216.0000 | 15 |
1996-11-14 00:00:00.000 | 1 | 172.8000 | 12 |
1996-12-03 00:00:00.000 | 1 | 216.0000 | 15 |
UPDATE [Sales by Product] SET Version = CASE WHEN Version = 2 THEN 1 ELSE 3 END FROM [Sales by Product], DatesForUpdates WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate |
OrderDate | ProductID | Amount | Quantity | Version |
1996-11-07 00:00:00.000 | 1 | 216.0000 | 15 | 3 |
1996-11-14 00:00:00.000 | 1 | 172.8000 | 12 | 3 |
1996-12-03 00:00:00.000 | 1 | 216.0000 | 15 | 3 |
1996-11-07 00:00:00.000 | 1 | 300.0000 | 20 | 1 |
1996-11-14 00:00:00.000 | 2 | 172.8000 | 12 | 1 |
1996-12-03 00:00:00.000 | 2 | 216.0000 | 15 | 1 |
OrderDate | ProductID | Amount | Quantity |
1996-11-07 00:00:00.000 | 1 | 300.0000 | 20 |
1996-11-14 00:00:00.000 | 2 | 172.8000 | 12 |
1996-12-03 00:00:00.000 | 2 | 216.0000 | 15 |
DELETE FROM [Sales by Product] WHERE Version = 3 DELETE FROM DatesForUpdates |
Итак, весь процесс обновления выглядит следующим образом –
-- Вставка новых данных с номером версии строк 2 INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity, Version) SELECT Orders.OrderDate, [Order Details].ProductID, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, SUM([Order Details].Quantity) AS Quantity, 2 as Version FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate GROUP BY Orders.OrderDate, [Order Details].ProductID -- Обновление версий строк: 2 –> 1, 1 -> 3 UPDATE [Sales by Product] SET Version = CASE WHEN Version = 2 THEN 1 ELSE 3 END FROM [Sales by Product], DatesForUpdates WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate -- Удаление старых данных с номером версии 3 DELETE FROM [Sales by Product] WHERE Version = 3 -- Очистка DatesForUpdates DELETE FROM DatesForUpdates |
Заключение
Данный простой механизм был использован в ряде промышленных разработок и зарекомендовал себя.
Я с радостью рассмотрю любые комментарии и вопросы по данной тематике.
Мои координаты доступны на сайте www.msmirnov.ru
Михаил Смирнов
Руководитель проектов.