Help me to optimize hard logic on SQL

Question

First I'll try (with my bad English) to explain what I've got and what I need to have

I have got the list of Events by TimeLine.

Event is some discrete signal 1/0 that happens in some time and got some duration.

my event list is looking like

Rectime - start event time
Col - event name
ChangedDate - end event time
InitalValue - event message
Value - event state 1/0

And those events can call some Complex event if there are A1 event is 1 A2 is 0 or A5 is 1 in the same time -- Just for example

my complex events (incidents) structure is :

[ID] - just ID
[Name] - just Name 
[SQL] - here is list of event names with logics alike ***(A1 AND NOT A2) OR A5***
[Message] - event message

I need do not miss any possible change so I when some event is happens I'm looking for complex events it could change , but to know if that changed complex events I need to know about other depends of this complex event, so next step is getting all the dependent events and their states 1/0. Here is my try :

With DependencedIncidents AS -- Get all dependenced Incidents from this Event
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
)
, DependencedEvents AS -- Split SQL string to get dependeced Events for each dependeced Incident
(
    select distinct word AS [Event] , [RecTime]
    from
    (
        select v.number, t.[RecTime] As [RecTime],
            substring(t.str+')',
                      v.number+1,
                      patindex('%[() ]%',
                               substring(t.str+')',
                                         v.number+1,
                                         1000))-1) word
        from DependencedIncidents AS t
        inner join master..spt_values v on v.type='P'
          and v.number < len(t.str)
          and (v.number=0 or substring(t.str,v.number,1) like '[() ]')
    ) x
    where word not in ('','OR','AND')
)
, EventStates AS -- Dependeced events with their states 1/0
(
    Select D.[RecTime], D.[Event], X.[Value]
    From [DependencedEvents] AS D
    LEFT JOIN [EventView] AS X 
        ON X.Col = D.[Event]
        AND D.[Rectime] >= X.[Rectime]
        AND D.[Rectime] <= X.[ChangedDate]
)

select * from EventStates
order by [RecTime]

And it works very very slow , I need a serious optimization if that possible.

The slowest ( 95% of time ) part is

LEFT JOIN [EventView] AS X 
    ON X.Col = D.[Event]
    AND D.[Rectime] BETWEEN X.[Rectime] AND  X.[ChangedDate]

maybe I'm doing something wrong here...

I just want to check Value of D.[Event] from EventView in this time D.[Rectime]...

eventview added by comments requests :

ALTER VIEW [dbo].[EventView] AS
            (SELECT RecTime, ChangedDate, ( 'Alarm' + CAST(ID as nvarchar(MAX)) ) AS Col, InitialValue, Value FROM [dbo].[Changes]
                WHERE InitialValue <> '')
            UNION ALL
            SELECT RecTime, ChangedDate, Col, InitialValue, Value FROM [dbo].[XDeltaIntervals]
            UNION ALL
            SELECT RecTime, ChangedDate, Col, InitialValue, Value FROM [dbo].[ActvXDeltaIntervals]

Answer 1

I think this should be about the same:

SELECT
  ev.Rectime,
  ev.Event,
  ev2.Value
FROM EventView AS ev
  INNER JOIN Incident i
    ON PATINDEX('%' + ev.Col + '%', i.SQL) > 0
  LEFT JOIN EventView ev2
    ON ev.Col = ev2.Col AND ev.Rectime BETWEEN ev2.Rectime AND ev2.ChangedDate

The thing is, you are finding your complex events using event names, then you are extracting those very names from the complex events found, and finally you are using the extracted names in the last CTE to compare against themselves. So, it seemed to me that the extracting part was completely unnecessary.

And without it the resulting query turned out to be quite simple (in appearance at least).