Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Performance test - select vs while - Get all days between two dates.
--Sql Server 2014 Express Edition --Batches are separated by 'go' DECLARE @Date1 DATE, @Date2 DATE, @DATE_NOW DATETIME = GETDATE(); SET @Date1 = '2017-01-01'; SET @Date2 = '2018-01-01'; CREATE TABLE #TEMP_DATES_TEST ( DATE DATETIME ); INSERT INTO #TEMP_DATES_TEST SELECT DATEADD(DAY ,number,@Date1) [DATE] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,@Date1) <= @Date2; SELECT 'QUERY 1 - TAKEN ' + CONVERT(VARCHAR(50), DATEDIFF(MS, @DATE_NOW, GETDATE())) + 'MS'; SET @DATE_NOW = GETDATE(); DECLARE @DAYS INT, @COUNTER INT = 0; SET @DAYS = DATEDIFF(DAY, '2017-01-01','2018-01-01'); SELECT @DAYS; CREATE TABLE #TEMP_DATES ( DATE DATETIME ); WHILE @DAYS > 0 BEGIN SET @DAYS = @DAYS - 1; INSERT INTO #TEMP_DATES SELECT DATEADD(DAY, @COUNTER ,'2017-01-01'); SET @COUNTER = @COUNTER + 1; END; --select 'DAYS = ' + convert(varchar(3), @DAYS); --select 'COUNTER = ' + convert(varchar(3), @COUNTER); --SELECT * FROM #TEMP_DATES; SELECT 'QUERY 2 (MORE SLOWER) - TAKEN ' + CONVERT(VARCHAR(50), DATEDIFF(MS, @DATE_NOW, GETDATE())) + 'MS'; DROP TABLE #TEMP_DATES; DROP TABLE #TEMP_DATES_TEST;
run
|
edit
|
history
|
help
0
Sundays in 2017
20181CSE0041
Libros
Xml Excercise 2
student
shrink logs
repert
SI 1
CorreçãoDeErros - UPDATE, DELETE e ALTER TABLE
Project 1