Help to speed up working query or re-design

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 to speed up working query or re-design

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-02 : 20:23:31

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-02 : 20:53:55

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'

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-02 : 23:54:11

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

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-03 : 19:09:45

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-04 : 11:50:28

What indexes do you have currently?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-04 : 12:32:24

Can you explain what you are trying to do? Maybe ther is another way to approch the problem..?