Моя память меня подводит. У меня есть простая таблица журнала аудита на основе триггера:
IDint (identity, PK)
CustomerID < /kbd>int
Имяvarchar(255)
Адресvarchar (255)
AuditDateTimedatetime
AuditCodechar(1)
Он содержит следующие данные:
IDCustomerIDИмя АдресAuditDateTimeAuditCode 1123Боб123 Internet Way2009-07-17 13:18:06.353I 2123< kbd>Боб123 Internet Way2009-07-17 13:19:02.117D 3 123Джерри123 Internet Way2009-07-17 13:36:03.517Я 4123Боб123 Мой отредактированный путь kbd>2009-07-17 13:36:08.050U 5100Арнольд100 SkyNet Way2009-07-17 13:36:18.607Я 6100 kbd>Ники100 Star Way2009-07-17 13:36:25.920U 7 < /kbd>110Блонди110 Другой путь2009-07-17 13:36:42.313Я 8113Салли113 Еще один способ2009-07-17 13:36 :57.627I
Каким должен быть эффективный оператор select для получения всех самых последних записей между временем начала и временем окончания? К вашему сведению: I для вставки, D для удаления и U для обновления.
Я что-то упустил в аудите? Таблица? Мой следующий шаг — создать таблицу аудита, которая записывает только изменения, но вы можете извлечь самые последние записи за заданный период времени. На всю жизнь я не могу легко найти его в любой поисковой системе. Ссылки тоже будут работать. Спасибо за помощь.
Стандарт SQL выбирает текущие записи из вопроса журнала аудита
Ответы (4)
Другой (лучший?) способ сохранить историю аудита — использовать столбцы «startDate» и «endDate», а не столбцы «auditDateTime» и «AuditCode». Это часто используется при отслеживании изменений типа 2 (новых версий строки) в хранилищах данных.
Это позволяет более непосредственно выбирать текущие строки (WHERE endDate равно NULL), и вам не нужно будет обрабатывать обновления иначе, чем вставки или удаления. У вас просто есть три случая:
- Вставка: скопируйте всю строку вместе с датой начала и датой окончания NULL.
- Удалить: установить дату окончания существующей текущей строки (endDate равно NULL)
- Обновление: выполните Удалить, затем Вставьте
Ваш выбор будет просто:
select * from AuditTable where endDate is NULL
Во всяком случае, вот мой запрос для вашей существующей схемы:
declare @from datetime
declare @to datetime
select b.* from (
select
customerId
max(auditdatetime) 'auditDateTime'
from
AuditTable
where
auditcode in ('I', 'U')
and auditdatetime between @from and @to
group by customerId
having
/* rely on "current" being defined as INSERTS > DELETES */
sum(case when auditcode = 'I' then 1 else 0 end) >
sum(case when auditcode = 'D' then 1 else 0 end)
) a
cross apply(
select top 1 customerId, name, address, auditdateTime
from AuditTable
where auditdatetime = a.auditdatetime and customerId = a.customerId
) b
Ссылки
шпаргалка для хранилищ данных , но есть хороший раздел об изменениях типа 2 (что вы хотите отслеживать)
Страница MSDN, посвященная хранилищу данных
Хорошо, пара вещей для таблиц журнала аудита.
Для большинства приложений мы хотим, чтобы таблицы аудита вставлялись очень быстро.
Если журнал аудита действительно предназначен для диагностики или по очень нерегулярным причинам аудита, то самый быстрый критерий вставки — сделать таблицу физически упорядоченной во время вставки.
И это означает, что время аудита должно быть указано в первом столбце кластеризованного индекса, например.
create unique clustered index idx_mytable on mytable(AuditDateTime, ID)
Это позволит выполнять чрезвычайно эффективные запросы на выборку при вставках AuditDateTime O(log n) и O(1).
Если вы хотите просмотреть свою таблицу аудита на основе идентификатора клиента, вам придется пойти на компромисс.
Вы можете добавить некластеризованный индекс по (CustomerID, AuditDateTime), что позволит O (log n) искать историю аудита для каждого клиента, однако стоимость обслуживания этого некластеризованного индекса после вставки — это обслуживание будет O ( log n) наоборот.
Однако этот штраф за время вставки может быть предпочтительнее сканирования таблицы (то есть O (n) времени сложности), которое вам придется заплатить, если у вас нет индекса для CustomerID, и это обычный запрос, который выполняется. Поиск O(n), который блокирует таблицу для процесса записи для нерегулярного запроса, может блокировать писатели, поэтому иногда в интересах писателей быть немного медленнее, если это гарантирует, что читатели не будут блокировать свои коммиты, потому что читателям нужно сканировать таблицу из-за отсутствия хорошего индекса для их поддержки....
Дополнение: если вы хотите ограничиться заданным таймфреймом, самое главное, прежде всего, это индекс по AuditDateTime. И сделайте его кластеризованным, когда вы вставляете в порядке AuditDateTime. Это самое большое, что вы можете сделать, чтобы сделать ваш запрос эффективным с самого начала.
Затем, если вы ищете самое последнее обновление для всех CustomerID в течение заданного промежутка времени, после этого требуется полное сканирование данных, ограниченное датой вставки.
Вам нужно будет выполнить подзапрос к вашей таблице аудита между диапазоном,
select CustomerID, max(AuditDateTime) MaxAuditDateTime
from AuditTrail
where AuditDateTime >= @begin and Audit DateTime <= @end
а затем включите это в свой собственный запрос выбора, например.
select AuditTrail.* from AuditTrail
inner join
(select CustomerID, max(AuditDateTime) MaxAuditDateTime
from AuditTrail
where AuditDateTime >= @begin and Audit DateTime <= @end
) filtration
on filtration.CustomerID = AuditTrail.CustomerID and
filtration.AuditDateTime = AuditTrail.AuditDateTime
Другой подход заключается в использовании подвыборки
select a.ID
, a.CustomerID
, a.Name
, a.Address
, a.AuditDateTime
, a.AuditCode
from myauditlogtable a,
(select s.id as maxid,max(s.AuditDateTime)
from myauditlogtable as s
group by maxid)
as subq
where subq.maxid=a.id;
время начала и окончания? например, между 1:00 и 3:00
или временем начала и окончания? например, с 17.07.2009 13:36 по 18.07.2009 13:36