Блог

Представьте, что у нас есть набор объектов, каждый из которых имеет свое состояние: свободно, занято или неработающее. Состояние указывается на день, например, сегодня 2011-05-17 объект E1 свободен, а завтра 2011-05-18 он занят.

Необходимо хранить ~ 10 ^ 5 объектов в течение 1000 дней. Какой лучший способ сделать это?

Я думаю о 2 вариантах:

• представлять каждый день в виде символа «0», «1» или «2» и сохранять для каждого объекта строку из 1000 символов
• сохраняйте каждый день состояние объекта в строке, т. е. 1000 строк для объекта

Наиболее важным запросом для таких данных является: заданная дата начала и дата окончания определяют, какие объекты свободны.

Производительность имеет более высокий приоритет, чем хранение.

Все предложения и комментарии приветствуются.

Лучший способ — сначала попробовать более простой и гибкий вариант (то есть хранить каждый день в отдельной строке) и разрабатывать сложный альтернативный метод только в том случае, если производительность неудовлетворительна. Избегайте преждевременной оптимизации.

10 ^ 8 строк — не такая уж большая проблема для вашей обычной базы данных на обычном сервере в настоящее время. Поместите индекс на дату, и я бы поспорил, что запросы диапазона («задана дата начала и дата окончания …») будут работать просто отлично.

Причины, по которым я утверждаю, что это проще и гибче, чем идея хранения строки из 1000 символов, заключаются в следующем:

• Вам придется обработать это в коде, и этот код будет не таким простым для понимания, как код, который запрашивает записи БД, содержащие дату и статус.
• В зависимости от компонента database Engine 1000 символьные строки могут быть большими двоичными объектами, которые хранятся вне записи. Это делает их менее эффективными.
• Что произойдет, если вам внезапно понадобится 2000 дней вместо 1000? Начать обновлять все строки и код, который их обрабатывает? Это намного больше работы, чем просто изменение вашего запроса.
• Что произойдет, когда вас в следующий раз попросят сохранить некоторую дополнительную информацию для ежедневной записи или потребуется изменить степень детализации (например, перейти от дней к часам)?

Создайте единую таблицу для хранения ваших данных. Создайте таблицу с идентификатором, датой, именем объекта и восемью логическими полями. SQL Server 2008 предоставил мне приведенный ниже код для таблицы:

 CREATE TABLE [dbo].[EntityAvailability](
[EA_Id] [int] IDENTITY(1,1) NOT NULL,
[EA_Date] [date] NOT NULL,
[EA_Entity] [nchar](10) NOT NULL,
[EA_IsAvailable] [bit] NOT NULL,
[EA_IsUnAvailable] [bit] NOT NULL,
[EA_IsBroken] [bit] NOT NULL,
[EA_IsLost] [bit] NOT NULL,
[EA_IsSpare1] [bit] NOT NULL,
[EA_IsSpare2] [bit] NOT NULL,
[EA_IsSpare3] [bit] NOT NULL,
[EA_IsActive] [bit] NOT NULL,
 CONSTRAINT [IX_EntityAvailability_Id] UNIQUE NONCLUSTERED 
(
    [EA_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]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EntityAvailability]') AND name = N'IXC_EntityAvailability_Date')
CREATE CLUSTERED INDEX [IXC_EntityAvailability_Date] ON [dbo].[EntityAvailability] 
(
    [EA_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
  

Кластеризованный индекс по дате будет наилучшим для поиска по диапазону. Никогда не разрешайте поиск без диапазона дат, и не будет необходимости в каком-либо индексе, отличном от кластеризованного индекса. Логические поля допускают восемь ситуаций, используя только один байт. Размер строки для этой таблицы составляет 35 байт. на странице поместится 230 строк. Вы заявили, что вам необходимо хранить 10 ^ 5 объектов в течение 1000 дней, что составляет 100 миллионов. Сто миллионов строк будут занимать 434 782 страницы 8K или около 3 гигабайт.

Установите таблицу на твердотельный накопитель, и все готово.

В зависимости от того, являются ли объекты чаще свободными или нет, просто храните даты, когда объект свободен или нет.

Предполагая, что вы сохраняете даты, когда объект не свободен, тогда поиск выполняется там, где начальная дата <= дата и конечная дата >= дата и любая строка, совпадающая, что означает, что объект не свободен на этот период

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

В целом я бы выбрал звездообразную схему Кимбалла (http://en.wikipedia.org/wiki/Star_schema ) введите структуру с тремя таблицами (изначально)

• Достоверность (FK kStatus, kDate)
• DimStatus (PK kStatus)
• DimDate (PK kDate)

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

Большим преимуществом этого дизайна является то, что он очень расширяемый; если вы хотите увеличить диапазон дат или увеличить количество допустимых состояний, его тривиально расширить.

Можно разумно добавить другие измерения, например DimEntity, которые могли бы содержать более богатую информацию, дающую категориальную информацию, которая может быть интересна для нарезки ваших объектов.

Значение DimDate обычно дополняется добавлением DayNo, MonthNo, YearNo, DayOfWeek, WeekendFlag, WeekdayFlag, PublicHolidayFlag. Это позволяет выполнять некоторые очень интересные анализы.

Как спрашивает @Elad, что произошло бы, если бы вы добавили информацию, основанную на времени, тогда это также может быть дополнено измерением DimTime, имеющим одну запись в час или минуту.

Прошу прощения за мое наименование, поскольку я не очень хорошо разбираюсь в ваших данных. Если бы у меня было больше времени, я мог бы придумать несколько лучших!

Чтобы получить свободные объекты на дату, вы можете попробовать:

 select
      e.EntityName
    , s.StateName
    , x.ValidFrom
from EntityState as x
join Entity      as e on e.EntityId = x.EntityId
join State       as s on s.StateID  = x.StateID
where StateName = 'free'
  and x.ValidFrom = ( select max(z.ValidFrom)
                      from EntityState as z
                      where z.EntityID   = x.EntityID
                        and z.ValidFrom <= your_date_here )
;
  

Примечание: Убедитесь, что вы сохраняете только изменения состояния в EntityState таблице.