Отчет по событиям

[ART63]

Добрый день!

Анализ работы со СКАДА показывает, что таблица с архивами содержит на несколько порядков больше строк, чем таблица сообщений.
Из этого следует, что построить отчет об изменении состояния оборудования быстрее по таблице сообщений. Но на текущий момент в ней недостаточно для этого информации. Можно ли в эту таблицу добавить колонки ID переменной и ее значение на момент формирования сообщения? Лучше конечно и предыдущее, но это уже "супер" пожелание. На данный момент формировать отчет можно только по тексту сообщения, но работа со строками это всегда существенно дольше.

[Simple_Scada]

Здравствуйте.

Можно ли в эту таблицу добавить колонки ID переменной и ее значение на момент формирования сообщения?

Такой возможности нет. Если Вы говорите о сообщениях по нарушению границ, то значение переменной на момент формирования сообщения содержится в самом тексте сообщения. Для обычных сообщений добавить значение переменной в текст сообщения нельзя. Если используются шаблонные сообщения, то в текст сообщения можно добавить значение переменой используя спец. значение "#var_value" в подстановке. 

Если из таблицы сообщений требуется получить только определенные сообщения или группы, то можно использовать колонки GroupID и MessageID, при помощи которых можно выбрать из БД нужное сообщение или группу. ID сообщения/группы можно посмотреть в редакторе сообщений. Если требуется получить только сообщения по границам переменных, то нужно выбрать из БД сообщения с GroupID = "-2". 

[ART63]

Добрый день!

Исхожу из следующей задачи. У меня есть несколько датчиков температуры, по которым формируются сообщения о нарушении границ параметров техпроцесса.
Мне надо сформировать отчет по типу:
---------------------------------------------------------------------------------------------------------------
Переменная                    Начало выхода        Возврат в норму         Время нарушения режима 
---------------------------------------------------------------------------------------------------------------
Печь №1. Температура        10:00:18                10:01:03                              00:00:45               
---------------------------------------------------------------------------------------------------------------
Как построить такой отчет?

Как я себе это вижу. Если в таблице message_data будет присутствовать ID переменной, то данную таблицу можно было бы использовать как собственный источник для формирования отчета с помощью процедуры. По текущей структуре таблицы я не понимаю как связать MessageID и ID переменной.

[Timothy Clement]

Раз переменных немного, сделайте скрипты в которых переменные типа Bool будут устанавливаться в 1 при превышении границы и в 0 при возврате в норму (с архивированием по изменению). Может проще будет реализовать в контроллере вместо скрипта, смотрите сами. Ну а далее имея набор переменных которые сигнализируют о превышении, можно составить отчёт вроде этого https://simple-scada.com/help/report/rep-time-work-idle.html с нужными вам параметрами и временем.

[ART63]

Раз переменных немного, сделайте скрипты в которых переменные типа Bool будут устанавливаться в 1 при превышении границы и в 0 при возврате в норму (с архивированием по изменению).

Для этих целей можно использовать функцию GetValueZone. Можно понять и в какую зону вышла переменная.
Но ключевым моментом здесь является объем таблицы архивов и сообщений. Строить отчет по таблице из нескольких тысяч строк существенно быстрее, чем из нескольких миллионов. Поэтому и пытаюсь найти решение с помощью таблицы сообщений. Но в текущей версии пока не получается...

[ART63]

Можно было бы сделать некую нормативно-справочную информацию для сообщений по аналогии с переменными. А именно выгружать в таблицу БД информацию в виде
MessageID, ParamID. Такая информация в проекте есть.

[B.B]

Можно было бы сделать некую нормативно-справочную информацию для сообщений по аналогии с переменными. А именно выгружать в таблицу БД информацию в виде
MessageID, ParamID. Такая информация в проекте есть.

view не спасёт?

[ART63]

view не спасёт?

Нет, VIEW динамическая вещь, работает она на тех же объемах.

[B.B]

Нет, VIEW динамическая вещь, работает она на тех же объемах.

Вы искренне ошибаетесь...
 
Не буду разводит теорию - сразу к практике перейдём...

Сделаем такую view (с лишней табличкой и сортировкой)

Код: (sql)

CREATE VIEW ev (id,ts,msg,val,quality,nam,description) AS  SELECT m.MessageId, m.Timestamp, m.Text, t.Value,t.Quality,v.Name,v.Description
FROM messages_data m 
JOIN trends_data t ON t.ID=m.MessageID AND m.timestamp=t.timestamp
JOIN variables_data v ON v.ID=m.MessageID
ORDER BY m.MessageID, m.Timestamp

Исходная база

Код: (sql)

SELECT COUNT(*) FROM messages_data;
10 894

SELECT COUNT(*) FROM trends_data;
694 256

смотрим план запроса

Код: (sql)

EXPLAIN ANALYZE 
SELECT * FROM ev
WHERE id=142 AND ts BETWEEN '2021-12-01' AND '2022-01-18'

-> Nested loop inner join  (cost=1049.73 rows=50) (actual time=0.533..4929.417 rows=9228 loops=1)
    -> Filter: ((m.GroupID = <cache>(-(2))) and (m.MessageID = 142) and (m.`Timestamp` between '2021-12-01' and '2022-01-18'))  (cost=1006.89 rows=50) (actual time=0.496..1570.360 rows=9387 loops=1)
        -> Index range scan on m using PRIMARY  (cost=1006.89 rows=4991) (actual time=0.096..1543.277 rows=10767 loops=1)
    -> Single-row index lookup on t using PRIMARY (ID=142, Timestamp=m.`Timestamp`)  (cost=0.76 rows=1) (actual time=0.357..0.357 rows=1 loops=9387)

Добавим данных в тренд

Код: (sql)

SELECT COUNT(*) FROM messages_data;
10 894

SELECT COUNT(*) FROM trends_data;
5 554 208

План не изменился...

Код: (sql)

EXPLAIN ANALYZE 
SELECT * FROM ev
WHERE id=142 AND ts BETWEEN '2021-12-01' AND '2022-01-18'

-> Nested loop inner join  (cost=1392.52 rows=499) (actual time=0.411..73.427 rows=9228 loops=1)
    -> Filter: ((m.MessageID = 142) and (m.`Timestamp` between '2021-12-01' and '2022-01-18'))  (cost=1000.38 rows=499) (actual time=0.380..27.775 rows=9387 loops=1)
        -> Index range scan on m using PRIMARY  (cost=1000.38 rows=4991) (actual time=0.105..11.896 rows=10767 loops=1)
    -> Single-row index lookup on t using PRIMARY (ID=142, Timestamp=m.`Timestamp`)  (cost=0.69 rows=1) (actual time=0.004..0.005 rows=1 loops=9387)

и теперь БЕЗ фильтра

Код: (sql)

EXPLAIN ANALYZE 
SELECT * FROM ev

-> Nested loop inner join  (cost=16118.27 rows=9982) (actual time=49.160..95.380 rows=9228 loops=1)
    -> Nested loop inner join  (cost=8275.40 rows=9982) (actual time=49.120..60.000 rows=9389 loops=1)
        -> Sort: m.MessageID, m.`Timestamp`  (cost=1038.45 rows=9982) (actual time=33.688..37.755 rows=10894 loops=1)
            -> Table scan on m  (cost=1038.45 rows=9982) (actual time=0.098..8.186 rows=10894 loops=1)
        -> Single-row index lookup on v using PRIMARY (ID=m.MessageID)  (cost=0.63 rows=1) (actual time=0.002..0.002 rows=1 loops=10894)
    -> Single-row index lookup on t using PRIMARY (ID=m.MessageID, Timestamp=m.`Timestamp`)  (cost=0.69 rows=1) (actual time=0.003..0.003 rows=1 loops=9389)

Т.е. выборка идёт по таблице messages_data. А потом по первичному ключу ищутся значения в таблице трендов.
Это ускоряет поиск.

Проверьте. Должно ускорить.

[Simple-Scada]

Но ключевым моментом здесь является объем таблицы архивов и сообщений. Строить отчет по таблице из нескольких тысяч строк существенно быстрее, чем из нескольких миллионов.

Это зависит только от того, будут ли использоваться индексы для поиска данных, или нет.
Если в запросах Вы планируете делать выборку без использования столбцов ID и Timestamp (по ним строится кластерный индекс), то размер таблицы трендов будет иметь значение (чем больше строк, тем медленнее выборка).
Если в запросе на выборку данные будут выбираться по ID и Timestamp (пример ниже), то количество строк в таблице трендов не имеет значения. Например, в таблице на 10 миллионов записей такая выборка будет очень быстрой. Скада именно так и работает, выбирая данные по индексируемым столбцам (иначе просмотр трендов был бы невозможен после роста таблицы трендов). В этом смысл индексов. Все остальные способы решения описанные в этой теме будут хуже.

Код: (sql)

SELECT * FROM `trends_data` 
WHERE (`id` = 1) AND (`timestamp` >= '2021-01-13 10:50:00.000') AND (`timestamp` <= '2021-01-13 11:00:00.000')

[ART63]

Добрый день!

Сделаем такую view (с лишней табличкой и сортировкой)

Код: (sql)

CREATE VIEW ev (id,ts,msg,val,quality,nam,description) AS  SELECT m.MessageId, m.Timestamp, m.Text, t.Value,t.Quality,v.Name,v.Description
FROM messages_data m 
JOIN trends_data t ON t.ID=m.MessageID AND m.timestamp=t.timestamp
JOIN variables_data v ON v.ID=m.MessageID
ORDER BY m.MessageID, m.Timestamp

Вы не поняли мой посыл разработчикам. Сейчас Вы сделали view, в которой скрестили, извините, ежа с ужом. Вы сделали джойн по ID сообщения и ID параметра.
Чтобы сделать такое представление корректным, в таблице сообщений должен присутствовать ID параметра, по которому формируется сообщение. Именно по нему и должен осуществляться джойн. И именно этот столбец я предложил добавить в таблицу.

Это зависит только от того, будут ли использоваться индексы для поиска данных, или нет.

Все верно. Но анализ данных часто включает в себя и значение переменной. А это не индексированное поле. Чтобы понять, ДГУ включилось или выключилось, мне надо посмотреть на ЗНАЧЕНИЕ переменной WORK. Если в таблице сообщений будут присутствовать столбцы ParamID и ValueID (а эти данные на момент формирования сообщения в системе есть), то обработку сообщений можно делать по этой таблице при существенно меньшем количестве строк.

[Simple-Scada]

Все верно. Но анализ данных часто включает в себя и значение переменной.

При использовании индексов дополнительные условия будут слабо влиять на производительность, т.к. сначала СУБД сделает выборку данных по индексам, а затем в полученной (малой) выборке будет искать нужные значения обычным перебором. Т.е. такой поиск тоже будет быстрым (если за искомый период по искомому ID данных не слишком много):

Код: (sql)

SELECT * FROM `trends_data` 
WHERE (`id` = 1) AND (`timestamp` >= '2021-01-13 10:50:00.000') AND (`timestamp` <= '2021-01-13 11:00:00.000') AND (`value` = 5)

Если ничего не подходит, то нужно рассматривать варианты без использования стандартных таблиц. Лучшим по производительности было бы создание собственной таблицы в БД и заполнение её из скады готовыми данными с нужной структурой через RunSQL. Затем выборка в отчет как описано здесь.

Чтобы сделать такое представление корректным, в таблице сообщений должен присутствовать ID параметра, по которому формируется сообщение.

Это полностью исключено, мы никогда не будем добавлять в стандартные таблицы колонки, которые нужны малому числу пользователей скады. Т.к. для 99% остальных пользователей это будут бесполезные колонки, которые просто съедят лишнее место на жестком диске и ухудшат производительность БД и скорость вставки данных или выборки из скады.

[ART63]

Это полностью исключено, мы никогда не будем добавлять в стандартные таблицы колонки, которые нужны малому числу пользователей скады. Т.к. для 99% остальных пользователей это будут бесполезные колонки, которые просто съедят лишнее место на жестком диске и ухудшат производительность БД и скорость вставки данных или выборки из скады.

Согласен, хотя трудно оценить насколько может быть востребована та или иная функциональность, если ее не попробовать. 
Но есть несложный промежуточный вариант, который позволит создать подобное VIEW. Это сделать из клиента выгрузку по аналогии с пунктом "создать таблицу переменных", например, "создать справочник сообщений". Структура: message_id, variable_id. Тем более в классе TM_Message variable_id присутствует.
Это позволит сделать view с джойнами между справочником сообщений, справочником переменных и архивами сообщений и трендов.

[B.B]

Вы не поняли мой посыл разработчикам. Сейчас Вы сделали view, в которой скрестили, извините, ежа с ужом. Вы сделали джойн по ID сообщения и ID параметра.

Вопрос спорный... кто чего не понял...
Изначально спрашивалось ведь именно про это?

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

MessageID удивительнейшим образом совпадает с ID переменной у которой контролируются границы...
JOIN, в данном случае, выступает в роли дополнительного фильтра, который не только связывает две таблички, но и фильтрует (из плана запроса это видно) лишние сообщения...

Возможно что это частный случай для моих проектов.
Быть может когда  MessageID много или ID переменной в первой десятке, то используются иные значения.
Точный ответ дадут только разработчики.

[ART63]

MessageID удивительнейшим образом совпадает с ID переменной у которой контролируются границы...

Добрый день!
У меня не совпадают.