Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 конвертации даты в число (быстрая)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4683
Господа, кто-то знает более быстрые способы перевода даты (DATE/DATETIME) в число, кроме вот этих ? 30 дек 2018 (DATE/DATETIME) -> 20181230 (INT)

интересует скорость в SELECT и WHERE


SELECT TOP 3000000 YEAR(a.create_date)*10000 + MONTH(a.create_date)*100 + DAY(a.create_date) D
INTO #T32
FROM sys.objects a, sys.objects b, sys.objects c    

SELECT TOP 3000000 CAST(CONVERT(CHAR(8), a.create_date, 112) AS INT) D
INTO #T22
FROM sys.objects a, sys.objects b, sys.objects c    
15 ноя 19, 13:45    [22017147]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
a_voronin
(DATE/DATETIME) -> (INT)


Я, конечно, понимаю что легаси и все такое, но


int - 4 байта
date - 3 байта
15 ноя 19, 13:49    [22017151]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
msLex, Угу, легаси...

SELECT sh.next_run_date, t.name
FROM msdb.dbo.sysjobschedules sh
        INNER JOIN msdb.sys.tables tbl ON tbl.name = 'sysjobschedules'
        INNER JOIN msdb.sys.columns c ON c.object_id = tbl.object_id AND c.name = 'next_run_date'
        INNER JOIN msdb.sys.types t ON c.system_type_id = t.system_type_id
15 ноя 19, 13:56    [22017164]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4683
msLex
a_voronin
(DATE/DATETIME) -> (INT)


Я, конечно, понимаю что легаси и все такое, но


int - 4 байта
date - 3 байта


Не все легаси -- в хранилищах есть рекомендация использовать INT вместо дат. К тому же DATETIME больше INT

И DATE_INT / 10000 -- получили год и т.п.
15 ноя 19, 14:10    [22017208]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
a_voronin
в хранилищах есть рекомендация использовать INT вместо дат

Хмм, можно ссылку на современные рекомендации?


a_voronin
К тому же DATETIME больше INT

Ага, только 20190101000000 (это только до секунды) уже в INT не влезет, нужно BIGINT, что 8 байт.
При этом datetime2, с точностью до 3 знаков после секунды, занимает 6 байт



a_voronin
И DATE_INT / 10000 -- получили год и т.п.

т.е. вот такой "костыль", это нормально, а year(date_date) нет.

Сообщение было отредактировано: 15 ноя 19, 14:26
15 ноя 19, 14:21    [22017237]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
Minamoto
msLex, Угу, легаси...

SELECT sh.next_run_date, t.name
FROM msdb.dbo.sysjobschedules sh
        INNER JOIN msdb.sys.tables tbl ON tbl.name = 'sysjobschedules'
        INNER JOIN msdb.sys.columns c ON c.object_id = tbl.object_id AND c.name = 'next_run_date'
        INNER JOIN msdb.sys.types t ON c.system_type_id = t.system_type_id


Это вы так не легаси пытаетесь показать?
Да это еще с SQL 2000 (а может и с 7.0) так хранится.

Сообщение было отредактировано: 15 ноя 19, 14:24
15 ноя 19, 14:24    [22017246]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
msLex

Это вы так не легаси пытаетесь показать?
Да это еще с SQL 2000 (а может и с 7.0) так хранится.

Это был не сарказм, а намек на то, что легаси, еще и введенный самим Майкрософтом, так что иногда приходится с этим иметь дело, даже если очень не хочется.
15 ноя 19, 14:26    [22017252]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
Minamoto
msLex

Это вы так не легаси пытаетесь показать?
Да это еще с SQL 2000 (а может и с 7.0) так хранится.

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

Ок.
Но это не делает чести "парням из Редмонда"
Вообще, структура хранение информации о джобах в msdb не та вещь, которой Майкрософт может гордиться (ну это уже совсем оффтоп).

Сообщение было отредактировано: 15 ноя 19, 14:29
15 ноя 19, 14:29    [22017255]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
msLex
a_voronin
(DATE/DATETIME) -> (INT)


Я, конечно, понимаю что легаси и все такое, но


int - 4 байта
date - 3 байта
Каждый бит на счету!
Может, DATEDIFF(DAY,0,a.create_date)?
15 ноя 19, 14:34    [22017269]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
iap
Каждый бит на счету! Картинка с другого сайта.


Дело-то в том, что искусственно увеличивают размер хранимых данных, при это выполняя дополнительные преобразования, с перфомансом которых потом борются.
15 ноя 19, 14:44    [22017289]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4683
msLex
iap
Каждый бит на счету! Картинка с другого сайта.


Дело-то в том, что искусственно увеличивают размер хранимых данных, при это выполняя дополнительные преобразования, с перфомансом которых потом борются.


А вы уверены, что правы на 100%? Например, если взять колумнстор, если взять ключи измерений в SSAS. 3 байта надо конвертировать в 8 на 64 битных средах. А если надо считать кол-во уникальных дней?
15 ноя 19, 14:57    [22017307]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4683
[quot msLex#22017237]
a_voronin
в хранилищах есть рекомендация использовать INT вместо дат

Хмм, можно ссылку на современные рекомендации?


наиболее распространена ситуация, когда DATETIME типа ORDER_DATE, превращают в INT 4 байта.

Можете почитать вот это

https://dwbi1.wordpress.com/2009/12/16/date-dimension-in-analysis-services-part-2/

И вот это

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/
15 ноя 19, 15:02    [22017320]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
a_voronin
А вы уверены, что правы на 100%?

Нет, поэтому я и просил ссылки на современные рекомендации.

a_voronin
Например, если взять колумнстор

Тут не сложно проверить date vs int
Провел небольшой тест на 100 млн записей с датой +/- 1 год от текущих данных.

date занимает меньше чем int



a_voronin
А если надо считать кол-во уникальных дней?

И чем здесь выигрывает int у date?
15 ноя 19, 15:23    [22017367]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
msLex
Member

Откуда:
Сообщений: 7998
a_voronin
msLex
a_voronin
в хранилищах есть рекомендация использовать INT вместо дат

Хмм, можно ссылку на современные рекомендации?


наиболее распространена ситуация, когда DATETIME типа ORDER_DATE, превращают в INT 4 байта.

Можете почитать вот это

https://dwbi1.wordpress.com/2009/12/16/date-dimension-in-analysis-services-part-2/

И вот это

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/



Спасибо, но
Во-первых, я просил свежие (2008 и 2009 год это совсем не то)
Во-вторых, почему вы сравниваете datetime и int? Я предлагал date.

Сообщение было отредактировано: 15 ноя 19, 15:28
15 ноя 19, 15:27    [22017373]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-lifecycle-toolkit/

По сслыке книга 2008 года вот с таким вот вступлением:
The world of data warehousing and business intelligence has changed remarkably since the first edition of The Data Warehouse Lifecycle Toolkit was published in 1998. Ralph Kimball and the Kimball Group refined the original set of lifecycle methods and techniques.

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

И давайте уже не будем СТОЛБЦЫ_ВОТ_ТАК_НАЗЫВАТЬ?
[И] [вот] [так] [квадратные] [скобочки] [ставить]? Картинка с другого сайта.
15 ноя 19, 17:38    [22017641]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4683
Владимир Затуливетер,

Вы упорно упускаете главный момент. Фильтрация таблицы фактов по ключу даты не главная операция, которая происходит в DWH. Таблица фактов часто используется ОЛАП кубами, где есть календари основанные на INT. Также у меня есть сомнения, что вы делал OLAP кубы и знаете нюансы использование полей различных типов там.


Также данные могут выгружаться в другие виды БД, например ORACLE или Vertica и там другие расклады по месту с скорости фильтрации.

https://stackoverflow.com/questions/44934126/why-is-datekey-in-fact-tables-always-int

https://www.codeproject.com/Articles/1060016/Creating-Date-dimension-from-scratch-in-Microsoft
18 ноя 19, 08:44    [22018435]     Ответить | Цитировать Сообщить модератору
 Re: конвертации даты в число (быстрая)  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
a_voronin,

Кубы я не делаю, давно игрался, поэтому тонкостей не знаю.
Знающие люди пусть ответят подтвердят что с типом Date какие-то проблемы в OLAP для sql server >= 2014 (в чем я сильно сомневаюсь, инфы такой в интернетах не нашел). И попутно вопрос, а во время выгрузки в olap нельзя преобразовать дату в int если это так важно. На крайний случай нельзя создать вычисляемы столбец DateInt на таблице и использовать его?

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

А то что касается преимуществ использования date в место int то вот они:

1. Date занимает меньше места 3 байта, для int это 4 байта.
2. Получение данных из фактов простыми запросами без джойна дименшена Date. Оптимизатору намного проще получить выполнить запрос select * from dbo.SomeFactTable where Date between '20190101' and '20190201' чем запрос с джойном.
3. Нет проблем с функциями которые работают с датами.
4. Не надо писать уродские запросы, где мы преобразовываем int в дату
5. Пользователю более понятна дата, а не int в котором дата
6. Невозможно вставить не валидную дату, меньше потенциальных ошибок.
18 ноя 19, 17:12    [22018999]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить