SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help get rid of Cursor - Datetime		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

buzybrit
Starting Member

2 Posts
Posted - 10/15/2012 :  13:33:04  Show Profile  Reply with Quote
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

Saudi Arabia
257 Posts
Posted - 10/15/2012 :  19:10:54  Show Profile  Reply with Quote
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

Saudi Arabia
257 Posts
Posted - 10/15/2012 :  19:32:42  Show Profile  Reply with Quote
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 - 10/16/2012 :  13:15:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000