Эффективность: МЕСЯЦ() против DATEDIFF()

У меня есть две таблицы с датами, к которым я хочу присоединиться с помощью ВНУТРЕННЕГО СОЕДИНЕНИЯ. Эти таблицы связаны друг с другом с помощью FK, который гарантирует, что запись в Табле A и связанная с ней запись в Таблице B относятся к одному и тому же году.

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

При таких предположениях - что было бы эффективнее?

    SELECT .... 
    FROM DatesA da 
    INNER JOIN DatesB db 
    ON MONTH(da.Date) = MONTH(db.Date) 
    AND [Rest of the join]

    SELECT .... 
    FROM DatesA da 
    INNER JOIN DatesB db 
    ON DATEDIFF(MM, da.Date, db.Date) = 0 
    AND [Rest of the join]

Спасибо!


sql-server performance datediff datepart
person noamyg    schedule 16.02.2017    source источник
comment
Только в том же месяце? А в том же году? Этот первый SELECT мог дать странные результаты при работе с датами более чем за один год.   -  person R. Richards    schedule 16.02.2017
comment
[Остальная часть соединения] имеет дело с другой таблицей, которая представляет годы и другие данные. Так что я знаю, что мне не нужно сравнивать годы, только месяцы.   -  person noamyg    schedule 16.02.2017
comment
@user3367818 user3367818 Тогда вам следует сравнить общую производительность MONTH() AND [some of the rest of the join, such as YEAR()] с производительностью DATEDIFF().   -  person GSerg    schedule 16.02.2017
comment
Что же касается собственно вопроса, то вы можете дать ответ только сами, сравнив планы выполнения. Я бы ожидал, что один datediff() будет быстрее и удобнее, не более того.   -  person GSerg    schedule 16.02.2017
comment
NB: select datediff(MM, '2015-07-01', '2017-07-01') MonthsBetween возвращает 24; не 0. Таким образом, эта проверка будет проверять, совпадают ли ваши даты с одним и тем же месяцем и одним и тем же годом.   -  person JohnLBevan    schedule 16.02.2017
comment
@JohnLBevan, пожалуйста, прочитайте еще раз ... В моем случае это не имеет значения.   -  person noamyg    schedule 16.02.2017
comment
Прохладный; просто проверка явно; поскольку это означает, что вы действительно сравниваете datediff(MM,da.date,db.date)=0 с month(da.date) = month(db.date) and year(da.date) = year(db.date); т. е. не только с выполнением одной только функции month.   -  person JohnLBevan    schedule 16.02.2017
comment
Не совсем. У меня нет YEAR() в моем запросе, у меня есть соединение FK с родительской записью, которая представляет год (среди прочего). Я не могу пропустить это объединение и заменить его только DATEDIFF().   -  person noamyg    schedule 16.02.2017
comment
Интересный вопрос, поэтому я провел тест на своих данных. Моя первоначальная мысль заключалась в том, что расчет 1 datediff будет быстрее, чем вызовы за 2 месяца. НЕ НАУЧНО, но в среднем по 6 запускам, month() был на 20 мс быстрее и постоянно ниже, чем метод datediff   -  person John Cappelletti    schedule 16.02.2017
comment
Пс. Хотя я не могу ответить на ваш вопрос о datediff и month; Я могу добавить другой вариант; вместо month попробуйте datepart(month,...): dba.stackexchange.com/questions/17956/   -  person JohnLBevan    schedule 16.02.2017
comment
@GSerg, как datediff() более поддается анализу?   -  person Horaciux    schedule 16.02.2017
comment
@Horaciux По моему опыту, datediff() прекрасно использует индексы. Глядя на различные сообщения здесь о том, что это не подлежит обсуждению, я бы предположил, что это может быть как-то связано с тем фактом, что обычно я использую его как where datediff(d, date1, date2) = 0, и эта комбинация (d, = 0) подлежит обсуждению. Или, может быть, это просто часть = 0.   -  person GSerg    schedule 16.02.2017
comment
Я думаю, что наиболее эффективным методом было бы добавление сохраняемого вычисляемого столбца с индексом для каждой таблицы. msdn.microsoft.com/en-us/library/ms189292.aspx   -  person George Mastros    schedule 16.02.2017
comment
@GMastros, не могли бы вы взглянуть на предложенный мной подход? Спасибо   -  person Horaciux    schedule 17.02.2017

Ответы (3)


arrow_upward
1
arrow_downward

Сравнение эффективности

Для меня при тестировании набора данных из 2508 записей с датами, равномерно распределенными по одному году, и присоединения таблицы к самой себе, datepart работал значительно лучше, чем datediff (разница между datepart и month была незначительной, хотя datepart обычно ~1ms быстрее. Этот тест был выполнен SQL 2008 R2 (SP3). Полный код представлен ниже:

--prep
create table #testDates (d date)

insert #testDates
select dateadd(dd,row_number() over (partition by 1 order by number) % 365,'2017-01-01')
from master.dbo.spt_values a --, master.dbo.spt_values --uncomment this for a larger test set

select @@VERSION --Microsoft SQL Server 2008 R2 (SP3) - 10.50.6529.0 (X64) 
go


--test statements
set statistics time on
select count(1) --return 1 so we're measuring query time; not the time to return the results
from #testDates a 
inner join #testDates b 
on month(a.d) = month(b.d)
set statistics time off

set statistics time on
select count(1) 
from #testDates a 
inner join #testDates b 
on datepart(month,a.d) = datepart(month,b.d)
set statistics time off

set statistics time on
select count(1) 
from #testDates a 
inner join #testDates b 
on datediff(MM,a.d,b.d) = 0
set statistics time off

--cleanup
go
drop table #testDates

Время было: 5ms, 4ms, 3432ms соответственно.

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

Как насчет проиндексированных данных?

Добавление индекса после заполнения данными улучшило производительность datediff; правда только до 3390ms; по-прежнему оставляя его далеко позади других.

create index ix_testDates_d on #testDates(d) --create the index after populating the data to ensure it's not fragmented

Другое

  • Еще одна причина использовать datepart/month вместо datediff — это лучший самодокументирующийся код; то есть это показывает, что вы ищете даты с одним и тем же месяцем; а не даты, где количество месяцев между ними равно 0 (что то же самое (не считая лет); но последнее требует на несколько секунд больше для когнитивной обработки.

  • Причина использования datepart вместо month заключается в том, что datepart соответствует стандарту ANSI.

  • Однако month имеет преимущество перед datepart в том, что является детерминированной функцией (ссылка: https://stackoverflow.com/a/14851564/361842), чего datepart почему-то нет!

  • Также month более интуитивно понятен; то есть сознательно быстрее для понимания людьми.

  • Выбор между datepart и month, учитывая незначительную разницу в производительности, должен зависеть от других ваших требований и/или стандартов кодирования.

person JohnLBevan    schedule 16.02.2017
comment
Привет, не могли бы вы рассмотреть предложенный мной подход? Спасибо. - person Horaciux; 17.02.2017

arrow_upward
1
arrow_downward

РЕДАКТИРОВАТЬ - похоже, что подход DateDiff может использовать индексы, поскольку это не скалярная функция, обертывающая значение. Быстрое сравнение в моей среде с тестовыми данными говорит о том, что DateDiff будет в несколько раз эффективнее.

person Jeffrey Van Laethem    schedule 16.02.2017
comment
Забавно, в моем тесте я получил обратное ... к моему удивлению - person John Cappelletti; 16.02.2017
comment
очень странно... Я собираюсь запросить гораздо больший набор данных и посмотреть, что я получу. - person Jeffrey Van Laethem; 16.02.2017
comment
Я также проверил это, и мне показалось, что МЕСЯЦ был быстрее; Я задал вопрос, потому что он показался мне нелогичным. Я обычно делаю DATEDIFF по умолчанию и боюсь, что на результаты повлияет инфраструктура (запустил ее в производственной среде). - person noamyg; 16.02.2017
comment
поэтому данные, которые я использую, имеют некластеризованный индекс в столбце даты и включают в себя другие столбцы, которые я возвращаю... Я думаю, что единственный способ узнать наверняка - это посмотреть предполагаемые/фактические планы выполнения в вашей среде и посмотрите, какие индексы могут принести пользу каждому. - person Jeffrey Van Laethem; 16.02.2017
comment
К вашему сведению: я включил некоторую информацию о том, как я тестирую, так как я нашел datepart самым быстрым: stackoverflow.com/a/42281644 /361842. - person JohnLBevan; 16.02.2017
comment
Интуитивно я подозреваю, что datepart был быстрее, поскольку он выбирает значение месяца, а затем выполняет целочисленное сравнение, а не вычисления, связанные с преобразованием дат в месяцы с 1900 года, затем вычитание, затем удаление знака (или эквивалентные шаги; не уверен, что происходит под поверхностью, но месяцы между двумя датами будут более сложными для вычисления SQL, чем миллисекунды между датами). - person JohnLBevan; 16.02.2017

arrow_upward
1
arrow_downward

Я основываю свой ответ на предыдущем ответе @JohnLBevan

Это всего 1 мс. Это удобное решение, в котором используются индексы в столбце даты.

«Хитрость» заключается в том, чтобы заранее иметь своего рода календарную таблицу (которую я создаю «на лету») с первым и последним днем ​​каждого месяца.

create table #testDates (d date)

insert #testDates
select dateadd(dd,row_number() over (partition by 1 order by number) % 365,'2017-01-01')
from master.dbo.spt_values a --, master.dbo.spt_values --uncomment this for a larger test set

select @@VERSION --Microsoft SQL Server 2008 R2 (SP3) - 10.50.6529.0 (X64) 
go


create index ix_testDates_d on #testDates(d) 

--test statements
set statistics time on
select count(1) --return 1 so we're measuring query time; not the time to return the results
from #testDates a 
inner join #testDates b 
on month(a.d) = month(b.d)
set statistics time off

select min(d) iniDay,max(d) endDay into #months from #testDates
group by month(d)


set statistics time on
select count(1) --return 1 so we're measuring query time; not the time to return the results
from #testDates a 
inner join #months m
on a.d>= m.iniDay and a.d<=m.endDay
inner join #testDates b 
 on b.d>= m.iniDay and b.d<=m.endDay
set statistics time off


--cleanup
go
drop table #testDates 
drop table #months

время было 4 мс, 10 мс для таблицы календаря, 1 мс.

Для 150 000 строк

(150000 row(s) affected)

(1 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 4 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 141 ms,  elapsed time = 130 ms.

(12 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 14 ms, elapsed time = 14 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 48 ms.
person Horaciux    schedule 17.02.2017
comment
Хорошее решение. Однако вы должны включить #months во время вашего запроса, если его нужно запускать каждый раз, чтобы запрос работал. Тем не менее, если все даты находятся в пределах известного диапазона, и вы можете предварительно заполнить постоянную таблицу всеми месяцами, вы получите преимущество без накладных расходов. - person JohnLBevan; 17.02.2017