Каков наилучший способ хранения исторических данных в SQL Server 2005/2008?

Моим упрощенным и надуманным примером является следующее: -

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

Это довольно простая проблема для решения, но я ищу лучшее решение.

2 основных варианта, о которых я могу думать, следующие: -

Вариант 1 - В той же таблице хранятся текущие и исторические записи

Сохраните все текущие и архивные записи в одной таблице.

то есть.

CREATE TABLE [dbo].[WeatherMeasurement](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

Это будет держать все просто, но что будет самым эффективным запросом для получения списка городов и текущей температуры? Будет ли эта шкала после того, как таблица будет иметь миллионы строк? Есть ли что-нибудь, что можно получить, если в таблице есть какой-то флаг IsCurrent?

Вариант 2 - Хранить все архивные записи в отдельной таблице

Там будет таблица для хранения текущих текущих измерений в

CREATE TABLE [dbo].[WeatherMeasurement](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

И таблица для хранения исторической архивной даты (возможно, вставлена ​​с помощью триггера)

CREATE TABLE [dbo].[WeatherMeasurementHistory](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

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

Какой из лучших вариантов? Есть ли лучшие варианты, о которых я не упоминал?

ПРИМЕЧАНИЕ. Я упростил схему, чтобы лучше сфокусировать мой вопрос, но предположим, что в день будет много вставленных данных (100 000 записей), а данные будут текущими в течение одного дня. Текущие данные также могут быть запрошены как исторические.

Ответы

Ответ 1

он ЗАВИСИТ ОТ шаблонов использования приложений... Если шаблоны использования указывают, что исторические данные будут запрашиваться чаще, чем текущие значения, тогда поместите их все в одну таблицу... Но если исторические запросы являются исключением, ( или менее 10% запросов), а производительность более распространенного запроса текущих значений будет связана с помещением всех данных в одну таблицу, тогда имеет смысл разделить эти данные на свою собственную таблицу...

Ответ 2

Я бы сохранил данные в одной таблице, если у вас нет очень серьезного смещения для текущих данных (в использовании) или исторических данных (по объему). Компонентный индекс с DATE + TOWNID (в этом порядке) в большинстве случаев устранит проблему с производительностью (хотя, очевидно, в настоящее время у нас нет данных, чтобы быть уверенным в этом).

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

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

Я бы порекомендовал читать подсказки для использования индекса и "охватывать индексы" для получения дополнительной информации о проблемах с производительностью.

Ответ 3

Ваша таблица очень узкая и, вероятно, будет работать в одной правильно проиндексированной таблице, которая никогда не опережала емкость SQL Server в традиционной нормированной OLTP-модели даже для миллионов и миллионов строк. Даже при использовании двухэлементных моделей преимущества могут быть уменьшены за счет использования разбиения таблиц в SQL Server. Поэтому не стоит рекомендовать его по модели с одной таблицей. Это будет сценарий Inmon-стиля или "Enterprise Data Warehouse".

В гораздо более крупных сценариях я буду регулярно передавать данные в хранилище данных (смоделированные с использованием модели размера в стиле Кимбалла) и просто чистить данные в реальном времени - в некоторых простых сценариях, подобных вашим, может быть эффективно < сильные > NO данные в реальном времени - все идет прямо на склад. Размерная модель имеет множество преимуществ при резке данных различными способами и хранении огромного количества фактов с различными размерами. Даже в сценарии хранилища данных часто таблицы фактов разделяются по дате.

Возможно, это не так, как у ваших данных (Town и Date - ваши единственные явные размеры), однако в большинстве хранилищ данных размеры могут быть заснежены или могут быть избыточными, поэтому были бы другие измерения в отношении факта, хранящегося на время загрузки вместо снежинки для большей эффективности - например, State, Zip Code, WasItRaining, IsStationUrban (изобретенный).

Это может показаться глупым, но когда вы начинаете добывать данные для получения результатов в хранилищах данных, это вызывает вопросы, например: в день с дождем в городских условиях, какова средняя температура в штате Мэн? - просто немного легче обойтись без объединения целого ряда таблиц (т.е. не требует большого опыта в вашей нормализованной модели и работает очень быстро). Как будто бесполезная статистика в бейсболе - но некоторые, по-видимому, оказываются полезными.

Ответ 4

Другой альтернативой может быть переход на одну таблицу для всех данных и просмотр текущей температуры. Это не поможет производительности, но может помочь в удобочитаемости/ремонтопригодности. Вы можете даже пойти на индексированное представление, чтобы повысить производительность, если у вас есть соответствующая версия sql.

Ответ 5

Я предлагаю сохранить в той же таблице, так как часто запрашиваются исторические данные. Если вы не добавите в таблицу больше столбцов.

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

Ответ 6

Я бы использовал одну таблицу с представлениями индекса, чтобы предоставить мне самую последнюю информацию. Сервер SQL 2005 и 2008 предназначен для хранилищ данных, поэтому он должен хорошо сформовать в этом состоянии.

Если у вас есть шаблон данных, который часто требует записи в db, лучшим вариантом будет иметь активную таблицу таблицы и архива, которую вы пакет обновляете с некоторым интервалом.

Ответ 7

Если вы храните все в одной таблице, как вы собираетесь создавать реляционную базу данных.

Пример:

ID -------------- GUID ---- ПК

record_id ------- GUID

каждый раз, когда будет вставлена ​​новая запись, [id] изменится, но [record_id] останется таким же. Теперь, если вам нужно связать его с таблицей адресов, как вы это сделаете?