/******************************************************************************
SQL SERVER DATE TRUNCATION PERFORMANCE TESTS

A common task in SQL Server programming is the extraction of the date portion 
from a datetime column in a table.  SQL Server offers no direct, single 
function method for accomplishing this task.  As such, SQL Server programmers
have devised a wide variety of solutions.  Most methods I have seen involve
the use CONVERT to change the datetime field to a varchar datatype, sometimes 
followed by a SUBSTRING of the resultant varchar to extract the date.

An alternate method is to combine the use of DATEDIFF and DATEADD functions, 
bypassing datatype conversion while still leaving the date portion of a 
datetime column intact.

My tests in real world scenarios indicate that the combined DATEDIFF / DATEADD
method consumes much less CPU time than even the simplest of the CONVERT
methods.  I constructed a simple test case below to demonstrate the difference
between these two methods.

To run this test in your own environment:
-- Execute the batch of queries indicated to create and seed the test table.
   Please be patient as this batch may take awhile to run.
-- Execute the subsequent queries one by one, following the directions given
   to capture CPU and execution times.
******************************************************************************/

/******************************************************************************
BEFORE YOU BEGIN

Note that SQL Server's ability to run multiple threads concurrently
(Parallelism), will greatly affect the real world performance of these queries.
As such, depending on your server configuration, you may see very high
CPU times, much higher in fact than the actual elapsed time of a query.  
The following link contains a good primer on Parallelism.

http://blogs.msdn.com/craigfr/attachment/2167013.ashx
******************************************************************************/


/******************************************************************************
BEGIN - RUN THIS BATCH OF QUERIES TO SET UP TEST TABLE
******************************************************************************/
if object_id('tempdb.dbo.#tbl_date_test') is not null
	drop table #tbl_date_test

create table
	#tbl_date_test
(
	test datetime not null
)

insert into
	#tbl_date_test
select
	getdate()


-- This loop will seed the test table with ~4.2 million records. 
-- Be careful going above 22 as each + 1 doubles the table size!
declare @x int
set @x = 1
while @x <= 22
begin
	insert into
		#tbl_date_test
	select
		dateadd(dd,@x,test)
	from
		#tbl_date_test

	set @x = @x + 1
end
/******************************************************************************
END - RUN THIS BATCH OF QUERIES TO SET UP TEST TABLE
******************************************************************************/


/******************************************************************************
Run the queries below one by one, noting the associated directions.
******************************************************************************/

-- Statistics time tracking must be turned on in order to verify CPU time.
set statistics time on;

-- TEST QUERY: varchar conversion method (CPU time = 3814 ms)
-- !!!!!!! DO NOT RUN THESE DBCC COMMANDS IN A PRODUCTION ENVIRONMENT !!!!!!!
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	convert(varchar(10),test,111) test
	,count(*) quantity
from
	#tbl_date_test
group by
	convert(varchar(10),test,111);


/******************************************************************************
Check the Messages tab in SQL Server Management Studio (SSMS) for lines
that look something like this:

SQL Server Execution Times:
   CPU time = 2078 ms,  elapsed time = 270 ms.

Note the CPU and elapsed times.
******************************************************************************/

-- TEST QUERY: float conversion method (FAST)
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	cast(floor( cast( test as float ) ) as datetime)
	,count(*) quantity
from
	#tbl_date_test
group by
	cast(floor( cast( test as float ) ) as datetime);
	
	
-- TEST QUERY: date part extraction, concatenation and conversion method (VERY SLOW)
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	cast((str(year(test)) + '/' + str(month(test)) + '/' + str(day(test))) as datetime)
	,count(*) quantity
from
	#tbl_date_test
group by
	cast((str(year(test)) + '/' + str(month(test)) + '/' + str(day(test))) as datetime);
	
	
-- TEST QUERY: date minus time, cast as binary method (FAST)
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	test - cast(cast(test as binary(4)) as datetime)
	,count(*) quantity
from
	#tbl_date_test
group by
	test - cast(cast(test as binary(4)) as datetime);


-- TEST QUERY: dateadd / datediff method (FAST)
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	dateadd(dd,datediff(dd,0,test),0) test
	,count(*) quantity
from
	#tbl_date_test
group by
dateadd(dd,datediff(dd,0,test),0);


/******************************************************************************
Again check client messages for the final CPU and elapsed times. You should
experience a marked improvement in the amount of CPU and elapsed time required.
******************************************************************************/


/******************************************************************************
USER DEFINED FUNCTION TEST

There is a fair amount of debate on the web about encapsulating date truncation
functionality into a user defined function.  While user defined functions 
provide an elegant method of hiding complex logic, the overhead associated with
their execution may result in extremely poor performance when applied to large
tables.

Below is a script which creates a user defined function utilizing the best 
performing DATEDIFF / DATEADD method of date truncation.  Execute the test 
query below to test the performance difference.

FAIR WARNING - IN MY TESTS, THIS QUERY WILL RUN ORDERS OF MAGNITUDE SLOWER
THAN A QUERY THAT DOES NOT UTILIZE UDF ENCAPSULATION!!  THIS IS DUE TO THE 
FACT THAT A TSQL UDF WILL NEGATE ANY OPPORTUNITY FOR SQL SERVER TO USE 
PARALLELISM (MULTIPLE CPUS).
******************************************************************************/


/******************************************************************************
BEGIN - CREATE THE USER DEFINED FUNCTION
******************************************************************************/
if object_id('dbo.udf_dateonly') is not null
	drop function dbo.udf_dateonly
go

create function 
	dbo.udf_dateonly
(
	@p_date datetime
)
returns smalldatetime
as 
begin
	return dateadd(dd,datediff(dd,0,@p_date),0)
end
go
/******************************************************************************
END - CREATE THE USER DEFINED FUNCTION
******************************************************************************/


-- Run a test query: conversion free method encapsulated in a UDF.
dbcc dropcleanbuffers;
dbcc freeproccache;

select
	dbo.udf_dateonly(test) test
	,count(*) quantity
from
	#tbl_date_test
group by
	dbo.udf_dateonly(test);
	
/******************************************************************************
Again check client messages for the final CPU and elapsed times. You should
experience a *dramatic* increase in the amount of CPU and elapsed time.
******************************************************************************/


-- cleanup the user defined functions
if object_id('dbo.udf_dateonly') is not null
	drop function dbo.udf_dateonly;
	
-- cleanup the temp table
if object_id('tempdb.dbo.#tbl_date_test') is not null
	drop table #tbl_date_test;