[sql-server] datetime 값의 시간 부분을 제거하는 방법 (SQL Server)?

SQL Server 2008 이상

물론 SQL Server 2008 이상에서 가장 빠른 방법은 Convert(date, @date). 필요한 경우 datetime또는로 다시 캐스팅 할 수 있습니다 datetime2.

SQL Server 2005 및 이전 버전에서 가장 좋은 것은 무엇입니까?

SQL Server에서 날짜에서 시간을 줄이는 데 가장 빠른 것이 무엇인지에 대한 일관되지 않은 주장을 보았고 일부 사람들은 테스트를했다고 말했지만 제 경험은 달랐습니다. 그러니 좀 더 엄격한 테스트를하고 모든 사람이 스크립트를 갖게하여 내가 실수를하면 사람들이 나를 고칠 수 있도록합시다.

부동 변환이 정확하지 않음

첫째, 나는 변환을 멀리 할 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

이것은 우리의 코드 나 온라인 예제에서 사람들에게 가르쳐야하는 것이 아닙니다.

또한 가장 빠른 방법도 아닙니다!

증명 – 성능 테스트

여러 메서드가 실제로 어떻게 쌓이는 지 확인하기 위해 몇 가지 테스트를 직접 수행하려면 테스트를 더 아래로 실행하려면이 설정 스크립트가 필요합니다.

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.57MB의 테이블이 생성되고 실행하는 데 15-30 분 정도 걸립니다. 데이터베이스가 작고 10 % 증가로 설정되어 있으면 먼저 충분히 큰 크기보다 더 오래 걸립니다.

이제 실제 성능 테스트 스크립트입니다. 2 천 6 백만 행에서 엄청난 비용이 발생하고 메서드 간의 성능 차이를 숨길 수 있으므로 행을 클라이언트로 다시 반환하지 않는 것이 목적입니다.

성능 결과

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;

일부 램 블링 분석

이것에 대한 몇 가지 메모. 우선 GROUP BY 또는 비교 만 수행하는 경우 .NET Framework로 다시 변환 할 필요가 없습니다 datetime. 따라서 표시 목적으로 최종 값이 필요하지 않는 한이를 방지하여 일부 CPU를 절약 할 수 있습니다. 변환되지 않은 값으로 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 부분을 나타냅니다. 날짜 계산을 포함하지 않는 CPU 사용량의 일부가 있으며 위 쿼리에서 이는 19875ms에 가까운 것으로 보입니다. 그런 다음 변환에는 약간의 추가 금액이 필요하므로 두 번의 변환이있는 경우 해당 금액은 약 두 번 사용됩니다.

더 많은 검사에 비해 것을 알 수 Convert(, 112)는 Convert(, 101)(그것이 더 이상 사용하기 때문에 쿼리가 몇 가지 추가 CPU 비용을 가지고 varchar?) 두 번째 변환 다시이 있기 때문에, date에 대한 초기 변환만큼하지 않는 비용 varchar으로하지만, Convert(, 112)그것이 가까이 같은 20000 ms CPU 기본 비용.

위의 분석에 사용한 CPU 시간에 대한 계산은 다음과 같습니다.

     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 은 대체 데이터 유형 (시간 부분을 제거하는 부작용이있는 유형)으로의 단일 변환에 대한 CPU 시간입니다.

• base 는 single두 호출 간의 차이 에서 뺀 계산입니다 single - (round - single). 해당 데이터 유형과의 변환을 가정하고 datetime어느 방향 으로든 거의 동일한 야구장 수치입니다 . 이 가정은 완벽하지는 않지만 값이 한 가지 예외를 제외하고 모두 20000ms에 가깝기 때문에 가깝습니다.

한 가지 더 흥미로운 점은 기본 비용이 단일 Convert(date)방법 과 거의 동일하다는 것입니다 (서버가 datetime데이터 유형 의 처음 4 바이트에서 정수 일 부분을 내부적으로 추출 할 수 있으므로 비용이 거의 0이어야 함 ).

결론

따라서 단일 방향 varchar변환 방법은 약 1.8 μs가 걸리고 단일 방향 DateDiff방법은 약 0.18 μs가 걸립니다. 필자는 25,920,000 행에 대해 총 18458ms를 테스트 할 때 가장 보수적 인 “기본 CPU”시간을 기준으로하므로 23218ms / 25920000 = 0.18μs입니다. 명백한 10 배 개선은 많은 것처럼 보이지만 수십만 개의 행 (617k 행 = 1 초 절약)을 처리하기 전까지는 솔직히 매우 작습니다.

이 작은 절대적인 개선을 감안할 때, 제 생각 DateAdd에는 성능과 명확성의 최상의 조합이기 때문에이 방법이 승리합니다. “매직 넘버”를 요구하는 대답은 0.50000004언젠가 누군가를 물릴 것입니다 (5 개의 0 또는 6 ???). 게다가 이해하기가 더 어렵습니다.

추가 참고 사항

시간이 생기면로 변경 0.50000004하여 '12:00:00.003'어떻게 작동하는지 볼 것입니다. 동일한 datetime값으로 변환되어 훨씬 더 쉽게 기억할 수 있습니다.

관심있는 사람들을 위해 위의 테스트는 @@ Version이 다음을 반환하는 서버에서 실행되었습니다.

Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86) 2008 년 7 월 9 일 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (빌드 3790 : 서비스 팩 2)