Помогите составить запрос SQL

[cspr]

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

Это, вообще, многое меняет. Индекс по функции (или как он там называется у мсскл) поможет

 

[Aeris]

2Kaa, какое время отклика приемлемо?

Задачу вычисления суммы m элементов в коллекции размерностью n элементов можно решить за n время, а можно за m + log n время, но можно решить и за время m, где m = 10, а n = 10^10.

Твоя задача решается за время m. Есть еще вариант O(1) - это уже для особых ********цев.

Если время отклика 10-15 секунд пользователя устраивает, тогда можно ничего не менять.

Если надо что-то побыстрей, тогда можно использовать логарифм алгоритм.

Если нужно сделать мгновенное время отклика, тогда m.

Для 2 и 3 варианта проидется либо менять таблицы, либо вариант с триггером.

может стоит попробывать с окнами

⚠ Тільки зареєстровані користувачі бачать весь контент та не бачать рекламу.

проблема:
1. таблица содержит 2М строк
2. тейблскан\индексскан по всем 2М строкам изза like "%...%"

как показывает практика... достать нужно то всего 5 строчек.

...добавил условие с отрезкой по времени. Вот так:
insert into points Select MAX(DATA_LOG.timestamp), DATA_LOG.point_id FROM dbo.DATA_LOG where (point_id Like N'%A1.A%')and(timestamp>=DATEADD(minute, -1, getdate())) GROUP BY DATA_LOG.point_id;
т.е. проверяю записи за последнюю минуту. Не уточнил сразу, все данные пишутся в таблицу с частотой раз в 20 секунд, беру минуту про запас, т.к. часто бывает, что один, два из сеансов записи в базу не проходят. Надеюсь что эффективность такого запроса повыситься.

если по логике приложение такое решение приемлемо, тогда надо обратить внимаение индексы. из ддл видно, что есть 2 индекса:
кластерный:point_id, timestamp
некластерный:timestamp

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

здесь скорее всего будет использоваться некластерный по timestamp, что влечет за собой ridlookup.

можно ускорить это дело 2мя способами:
1. поменять порядок кластрного индекса - timestamp, point_id
2. из 2го индекса сделать покрывающий, либо включающий колонки

это уберет ридлукап.

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

 

[dr. Unk]

проблема:
1. таблица содержит 2М строк
2. тейблскан\индексскан по всем 2М строкам изза like "%...%"

как показывает практика... достать нужно то всего 5 строчек.

Like - не проблема
Делаем отдельный словарь, по нему делаем Like остальное дело техники

 

[LE]

Такое впечатление что я попал в общество фантастов
Какой нафиг словарь? Какой нафиг скан 2М cтрок по Like? Читаем ВНИМАТЕЛЬНО постановку задачи (первый пост).

Есть таблица unpoints с полем: point. В таблице содержаться имена переменных, которые нужно выбирать из таблицы data_log

 

[dr. Unk]

Такое впечатление что я попал в общество фантастов
Какой нафиг словарь? Какой нафиг скан 2М cтрок по Like? Читаем ВНИМАТЕЛЬНО постановку задачи (первый пост).

Читайте еще ниже, и долго курите, что курил автор

Привожу запрос который есть, но беда в том, что запрос слишком сильно "грузит" сервер, т.к. в таблице data_log несколько миллионов записей:
insert into points Select MAX(DATA_LOG.timestamp), DATA_LOG.point_id FROM dbo.DATA_LOG where point_id Like N'%A1.A%' GROUP BY DATA_LOG.point_id;(этот запрос сильно "грузит" сервер)

 

[kaa_]

Если создать вот такую таблицу

CREATE TABLE [dbo].[last_data_values](
	[timestamp] [datetime] NOT NULL,
	[point_id] [varchar](55) NOT NULL,
	[_VAL] [varchar](255) NULL
) ON [PRIMARY]

и на data_log повесить, например, вот такой триггер

CREATE TRIGGER dbo.MyDataLogTrg
   ON  dbo.data_log
   AFTER INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  declare @val varchar(255)
  declare @ts datetime;
  declare @point_id varchar(55);
  
  set @point_id = (select point_id from inserted);
  set @val = (select _val from inserted);
  set @ts = (select timestamp from inserted);
  
  if @val is not null begin
    update last_data_values
      set _val = @val, timestamp = @ts
      where point_id = @point_id;
    if @@rowcount = 0 begin
      insert into last_data_values(timestamp, point_id, _val)
        values (@ts, @point_id, @val);
    end;
  end;
END

То в произвольный момент времени можно получить требуемый результат из таблицы last_data_values с имеющимися (озвученными) ограничениями и без каких-либо проблем с производительностью. Имена, индексы и владельцев - можно определить на свой вкус.

p.s. знаю, мой t-sql далек от идеала, но что бы понять суть идеи - пример достаточный.

По-моему, очень дельный вариант, беру как альтернативу обрезке по времени.

может стоит попробывать с окнами

⚠ Тільки зареєстровані користувачі бачать весь контент та не бачать рекламу.

Возможно. Есть еще ссылки с "популярным" объяснением, что такое окна?

 

[LE]

Автор допусти ошибку в запрсе и у него запрос соответственно долго выполняется, но это не значит что мы должны идти по его стопам . В его запросе есть две грубейшие ошибки которые делают фулл скан - это Group By и Like

Я предложил один из самых простых и проверенных способов

Select 
a.* From unpoints c left outer join data_log a on c.point = a.point_id
Where 
a.timestamp = (Select Max(b.TimeStamp) From data_log b where a.Point_id = b.Point_ID)
and c.point Like N'%A1.A%'

но видно автор не ищет простых путей

 

[kaa_]

Автор допусти ошибку в запрсе и у него запрос соответственно долго выполняется, но это не значит что мы должны идти по его стопам . В его запросе есть две грубейшие ошибки которые делают фулл скан - это Group By и Like

Я предложил один из самых простых и проверенных способов

Select 
a.* From unpoints c left outer join data_log a on c.point = a.point_id
Where 
a.timestamp = (Select Max(b.TimeStamp) From data_log b where a.Point_id = b.Point_ID)
and c.point Like N'%A1.A%'

но видно автор не ищет простых путей

Возможно, я не до конца понял твою идею, я извиняюсь и прошу пояснить. То, что я увидел в твоём запросе: ты объеденил все запросы в один, но я не вижу в нем оптимизации по времени выполнения запроса. Почему ты считаешь, что твой запрос будет выполнятся достаточно быстро?

 

[LE]

Запрос сделан на твоих таблица (скриптах выложенных выше).
Просто запусти его и проверь работу, напрмер подставь данные в Like или вообще без Like. Если скорость выполнения тебя устроит - то тогда могу подробнее если непонятно, в противном случае скажи что всёравно выполняется долго и тогда не будем флудить

 

[eugene_kr]

Вариант, предложенный LE, возможно будет работать достаточно быстро (если оптимизатор примет правильное решение об очерёдности выполнения коррелированного подзапроса). Но, давайте учтём всю известную постановку задачи. Тогда получается следующий запрос:

select 
  p.point, d._val
from
  unpoints p
    inner join data_log d on d.point = a.point_id
where 
  d.timestamp = (select max(b.TimeStamp)
                          from data_log d_max
                          where d_max.point_id = p.point and d_max._val is not null)
  and p.point like N'A1.A%'

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

но, если добавить отсечение по времени, то может оказаться не так уж и плохо:

select 
  p.point, d._val
from
  unpoints p
    inner join data_log d on d.point = a.point_id
where 
  d.timestamp = (select max(b.TimeStamp)
                          from data_log d_max
                          where d_max.point_id = p.point
                            and d_max._val is not null
                            and d_max.timestamp >= DATEADD(minute, -1, getdate()
                        )
  and p.point like N'A1.A%'

Проблема этого подхода в том, что ответственность за "эффективность" выполнения данного запроса полностью лежит на оптимизаторе и, со временем, он (оптимизатор) может пересмотреть свою точку зрения.

Эта дискуссия напоминает гадание на кофейной гуще. Не определено сколько процентов данных возвращает запрос

select d.* from data_log d where d.point_id like N'A1.A%' and d.timestamp >= DATEADD(minute, -1, getdate()

каков объём данных содержится в таблице data_log, что реально означает исходная фраза "запрос грузит сервер", о каком сервере/версии/железе идет речь, какое время выполнения запроса является приемлемым, что допускается делать на сервере БД вообще и т.д.?
Без ответов - мы оптимизируем положение сферического коня в вакууме .

 

[LE]

впринципе, можно и так как написал eugene_kr только нужны уточнения от ТС. Если _val за последнюю минуту был null то его не выбирать или или выбирать последний значащий _val?
В общем, вот запрос, берешь "руки в руки" и "пирдолишь" или "топчишь клаву"
Устал

 

[eugene_kr]

Читаем внимательно

Вообщем, нужно сделать вот что: выбирать из таблицы data_log самые свежие(!) значения переменных, которые начинаются на "A1.A" и, желательно, учесть, что имя переменной может быть, а значение может быть 'NULL'(т.е. не брать со значением NULL)

если это ограничение прочитать так, как оно написано, то в таблице фактов могут быть записи, с "пустым" значением в поле _val. И эти записи не нужно учитывать, при поиске "самых свежих" значений.


читаем дальше не менее внимательно

Не уточнил сразу, все данные пишутся в таблицу с частотой раз в 20 секунд, беру минуту про запас, т.к. часто бывает, что один, два из сеансов записи в базу не проходят.

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


Так что это не тюнинг, а дополнение запроса пропущенными ограничениями.

P.S. Надеюсь, что все это поможет принять ТСу обоснованное и эффективное решение.

P.P.S. Очень напоминает систему для визуализации текущих параметров (значений датчиков) некоторой системы.

 

[LE]

eugene_kr, ты не обижайся, жара и т.д. Я уже исправил свой пост

 

[eugene_kr]

Никаких проблем, жара на всех влияет.

Главное, оно ведь не кто прав или неправ. У каждого варианта есть свои достоинства, недостатки и цена. Главное - иметь достаточно полное представление о рассматриваемых вариантах и знать, какие есть альтернативы. Тогда и паршивого кода будет чуть-чуть меньше, а уровень наших разработчиков выше .

 

[kaa_]

Не определено сколько процентов данных возвращает запрос

select d.* from data_log d where d.point_id like N'A1.A%' and d.timestamp >= DATEADD(minute, -1, getdate()

Запрос возвращает всего 92 записи.

каков объём данных содержится в таблице data_log,

В data_log содержиться 26 000 000 записей +- 100 000.

что реально означает исходная фраза "запрос грузит сервер", о каком сервере/версии/железе идет речь, какое время выполнения запроса является приемлемым, что допускается делать на сервере БД вообще и т.д.?
Без ответов - мы оптимизируем положение сферического коня в вакууме .

"Запрос грузит сервер" означает, что вызывая не оптимизированный запрос один раз в минуту, 100%я загрузка серверного процессора длится 17-25 секунд каждой минуты. Приемлемым считается время загрузки процессора на 100% равное 0,001 - 5 секунд.

 

[LE]

To kaa_
Так а что с последним вариантом запроса?
Какие результаты?

 

[kaa_]

Пока приостановимся в оптимизации запроса, до тех пор пока появится возможность поработать с БД и попробовать все намеченные варианты. Большое спасибо всем, кто отозвался. Особо хочется отметить за дельные предложения:eugene_kr, le.
Результаты экспериментов обязательно оглашу.

 

[eugene_kr]

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

 

[kaa_]

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

Спасибо за очень важную деталь.

 

[kaa_]

Очень всё хорошо получилось с отсечением по времени:
select _val, point_id, data_log.timestamp from data_log INNER JOIN points ON data_log.point_id = points.point
WHERE (points.timestamp=DATA_LOG.[timestamp]) and (timestamp >= DATEADD(minute, -20, getdate());

Запрос выполняется за 219 мсек. Процессор сервера практически не грузится.


Спасибо огромное всем, кто отозвался и особое eugene_kr и LE.