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 to speed up working query or re-design
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 04/02/2013 :  20:23:31  Show Profile  Reply with Quote
This is only part of the code however what I am tring to do is add a case statement in a where clause.

CROSS APPLY
(Select Top (1) EventID, LocalStartTime, Cause, SiteID, LoginName
From EventDetails Inner Join
SessionDetails ON SessionDetails.SessionID = EventDetails.SessionID Inner Join
UserDetails ON UserDetails.UserID = SessionDetails.SourceID
Where CASE
WHEN LocalStartTime > sa.LocalStartTime THEN LocalStartTime <= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime DESC
WHEN LocalStartTime < sa.LocalStartTime THEN LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime ASC
END As Selection
) ss

Edited by - harlingtonthewizard on 04/03/2013 01:37:37

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/02/2013 :  20:53:55  Show Profile  Reply with Quote
You can recast the where clause to the following:
where
	(
		(LocalStartTime > sa.LocalStartTime and LocalStartTime <= ed.LocalStartTime )
		or
		(LocalStartTime < sa.LocalStartTime and LocalStartTime >= ed.LocalStartTime )
	) 
	and SiteID = ed.SiteID AND Cause = 'SITE SELECT'
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 04/02/2013 :  23:54:11  Show Profile  Reply with Quote
Thanks James for your suggestion however your suggestion will not work.

Here is the full test case and working (inefficent code). How can I change this to speed it up?


--Create Test Data

Declare @StartDateRange as DateTime
Declare @EndDateRange as DateTime

SET @StartDateRange = (Select '04/02/2013 00:00:00')
SET @EndDateRange = (Select '04/05/2013 00:00:00')

Create Table #Test (EventID int, SiteID int, LocalStartTime DateTime, Cause nvarchar(MAX), SourceType nvarchar(MAX))
Insert INTO #Test
VALUES

--Event > Select > Ack
('1', '8', '04/03/2013 17:00:00', 'CAM01 Event', 'EV_SRC_VCP_CAMERAS'),
('2', '8', '04/03/2013 17:00:05', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('3', '8', '04/03/2013 17:00:10', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('4', '8', '04/03/2013 17:00:15', 'CAM02 Event', 'EV_SRC_VCP_CAMERAS'),
('5', '8', '04/03/2013 17:00:20', 'CAM03 Event', 'EV_SRC_VCP_CAMERAS'),
('6', '8', '04/03/2013 17:00:25', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('7', '8', '04/03/2013 17:00:30', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('8', '8', '04/03/2013 17:00:35', 'CAM04 Event', 'EV_SRC_VCP_CAMERAS'),
('9', '8', '04/03/2013 17:00:40', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('10', '8', '04/03/2013 17:00:45', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
-- Select > Event > Ack
('11', '8', '04/03/2013 18:00:00', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('12', '8', '04/03/2013 18:00:05', 'CAM05 Event', 'EV_SRC_VCP_CAMERAS'),
('13', '8', '04/03/2013 18:00:10', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('14', '8', '04/03/2013 18:00:15', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('15', '8', '04/03/2013 18:00:20', 'CAM06 Event', 'EV_SRC_VCP_CAMERAS'),
('16', '8', '04/03/2013 18:00:25', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
--Event > Select > Ack seperated
('17', '9', '04/03/2013 19:00:00', 'CAM07 Event', 'EV_SRC_VCP_CAMERAS'),
('18', '10', '04/03/2013 19:00:15', 'CAM08 Event', 'EV_SRC_VCP_CAMERAS'),
('19', '10', '04/03/2013 19:00:20', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('20', '10', '04/03/2013 19:00:25', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('21', '9', '04/03/2013 19:00:30', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('22', '9', '04/03/2013 19:00:35', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
--Ack > Select > Event
('23', '11', '04/03/2013 20:00:05', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('24', '11', '04/03/2013 20:00:10', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR'),
('25', '11', '04/03/2013 20:00:15', 'CAM09 Event', 'EV_SRC_VCP_CAMERAS'),
--Ack > Event > Select
('26', '12', '04/03/2013 20:00:20', 'SITE ACKNOWLEDGEMENT', 'EV_SRC_VCP_OPERATOR'),
('27', '12', '04/03/2013 20:00:25', 'CAM10 Event', 'EV_SRC_VCP_CAMERAS'),
('28', '12', '04/03/2013 20:00:30', 'SITE SELECT', 'EV_SRC_VCP_OPERATOR')




--Working slow code

Create Table #EventDetails ([Event_on_ASL_To_Select_Site (Seconds)] bigint, [Select_Site_To_Acknowledge_Site (Seconds)] bigint, [Event_on_ASL_To_Acknowledge_Site (Seconds)] bigint)

Insert Into #EventDetails

Select "EventToSelect" = CASE WHEN ss.LocalStartTime > sa.LocalStartTime OR ss.LocalStartTime IS NULL THEN 0 ELSE DATEDIFF(SECOND, ed.LocalStartTime, ss.LocalStartTime) END,
"SelectToAcknowledge" = CASE WHEN ss.LocalStartTime > sa.LocalStartTime OR ss.LocalStartTime IS NULL THEN DATEDIFF(SECOND, ss1.LocalStartTime, sa.LocalStartTime) ELSE DATEDIFF(SECOND, ss.LocalStartTime, sa.LocalStartTime) END,
DATEDIFF(SECOND, ed.LocalStartTime, sa.LocalStartTime) AS EventToAck
From #Test ed

OUTER APPLY
(Select Top (1) EventID, LocalStartTime, Cause
From #Test
Where LocalStartTime < ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime DESC) ss1

OUTER APPLY
(Select Top (1) EventID, LocalStartTime, Cause
From #Test
Where LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime ASC) ss

OUTER APPLY
(Select Top (1) EventID, LocalStartTime, Cause
From #Test
Where LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE ACKNOWLEDGEMENT' Order By LocalStartTime ASC) sa

Where ed.LocalStartTime Between @StartDateRange AND @EndDateRange AND ed.SourceType != 'EV_SRC_VCP_OPERATOR'
Order By ed.LocalStartTime Asc

--Select * From #Test
Select * From #EventDetails

Drop Table #EventDetails
Drop Table #Test



Expected Result;

Event_on_ASL_To_Select_Site (Seconds) Select_Site_To_Acknowledge_Site (Seconds) Event_on_ASL_To_Acknowledge_Site (Seconds)
5 5 10
10 5 15
5 5 10
5 5 10
0 10 5
0 10 5
30 5 35
5 5 10
0 NULL NULL
5 NULL NULL

Edited by - harlingtonthewizard on 04/03/2013 01:30:43
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 04/03/2013 :  19:09:45  Show Profile  Reply with Quote
Any ideas? Ran a test last night and to do around 124000 events it took 2 hours! Management studio is not indicating I need anymore indexes.

Edited by - harlingtonthewizard on 04/04/2013 01:40:40
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/04/2013 :  11:50:28  Show Profile  Reply with Quote
What indexes do you have currently?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/04/2013 :  12:32:24  Show Profile  Reply with Quote
Can you explain what you are trying to do? Maybe ther is another way to approch the problem..?
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.05 seconds. Powered By: Snitz Forums 2000