The previous query will be faster than the cursor version. But if you want to boost the performance tremendously then you have two options:Option 1:Change VPNKey column type to any size but MAX, and create an index on VPNKey and myDateTime like this:ALTER TABLE Analytics ALTER COLUMN VPNKey NVARCHAR(100); -- Choose the size that suits your needs.CREATE INDEX Analytics__IX__VPNKey__myDateTime ON Analytics(VPNKey, myDateTime);
Option 2:Add a computed column based on and create an index on the computed column and myDateTime like this:ALTER TABLE Analytics ADD vpn_key AS CAST(VPNKey AS NVARCHAR(100)); -- Choose the size that suits your needs.CREATE INDEX Analytics__IX__vpn_key__myDateTime ON Analytics(vpn_key, myDateTime);
then modify the query to use the computed column like this:DECLARE @totalCount INT = 0;DECLARE @pastDateTime DATETIME;DECLARE @currentDateTime DATETIME;SELECT TOP(1) @pastDateTime = A.myDateTime FROM Analytics AS A WHERE A.vpn_key = 'kiosk201.key' OR A.vpn_key = 'kiosk202.key' OR A.vpn_key = 'kiosk203.key' ORDER BY A.vpn_key, A.myDateTime;SELECT @totalCount = @totalCount + CASE WHEN DATEDIFF(SECOND, @pastDateTime, A.myDatetime) > 30 THEN 1 ELSE 0 END, @pastDateTime = CASE WHEN DATEDIFF(SECOND, @pastDateTime, A.myDatetime) > 30 OR DATEDIFF(SECOND, @pastDateTime, A.myDatetime) < 0 THEN A.myDatetime ELSE @pastDateTime END, @currentDateTime = A.myDateTime FROM Analytics AS A WHERE A.vpn_key = 'kiosk201.key' OR A.vpn_key = 'kiosk202.key' OR A.vpn_key = 'kiosk203.key' ORDER BY A.vpn_key, A.myDateTime;PRINT @totalCount;
For us, there is only the trying. The rest is not our business. ~T.S. EliotMuhammad Al Pasha