TSQL DateTime в DateKey Int

В статье Увеличение масштаба хранилища данных с помощью SQL Server 2008 R2 автор рекомендует использовать целочисленный ключ даты в формате ГГГГММДД в ​​качестве кластеризованного индекса для ваших таблиц фактов, чтобы оптимизировать скорость запросов.

Каков наилучший способ преобразовать поле ключевой даты в ключ даты? Я чувствую, что следующее будет работать, но немного небрежно:

select Replace(CONVERT(varchar,GETDATE(),102),'.','')

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

Во-первых, как бы вы предложили сделать это преобразование? Приемлема ли моя идея?

Во-вторых, добился ли кто-нибудь большого успеха в использовании ключа даты в качестве кластеризованного индекса?


data-warehouse tsql sql-server-2008-r2
person jreed350z    schedule 10.04.2012    source источник

Ответы (3)


arrow_upward
10
arrow_downward

ISO long (112) поможет:

SELECT CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))

Приведение getdate() прямо к int с ISO 112 по какой-то причине дает 41008, но переход через VARCHAR, кажется, работает - я обновлю, если придумаю более быстрое приведение.

РЕДАКТИРОВАТЬ: что касается дебатов о int only vs varchar, вот мои выводы (повторяемые на моей тестовой установке и производственном сервере). Метод Varchar использует меньше процессорного времени для полумиллиона бросков, но в целом немного медленнее - незначительно, если вы не имеете дело с миллиардами строк

РЕДАКТИРОВАТЬ 2: исправлен тестовый пример для очистки кеша и разных дат.

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date])
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 255ms.    
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 602ms
person HeavenCore    schedule 10.04.2012
comment
преобразование getdate() прямо в INT с IS 112 дает 41008, потому что это количество дней с даты 0. Вы можете проверить, сравнив этот результат с результатом DATEDIFF между 0 и GETDATE(). SELECT CONVERT(INT, GETDATE(), 112), DATEDIFF(day,0,GETDATE()) - person Adam Porad; 10.04.2012
comment
@AdamPorad +1 Спасибо за разъяснение этого - person HeavenCore; 10.04.2012
comment
Я думаю, что ваш тест ошибочен. Разница в прошедшем времени является результатом отображения длинного списка значений на экране. Кроме того, вы используете GetDate(), поэтому SQL Server распознает его как константу и фактически не выполняет вычисление для каждой строки. Вам нужно использовать столбец из фактической таблицы. Наконец, чтобы точно протестировать производительность, вам нужно удалить чистые буферы и освободить кеш процедур. Вы не должны делать это на рабочем сервере. - person George Mastros; 10.04.2012
comment
@GMastros Хорошие моменты, я пересмотрел свой тестовый код, разница все еще кажется очень небольшой (varchar, возможно, на 40 мс или около того медленнее для 500000 строк, прошедшее время - это не количество времени, необходимое для отображения длинного списка, что на самом деле занимает 7+ секунд. - person HeavenCore; 10.04.2012
comment
@HeavenCore Возможно, вы правы насчет прошедшего времени. Обычно я объявляю переменную и присваиваю ей результаты. Пример: объявить корзину Int; Выберите Корзина = Конвертировать (целое число, ....). Я согласен, что разница во времени очень мала в этом случае. - person George Mastros; 10.04.2012

arrow_upward
4
arrow_downward

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

Select Year(GetDate()) * 10000 + Month(GetDate()) * 100 + Day(GetDate())

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

person George Mastros    schedule 10.04.2012

arrow_upward
4
arrow_downward

Вместо создания DateKey в формате ГГГГММДД вы можете использовать DATEDIFF. чтобы получить количество дней между 0 (т.е. «дата, представленная 0») и датой, для которой вы создаете DateKey.

SELECT DATEDIFF(day,0,GETDATE())

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

SELECT DATEADD(day, 41007, 0)

(Примечание: 41007 – это результат функции DATEDIFF, описанной выше, когда я запускал ее 10 апреля 2012 г.)

person Adam Porad    schedule 10.04.2012