Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help get rid of Cursor - Datetime

Author  Topic 

buzybrit
Starting Member

2 Posts

Posted - 2012-10-15 : 13:33:04
Ok,

So I have an application that reports in analytics on what the user is doing with a time stamp. The application is in a public space so multiple users can use it but I don't know how many. What I want to find out is how roughly many people are using it by finding out groups of times. I am currently looking for inactivity periods in the logs longer than X(In this case 30 seconds). I am using a cursor to complete this task and is taking about 17 seconds to perform this on 90,000 records(Dam cursor performance). There has to be a way to accomplish this without cursors using datepart in a select statement but I am not sure how to accomplish this. Any help would be awesome! Here is my current code in MSSQL 2008

Table:
Analytics(Screen nvarchar(max), Action nvarchar(max), myDateTime datetime, VPNKey nvarchar(max))

Cursor

IF (SELECT CURSOR_STATUS('global','MyCursor')) >=0
BEGIN
DEALLOCATE MyCursor
END

declare @totalCount int;
declare @pastDateTime DateTime;
declare @currentDateTime DateTime;
declare MyCursor cursor
set @totalCount = 0;

for select myDateTime from Analytics where VPNKey = 'kiosk201.key' or
VPNKey = 'kiosk202.key' or
VPNKey = 'kiosk203.key'
order by vpnkey,myDateTime ;
open MyCursor;
fetch next from MyCursor into @currentDateTime;
set @pastDateTime = @currentDateTime;
fetch next from MyCursor into @currentDateTime;
while @@FETCH_STATUS = 0
begin
if(datediff(second,@pastDateTime, @currentDateTime) > 30)
begin
set @totalCount = @totalCount + 1;
set @pastDateTime = @currentDateTime;
end
if(datediff(second,@pastDateTime, @currentDateTime) < 0)
begin
set @pastDateTime = @currentDateTime;
end
fetch next from MyCursor into @currentDateTime;
end
print @totalCount;
close MyCursor;
DEALLOCATE MyCursor;

Soooo if anyone knows how I can create a statement that will determine groups of time like this, that would be awesome!!!

Thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-15 : 19:10:54
Try something like this:

DECLARE @totalCount INT = 0;
DECLARE @pastDateTime DATETIME;
DECLARE @currentDateTime DATETIME;

SELECT TOP(1) @pastDateTime = A.myDateTime
FROM Analytics AS A
WHERE A.VPNKey = 'kiosk201.key'
OR A.VPNKey = 'kiosk202.key'
OR A.VPNKey = 'kiosk203.key'
ORDER BY A.VPNKey, 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.VPNKey = 'kiosk201.key'
OR A.VPNKey = 'kiosk202.key'
OR A.VPNKey = 'kiosk203.key'
ORDER BY A.VPNKey, A.myDateTime;

PRINT @totalCount;




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-15 : 19:32:42
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. Eliot

Muhammad Al Pasha
Go to Top of Page

buzybrit
Starting Member

2 Posts

Posted - 2012-10-16 : 13:15:26
Wow!!

Dam you are good, Under a second!

Thank you so much for the detailed information; This is great. Now I just need to wrap my head around it so I can recreate it next time.

Awesome malpashaa!

Thanks

Go to Top of Page
   

- Advertisement -