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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
264 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
264 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  
 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