Здесь я привожу текст статьи "Использование представлений для сокрытия промежуточных данных в Microsoft SQL Server", опубликованной в 359 номере рассылки MS SQL Server - дело тонкое 25 мая 2007г. http://www.sql.ru/subscribe/2007/359.shtml#20

Использование представлений для сокрытия промежуточных данных в 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

Однако, если в исходных таблицах Orders или Order Details будет находится больше количество записей (сотни миллионов), то выборка из такого view будет очень медленной. Быстро получить данные из него будет невозможно.

Возможным решением проблемы могла бы быть замена 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

Однако, в связи с тем, что в таблице Orders у нас находится очень много заказов, возникают две проблемы, которые не позволят нам обновлять эту таблицу в рабочее время:

Первая проблема: Очистка и обновление такой таблицы может происходить очень долго. В реальной практике расчет итоговых значений для такой таблицы может быть намного сложнее чем в данном примере. В нем могут участвовать множество таблиц с большим количеством записей в каждой, он может содержать сложные вычисления и т.п, так что он, например, может выполняться несколько часов.

Вторая проблема: В течении всего этого времени пользователи не смогу видеть данные о продажах, так как они были удалены первым запросом.

Описанный здесь механизм позволяет решить обе эти проблемы.

Описание механизма

Для решения первой проблемы нужно принять во внимание, что далеко не все строки таблицы Sales by Product нуждаются в обновлении. Заказы редко вписываются «задним числом», поэтому обычно в обновлении нуждается несколько последних дней и очень редко данные в далеком прошлом. Поэтому, для ускорения процесса расчета можно создать дополнительную вспомогательную таблицу, которая будет содержать даты, для которых требуется пересчет.

CREATE TABLE DatesForUpdates (
	UpdateDate datetime NOT NULL 
)

Такая таблица может заполняться при создании либо обновлении заказов. При наличии такой вспомогательной таблицы процесс обновления Sales by Product может выглядеть так:

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 – строка, содержащая устаревшие данные. Пользователю также не доступна

Во-вторых: создадим view

CREATE VIEW dbo.[Report by Product]
AS
SELECT     *
FROM        [Sales by Product]
WHERE     Version = 1

Именно с этим представлением Report by Product, а не с таблицей Sales by Product будет теперь работать пользователь. Видим, что это представление фильтрует строки со всеми номерами строк кроме 1, скрывая таким образом промежуточные данные.
Процесс обновления, однако, по-прежнему будет работать с таблицей Sales by Product и будет происходить так, как описано ниже.
Первоначально все строки имеют версию равную 1 и доступны для пользователя.

Соответственно запрос к представлению

SELECT *
FROM [Report by Product]

возвращает следующий результат:

После выполнения следующего запроса на вставку в таблицу

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

в таблице Sales by Product будут содержаться следующие данные:

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

Следующий запрос выполнит обновление номеров версий строк:

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

Это приведет к тому, что номера версий строк обновятся так:

Теперь старые данные стали недоступны через представление – на их место встали новые. Запрос к представлению возвращает следующий результат:

После этого мы можем произвести очистку старых данных

DELETE FROM [Sales by Product] WHERE Version = 3

DELETE FROM DatesForUpdates

После очистки таблица вновь остается в исходном состоянии – все строки имеют номер версии равный 1.

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

-- Вставка новых данных с номером версии строк 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

Видим, что первый запрос вставляет в таблицу данные с версией строк равной 2 (новая строка), благодаря чему эти новые данные пользователю не доступны. Сколько бы ни шел процесс расчета и вставки, пользователь не видит этих строк – он продолжает получать старые строки, которые были созданы в процессе предыдущего обновления и имеют значение поле Version равное 1. Второй запрос изменяет номера версий строк – новые данные становятся на место старых (2 исправляется на 1), а старые помечаются как готовые к удалению (версия 1 исправляется на 3). После выполнения этого запроса у пользователя мгновенно исчезают старые данные, а вместо них появляются новые. Затем строки с устаревшими данными (номер версии 3) удаляются. Благодаря такой схеме у пользователя ни в один момент времени не пропадают данные и не возникает ситуации при которой он видит одновременно и старые и новые данные.

Заключение

Данный простой механизм был использован в ряде промышленных разработок и зарекомендовал себя.
Я с радостью рассмотрю любые комментарии и вопросы по данной тематике.

Мои координаты доступны на сайте www.msmirnov.ru

Михаил Смирнов
Руководитель проектов.