Ответ 1
SQL Server 2008 и
В SQL Server 2008 и, конечно, самый быстрый способ - Convert(date, @date). При необходимости это можно вернуть к datetime или datetime2.
Что на самом деле лучше всего в SQL Server 2005 и старше?
Я видел противоречивые утверждения о том, что самое быстрое для сокращения времени с даты на SQL Server, а некоторые даже говорят, что они тестировали, но мой опыт был другим. Поэтому давайте сделаем еще более строгий тест, и пусть у всех есть script, поэтому, если я ошибаюсь, люди могут меня поправить.
Преобразования с плавающей точкой не являются точными
Во-первых, я бы держался подальше от преобразования datetime в float, потому что он неверно конвертируется. Вы можете уйти от выполнения операции удаления времени точно, но я думаю, что это плохая идея использовать ее, потому что она неявно сообщает разработчикам, что это безопасная операция, а она не. Посмотрите:
declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops
Это не то, чему мы должны учить людей в нашем коде или в наших примерах в Интернете.
Кроме того, это даже не самый быстрый способ!
Проверка эффективности -
Если вы хотите сами выполнить некоторые тесты, чтобы увидеть, как различные методы действительно складываются, вам понадобится эта настройка script, чтобы выполнить тесты дальше:
create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
insert AllDay
select * from (
select Tm =
DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
from AllDay
) X
where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay; -- 25,920,000 rows
Обратите внимание, что это создает таблицу 427.57 MB в вашей базе данных и займет примерно 15-30 минут. Если ваша база данных мала и установлена на 10%, это займет больше времени, чем если бы вы сначала стали большими.
Теперь для фактического тестирования производительности script. Имейте в виду, что целесообразна не возвращать строки обратно клиенту, так как это безумие стоит 26 миллионов строк и будет скрывать различия в производительности между методами.
Результаты эффективности
set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
@dd date,
@d datetime,
@di int,
@df float,
@dv varchar(10);
-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms, elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms, elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.
-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms, elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms, elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;
Пример анализа Rambling
Некоторые заметки об этом. Прежде всего, если просто выполнить GROUP BY или сравнение, нет необходимости конвертировать обратно в datetime. Таким образом, вы можете сэкономить некоторый процессор, избегая этого, если только вам не понадобится окончательное значение для целей показа. Вы можете даже GROUP BY неотвержденным значением и поместить преобразование только в предложение SELECT:
select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)
Также посмотрите, как числовые преобразования занимают немного больше времени, чтобы преобразовать обратно в datetime, но преобразование varchar почти удваивается? Это показывает часть CPU, которая посвящена вычислению даты в запросах. Есть части использования ЦП, которые не связаны с вычислением даты, и это похоже на что-то близкое к 19875 мс в вышеуказанных запросах. Затем преобразование принимает некоторую дополнительную сумму, поэтому, если есть два преобразования, эта сумма используется примерно в два раза.
Дополнительная проверка показывает, что по сравнению с Convert(, 112) запрос Convert(, 101) имеет некоторые дополнительные затраты на процессор (поскольку он использует более длинный varchar?), поскольку второе преобразование обратно в date не так дорого как начальное преобразование в varchar, но с Convert(, 112) оно ближе к тем же базовым затратам ЦП 20000 ms.
Вот те вычисления времени процессора, которые я использовал для вышеуказанного анализа:
method round single base
----------- ------ ------ -----
date 21324 19891 18458
int 23031 21453 19875
datediff 23782 23218 22654
float 36891 29312 21733
varchar-112 102984 64016 25048
varchar-101 123375 65609 7843
-
round - это время CPU для возврата туда и обратно к
datetime. -
single - это процессорное время для одного преобразования в альтернативный тип данных (тот, который имеет побочный эффект удаления временной части).
-
base - это вычисление вычитания из
singleразницы между двумя вызовами:single - (round - single). Это цифра шарикового знака, которая предполагает преобразование в и из этого типа данных, аdatetimeпримерно одинакова в любом направлении. Похоже, это предположение не является совершенным, но близко, потому что значения близки к 20000 мск с единственным исключением.
Еще одна интересная вещь заключается в том, что базовая стоимость почти равна единому методу Convert(date) (который должен быть почти 0 стоимости, так как сервер может внутренне извлекать целую часть дня прямо из первых четырех байтов datetime).
Заключение
Итак, похоже, что метод преобразования однонаправленного varchar занимает около 1,8 мкс, а однонаправленный метод DateDiff занимает около 0,18 мкс. Я основываю это на наиболее консервативном "базовом процессоре" времени в моем тестировании 18458 ms total на 25 920 000 строк, поэтому 23218 ms/25920000 = 0,18 μs. Очевидное улучшение в 10 раз кажется много, но оно откровенно мало, пока вы не имеете дело со сотнями тысяч строк (617 тыс. Строк = 1 секунда).
Даже при этом небольшом абсолютном улучшении, на мой взгляд, метод DateAdd выигрывает, потому что это лучшая комбинация производительности и ясности. Ответ, требующий "магического числа" 0.50000004, однажды кого-нибудь укусит кого-нибудь (пять нулей или шесть?), И это будет труднее понять.
Дополнительные примечания
Когда я получу какое-то время, я собираюсь изменить 0.50000004 на '12:00:00.003' и посмотреть, как это происходит. Он преобразуется в одно и то же значение datetime, и мне гораздо легче его запомнить.
Для тех, кто заинтересован, вышеуказанные тесты выполнялись на сервере, где @@Version возвращает следующее:
Microsoft SQL Server 2008 (окончательная первоначальная версия) - 10.0.1600.22 (Intel X86) 9 июля 2008 г. 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition в Windows NT 5.2 (сборка 3790: с пакетом обновления 2)