We have a table that stores website statistics, It contains 12,294,806 rows. Some of the columns stored are Id (this is the primary key), DateTime and ClientIpAddress. I'm trying to write a query that finds all requests that occurred less than 5 seconds apart and came from the same IP address. So far I have this:
FROM [Custom_Website_Data].[dbo].[Sta_WebStats] AS s1
INNER JOIN [Custom_Website_Data].[dbo].[Sta_WebStats] AS s2
ON s2.DateTime <= DateAdd(ss, 5, s1.DateTime)
AND s2.DateTime >= s1.DateTime
AND s1.ClientIpAddress = s2.ClientIpAddress
This works but it is very slow. Can anyone think of a faster way of doing it?