Views works slow, must I use select into temp tables?

Question

So I've got a serious trouble because my last query execute time is 8 minutes to get 2500 nodes. And it just combination of 2 other views.

First view is :

SELECT
   RecTime, SQL AS str, ID, 
   ROW_NUMBER() OVER(ORDER BY RecTime,ID) AS rwnb 
FROM         
   (SELECT
       A.RecTime, X.SQL, X.ID
    FROM 
       dbo.EventView AS A 
    CROSS JOIN
       dbo.Incident AS X
    WHERE      
       (PATINDEX('%' + A.Col + '%', X.SQL) > 0)) AS INC

1 second and 1600 nodes

Second view is :

SELECT     
   D.RecTime, D.Event, D.ID, CAST(CASE WHEN X.[Value] IS NULL THEN 0 ELSE X.[Value] END AS bit) AS Value
FROM         
   dbo.XDependencedEvents AS D 
INNER JOIN
    dbo.EventView AS X ON X.Col = D.Event 
                          AND D.RecTime BETWEEN X.RecTime AND X.ChangedDate

3 seconds execute time and 2100 nodes

and final view is

SELECT 
    X.[Rectime], X.[ID], X.[str], D.[Event], D.[Value],X.[rwnb] 
FROM 
    [XDependencedIncidents] AS X
INNER JOIN [XEventStates] AS D ON X.[Rectime] = D.[Rectime]
                                  AND X.[ID] = D.[ID]

8 minutes and 2500 nodes.

I even use RowNumber AS rwnb to speed up working with views but it still being really slow. Must I use select into temp tables for first 2 views ? Or I making wrong something here ?

The final purpose is to getting values for some "groups" of "events" for each time when "events" changing

Answer 1

My weird solution is using RowNumber like modify 2nd and last Views like :

SELECT     D.RecTime, D.Event, D.ID
,CAST(CASE WHEN X.[Value] IS NULL THEN 0 ELSE X.[Value] END AS bit) AS Value
,ROW_NUMBER() OVER(ORDER BY D.RecTime,D.ID) AS rwnb 
FROM         dbo.XDependencedEvents AS D 
    INNER JOIN
    dbo.EventView AS X 
        ON X.Col = D.Event 
        AND D.RecTime BETWEEN X.RecTime AND X.ChangedDate

and

SELECT X.[Rectime] , X.[ID] , X.[str], D.[Event], D.[Value],X.[rwnb], D.[rwnb]
FROM [XDependencedIncidents] AS X
INNER JOIN [XEventStates] AS D ON X.[ID] = D.[ID]
AND X.[Rectime] = D.[Rectime]

What do you think about this weird method of optimization ? I really want a better solution if there some.

BTW yes, adding row number makes from 8 minutes -> 5 seconds.