Помогите мне оптимизировать сложную логику на SQL

Сначала я попытаюсь (со своим плохим английским языком) объяснить, что у меня есть и что мне нужно иметь

У меня есть список событий по временной шкале.

Событие - это некоторый дискретный сигнал 1/0, который происходит через некоторое время и имеет некоторую длительность.

мой список событий выглядит как

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

И эти события могут вызывать какое-то сложное событие, если событие A1 имеет значение 1, A2 равно 0 или A5 равно 1 в одно и то же время. Например,

Моя сложная структура событий (инцидентов):

[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

Мне нужно не пропустить ни одного возможного изменения, поэтому я, когда происходит какое-то событие, я ищу сложные события, которые могут измениться, но чтобы узнать, изменились ли сложные события, мне нужно знать о других, зависит от этого сложного события, поэтому следующий шаг получение всех зависимых событий и их состояний 1/0. Вот моя попытка:

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]

И это работает очень медленно, мне нужна серьезная оптимизация, если это возможно.

Самая медленная ( 95% времени) часть

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

может я тут что то не так делаю...

Я просто хочу проверить значение D.[Event] из EventView в это время D.[Rectime]...

обзор событий, добавленный комментариями запросов:

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]
Источник

3 ответа

Решение

Я думаю, что это должно быть примерно так же:

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

Дело в том, что вы находите свои сложные события, используя имена событий, затем вы извлекаете эти самые имена из найденных сложных событий, и, наконец, вы используете извлеченные имена в последнем CTE для сравнения с самими собой. Итак, мне показалось, что извлекающая часть была совершенно ненужной.

И без этого результирующий запрос оказался довольно простым (по крайней мере, на вид).

Источник

Ну, одна из самых основных концепций реляционного хранения данных

  • хранить данные в нормализованном порядке и
  • используйте реляционную базу данных для хранения данных, но не анализируйте и не обрабатывайте их и т. д. Для этого используйте прикладной уровень.

Это должно быть первым делом, которое вы делаете, а затем вы можете перейти на следующий уровень оптимизации запросов, объединений, создания индексов и т. Д.

Источник

Я думаю, что самая медленная часть происходит из определения EventView:

SELECT ... ( 'Alarm' + CAST(ID as nvarchar(MAX)) ) AS Col, ...

Соединение с таким вычисленным полем вызывает неприятный удар по производительности.

Вы не можете:

  • запись (Col=)Alarm+ID непосредственно в таблицу изменений или
  • обновить Alarm + ID по триггеру или
  • используйте индексированное представление для расчета Alarm + ID или
  • использовать временную таблицу для хранения Alarm + ID или хотя бы
  • не использовать nvarchar(MAX), а что-то вроде nvarchar(10) (если это меняет план запроса)

?

Источник
Другие вопросы по тегам