Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

10 ways to simulate DateSerial function - Faster methods

Jul 24 2012 12:00AM by Madhivanan   

In my last blog post I have shown 10 ways to simulate DateSerial function.
Jeff Moden, SQL Server MVP, legend, mentor, Author, Exceptional DBA award winner and my friend asked me

"Which is fastest in 2008? 2012?"

Here is the analysis. If you want to run this code, do it in TEST server and don't use it in PRODUCTION server.

Create a temp table #t
create table #t(year_col int,month_col int,day_col int)
GO
Populate 1 Million sample data
insert into #t (year_col,month_col,day_col)

select 
	top 1000000 
		abs(checksum(newid()))%10000 as year_value,
		abs(checksum(newid()))%100/12 as month_value ,
		abs(checksum(newid()))%100/30 as day_col 
from 
	sys.objects as so1 cross join
	sys.objects as so2 cross join
	sys.objects as so3 cross join
	sys.objects as so4

Note that some columns may have invalid data for year,month or day. So identify them and make them valid
update
	#t
set
	year_col= case when year_col <1753 then 1753 else year_col end,
	month_col= case when month_col >11 then 12 else month_col+1 end,
	day_col= case when day_col >27 then 28 else day_col+1 end

	GO
Now Enable Statistics time and compare the time taken by each method
SET STATISTICS TIME ON 
print '======================================================'
Print 'Method 1 starts'
print '======================================================'
select 
	convert(datetime,cast(day_col as varchar(2))+'/'+cast(month_col as varchar(2))+'/'+cast(year_col as varchar(4)),103)
from
	#t 
print '======================================================'
Print 'Method 1 ends'
print '======================================================'
print '======================================================'
Print 'Method 2 starts'
print '======================================================'

select 
	convert(datetime,replace(str(year_col,4)+ str(month_col,2)+str(day_col,2),' ',0),112)
from
	#t 
print '======================================================'
Print 'Method 2 ends'
print '======================================================'

print '======================================================'
Print 'Method 3 starts'
print '======================================================'

select 
	convert(datetime,concat(day_col,'/',month_col,'/',year_col) ,103)
from
	#t 

print '======================================================'
Print 'Method 3 ends'
print '======================================================'

print '======================================================'
Print 'Method 4 starts'
print '======================================================'

select
	convert(datetime,ltrim(day_col)+'/'+ltrim(month_col)+'/'+ltrim(year_col),103)
from
	#t

print '======================================================'
Print 'Method 4 ends'
print '======================================================'

print '======================================================'
Print 'Method 5 starts'
print '======================================================'

select
	dateadd(day,day_col-1,dateadd(month,month_col-1,dateadd(year,year_col-1900,0)))
from
	#t

print '======================================================'
Print 'Method 5 ends'
print '======================================================'

print '======================================================'
Print 'Method 6 starts'
print '======================================================'

select
	dateadd(month,month_col-1,dateadd(year,year_col-1900,0))+day_col-1
from
	#t

print '======================================================'
Print 'Method 6 ends'
print '======================================================'

print '======================================================'
Print 'Method 7 starts'
print '======================================================'

select
	dateadd(month,(year_col-1900)*12+month_col-1,0)+day_col-1
from
	#t
	
print '======================================================'
Print 'Method 7 ends'
print '======================================================'

print '======================================================'
Print 'Method 8 starts'
print '======================================================'

select
	cast(datefromparts(year_col,month_col,day_col) as datetime)
from
	 #t
	 	
print '======================================================'
Print 'Method 8 ends'
print '======================================================'

print '======================================================'
Print 'Method 9 starts'
print '======================================================'

select
	datetimefromparts(year_col,month_col,day_col,0,0,0,0) 
from
	 #t
	
print '======================================================'
Print 'Method 9 ends'
print '======================================================'

print '======================================================'
Print 'Method 10 starts'
print '======================================================'

select
	cast(ltrim(year_col*10000+month_col*100+day_col) as datetime)
from
	#t

print '======================================================'
Print 'Method 10 ends'
print '======================================================'

SET STATISTICS TIME OFF 

Here is the data in tabular format
==============================================
		CPU Time        Elapsed time  
==============================================

Method 1		1809 ms		10700 ms		

Method 2		7582 ms		8781 ms		

Method 3		1342 ms		7458 ms

Method 4		1529 ms		7966 ms

Method 5		702 ms		9071 ms

Method 6		483 ms		8066 ms

Method 7		468 ms		8045 ms

Method 8		359 ms		8084 ms

Method 9		468 ms		8076 ms

Method 10	   858 ms		7932 ms
==============================================

In version 2012, Method 8 seems faster. It is becuase it only accepts three parameters and takes lesser time to convert them into datetime value.

In versions prior to 2012, Method 7 seems faster.(convert year value into month). An extra DATEADD function for a year is not needed when a year value is converted to month value.

It should be also noted that all CHARACTER convertions are slowing than Method 10 that uses arthmetic operators and convert the result into varchar and into datetime value. Only one explicit convertion happens before being converted to datetime

Note : All these were tested using SQL Server 2012 Express edition.

Tags: 


Madhivanan
3 · 40% · 12862
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"10 ways to simulate DateSerial function - Faster methods" rated 5 out of 5 by 4 readers
10 ways to simulate DateSerial function - Faster methods , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]