I understand that there have been many resources about this already, for example Find closest date in SQL Server but I don't think this is a duplicate because it goes into much more depth due to the requirement.
I need to find a record closest to a given date/time/offset either in the past or in the future to the nearest second.
IF ( OBJECT_ID( N'[dbo].[MYTIMETABLE]' ) IS NOT Null )
DROP TABLE [dbo].[MYTIMETABLE];
GO
CREATE TABLE [dbo].[MYTIMETABLE]
(
[TIMESTAMP] datetimeoffset(0) NOT NULL,
[VALUE] char(3) NOT NULL
);
GO
Fill the table with some records, in my instance, there will eventually be millions of records, hence why this requirement is a little more complicated.
SET NOCOUNT ON;
GO
WHILE ( ( SELECT COUNT(*) FROM [dbo].[MYTIMETABLE] ) < 1000 )
BEGIN
DECLARE @Script nvarchar(max) =
N'INSERT INTO [dbo].[MYTIMETABLE] ( [TIMESTAMP], [VALUE] )
SELECT DATEADD( ' +
CASE ( FLOOR( ( RAND() * 4 ) + 1 ) )
WHEN 1 THEN N'second'
WHEN 2 THEN N'minute'
WHEN 3 THEN N'hour'
WHEN 4 THEN N'day'
END +
N', ' +
CASE ( FLOOR( RAND() * 2 ) )
WHEN 0 THEN N'-'
WHEN 1 THEN N''
END +
CONVERT( nvarchar, FLOOR( ( RAND() * 100 ) + 1 ) ) +
N', SWITCHOFFSET( SYSDATETIMEOFFSET(), ''' +
CASE ( FLOOR( RAND() * 2 ) )
WHEN 0 THEN N'-'
WHEN 1 THEN N'+'
END +
N'0' +
CONVERT( nvarchar, FLOOR( RAND() * 10 ) ) +
N':00'' ) ), ' +
CONVERT( nvarchar, FLOOR( ( RAND() * 100 ) + 1 ) );
--RAISERROR( @Script , 0, 1 ) WITH NOWAIT;
EXEC sp_executesql @Script;
END
GO
The lookup script I came up with:
DECLARE @DateTime datetimeoffset(0) = SYSDATETIMEOFFSET();
SELECT TOP(1) [Current Time] = @DateTime, [Time Difference] = DATEDIFF( second, [TIMESTAMP], @DateTime ), *
FROM [dbo].[MYTIMETABLE]
ORDER BY ABS( DATEDIFF( second, [TIMESTAMP], @DateTime ) );
My question is, is this the most optimal version of this script? It looks very basic and I am worried that once this goes into production and it's running thousands of lookups per day against millions of records that it's going to suffer performance problems.
The script will be housed in a function so that it can be compiled to further optimise it but any additional performance improvement advice would be highly appreciated.
