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 JoinSessionDetails ON SessionDetails.SessionID = EventDetails.SessionID Inner JoinUserDetails ON UserDetails.UserID = SessionDetails.SourceIDWhere CASEWHEN LocalStartTime > sa.LocalStartTime THEN LocalStartTime <= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime DESCWHEN LocalStartTime < sa.LocalStartTime THEN LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime ASCEND 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 DataDeclare @StartDateRange as DateTimeDeclare @EndDateRange as DateTimeSET @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 #TestVALUES --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 codeCreate 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 #EventDetailsSelect "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 EventToAckFrom #Test edOUTER APPLY (Select Top (1) EventID, LocalStartTime, CauseFrom #TestWhere LocalStartTime < ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime DESC) ss1OUTER APPLY (Select Top (1) EventID, LocalStartTime, CauseFrom #TestWhere LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE SELECT' Order By LocalStartTime ASC) ssOUTER APPLY (Select Top (1) EventID, LocalStartTime, CauseFrom #TestWhere LocalStartTime >= ed.LocalStartTime AND SiteID = ed.SiteID AND Cause = 'SITE ACKNOWLEDGEMENT' Order By LocalStartTime ASC) saWhere ed.LocalStartTime Between @StartDateRange AND @EndDateRange AND ed.SourceType != 'EV_SRC_VCP_OPERATOR'Order By ed.LocalStartTime Asc--Select * From #TestSelect * From #EventDetailsDrop Table #EventDetailsDrop Table #TestExpected Result;Event_on_ASL_To_Select_Site (Seconds) Select_Site_To_Acknowledge_Site (Seconds) Event_on_ASL_To_Acknowledge_Site (Seconds)5 5 1010 5 155 5 105 5 100 10 50 10 530 5 355 5 100 NULL NULL5 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..? |
|
|
|
|
|