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

[kaa_]

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

Есть таблица data_log с полями: point_id, timestamp, _VAL.
Есть таблица unpoints с полем: point. В таблице содержаться имена переменных, которые нужно выбирать из таблицы data_log, и выбирать значения(_VAL)так, чтобы поле timestamp(дата, время) было максимальным, т.е. самым свежим.
Создаю третью таблицу "points" в которой содержаться имена переменных из таблицы unpoints и соответствующие им самые свежие timestamp(из таблицы data_log), затем запросом сопоставляю таблицы "points" и "data_log", чтобы взять самые свежие значения переменных, которые записаны в таблице "unpoints".
Есть таблица points с полями: point, timestamp.

Привожу запрос который есть, но беда в том, что запрос слишком сильно "грузит" сервер, т.к. в таблице data_log несколько миллионов записей:
delete from points;(предварительно очищаю таблицу points)

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;(этот запрос сильно "грузит" сервер)

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;

Вопрос: как оптимизировать запрос или создать другой запрос, чтобы не сильно "грузить" сервер?

 

[Huкuтa]

я не особо вникал в запрос, но так понял что
Select MAX(DATA_LOG.timestamp)
это проблема, которая решается с помощью LIMIT

 

[GeniusUA]

скорее всего сильно грузит сервер тут выражение:
point_id Like N'%A1.A%'

строковые сравнения никогда не отличались скоростью выполнения.
тут нужно оптимизировать структурно - ввести новые таблицы, поля для хранения твоих "A1.A" и джойнить уже по первичным/вторичным ключам.

может ради прикола сравнить производительность убрав в N'%A1.A%' первый знак %.

 

[kaa_]

скорее всего сильно грузит сервер тут выражение:
point_id Like N'%A1.A%'

строковые сравнения никогда не отличались скоростью выполнения.
тут нужно оптимизировать структурно - ввести новые таблицы, поля для хранения твоих "A1.A" и джойнить уже по первичным/вторичным ключам.

может ради прикола сравнить производительность убрав в N'%A1.A%' первый знак %.

как раз к строковым сравнениям вопросов нет, все работает шикарно и быстро, а вот функция MAX "ложит" сервер.

я не особо вникал в запрос, но так понял что
Select MAX(DATA_LOG.timestamp)
это проблема, которая решается с помощью LIMIT

к сожалению, проблема не решается с помощью LIMIT, т.к. нужно просмотреть все записи в базе и найти именно наиболее "свежие"

 

[eugene_kr]

Есть два пути:
1) создать индекс на data_log c набором столбцов (point_id, timestamp desc, _VAL) и избавиться от оператора Like запросе. Тогда вычисление max будет требовать меньше ресурсов. Но затраты ресурсов будут всегда возрастать соразмерно количеству данных. В тоже время индекс будет занимать, скорее всего, больше места чем сама таблица и будут значительные накладные расходы на его поддержку (при операциях вставки). Путь неконструктивный.
2) создать триггер для таблицы data_log, который бы поддерживал содержимое таблицы points (point_id, max_timestamp, _val) в актуальном состоянии (хранил последнее время изменения для переменной и её актуальное значение). В этом случае накладные расходы при добавлении новой записи в data_log практически постоянны, а копия "свежих" значений всегда доступна в "небольшой" таблице points. Больше кода, меньше накладных расходов. На основании рекомендаций лучших собаководов.

 

[GeniusUA]

eugene_kr,
фигню говоришь. какие нафиг триггеры?
могу согласиться разве что с первым пунктом, т.к. мне кажется там явно не хватает индексов.

вообще, ТС, давай скрипт обеих таблиц, а то все эти догадки - пальцем в небо.

 

[eugene_kr]

eugene_kr,
фигню говоришь. какие нафиг триггеры?

Может вспомним классику - задачу о определении текущего баланса по счёту на базе таблицы "движения средств"?

Суть второго метода в том, что бы хранить необходимую информацию вместо постоянных вычислений. Метод достижения поставленной цели может быть произвольным. Использование триггера - это метод, который не зависит от того, контролирует ли ТС заполнение таблицы data_log.

 

[kaa_]

что-то во втором методе есть очень рациональное, учитывая, что таблица data_log огромна(миллионы записей).

скрипт таблицы data_log:
CREATE TABLE [dbo].[DATA_LOG] (
[timestamp] datetime NOT NULL,
[point_id] varchar(55) COLLATE Cyrillic_General_CI_AS NOT NULL,
[_VAL] varchar(255) COLLATE Cyrillic_General_CI_AS NULL,
CONSTRAINT [PK_DATA_LOG] PRIMARY KEY CLUSTERED ([point_id], [timestamp])
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [timestamp] ON [dbo].[DATA_LOG]
([timestamp])
ON [PRIMARY]
GO

скрипт таблицы unpoints:
CREATE TABLE [dbo].[UNPOINTS] (
[point] varchar(55) COLLATE Cyrillic_General_CI_AS NOT NULL)
GO

скрипт таблицы points:
CREATE TABLE [dbo].[points] (
[timestamp] datetime NOT NULL,
[point] varchar(55) COLLATE Cyrillic_General_CI_AS NOT NULL)
)
GO

 

[GeniusUA]

хм, и какие данные тут дложны быть? бэкап есть (если это MSSQL)?

и вот еще что - зачем тут комбинированный PK? нельзя ли как-то отделить зерна от плевел и сделать отдельное поле для PK с типом попроще, чем varchar. я не уверен, что сиквел сможет использовать тут свой индекс на эти два поля, используя только одно из них.
т.е. идея в том, чтобы сделать PK на отдельное поле и отдельный индекс на timestamp.
и опять-таки - надо избавляться от LIKE '%***%'

 

[eugene_kr]

Польза от совета есть, но обоснование "хромает".
Насколько я помню, в MsSQL кластерный индекс определяет физический порядок хранения записей. При такой его организации страдает производительность при операциях вставки/обновления данных, так как данные практически постоянно вставляются "в середину" и значительная часть записей перемещается внутри физического файла. С другой стороны при такой организации запрос вида "select t.point, max(t.timespamp) from data_log t where t.point = :Param group by t.point " может получить преимущества, так как все записи для данной переменной находятся "недалеко" и есть гарантия, что они не встречаются где-либо еще в файле.
С точки зрения оптимизации времени вставки записи в data_log выгоднее кластерный индекс сделать по дополнительному автоинкрементному полю, так как обновление связанных некластерных индексов, обычно, потребует меньшее ресурсов по сравнению с постоянным перемещением записей в файле (а добавление записей "в конец" в таком случае не потребует изменения порядка следования записей в физическом файле).
Решение зависит от того, что является критичным для производительности приложения.
Кстати, чем обосновано ограничение переменных like'ом?

 

[cspr]

как раз к строковым сравнениям вопросов нет, все работает шикарно и быстро, а вот функция MAX "ложит" сервер.

Сколько строк для "point_id Like N'%A1.A%'" (без group)?

To GeniusUa
>>сиквел сможет использовать тут свой индекс на эти два поля, используя только одно из них.
Завернул

 

[Aeris]

2kaa

индексы не помогу

like '%...%' дорогая операция

имо, надо делать без like и group by

раскажи про природу DATA_LOG.point_id и вообще расскажи что надо сделать в конечном итоге без технических деталей как конечному пользователю.

 

[BFG-9000]

Я бы для начала выдал бы неделю расстрела из крупнокалиберного пулемета за вот эти две строчки:
[point_id] varchar(55) COLLATE Cyrillic_General_CI_AS NOT NULL,
CONSTRAINT [PK_DATA_LOG] PRIMARY KEY CLUSTERED ([point_id], [timestamp])

Потом я бы добавил дополнительное числовое поле, куда бы писал хеш от point_id и сделал бы это поле первичным ключом.
Далее с учетом природы поля timestamp (оно по логике кагбе всегда нарастает) я бы создал составной индекс timestamp+point_id_hash (именно в таком порядке). И тогда обычный мускульный сервак без проблем бы пережовывал базу в 50-100 лямов записей - ибо исходя из такого составного индекса он бы обращался только к ее хвостику.

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

 

[cspr]

Потом я бы добавил дополнительное числовое поле, куда бы писал хеш от point_id ...

хеш и поиск по лайк не стыкуются.
Нужно выбирать неизвестно сколько point_id (в запросе с group by), поэтому обрезания по времени не подходят

 

[kaa_]

Я бы для начала выдал бы неделю расстрела из крупнокалиберного пулемета за вот эти две строчки:
[point_id] varchar(55) COLLATE Cyrillic_General_CI_AS NOT NULL,
CONSTRAINT [PK_DATA_LOG] PRIMARY KEY CLUSTERED ([point_id], [timestamp])

Потом я бы добавил дополнительное числовое поле, куда бы писал хеш от point_id и сделал бы это поле первичным ключом.
Далее с учетом природы поля timestamp (оно по логике кагбе всегда нарастает) я бы создал составной индекс timestamp+point_id_hash (именно в таком порядке). И тогда обычный мускульный сервак без проблем бы пережовывал базу в 50-100 лямов записей - ибо исходя из такого составного индекса он бы обращался только к ее хвостику.

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

Да, со временем, это неплохая идея, при первой возможности попробую производительность, просто в запрос добавил условие с отрезкой по времени. Вот так:
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 секунд, беру минуту про запас, т.к. часто бывает, что один, два из сеансов записи в базу не проходят. Надеюсь что эффективность такого запроса повыситься.

Кстати, чем обосновано ограничение переменных like'ом?

В базе множество различных переменных, а выбирать нужно именно те, которые начинаются на "A1.A".

хеш и поиск по лайк не стыкуются.
Нужно выбирать неизвестно сколько point_id (в запросе с group by), поэтому обрезания по времени не подходят

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

2kaa

индексы не помогу

like '%...%' дорогая операция

имо, надо делать без like и group by

раскажи про природу DATA_LOG.point_id и вообще расскажи что надо сделать в конечном итоге без технических деталей как конечному пользователю.

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

 

[eugene_kr]

Поменяй порядок следования столбцов в первичном ключе для таблицы data_log на следующий
CONSTRAINT [PK_DATA_LOG] PRIMARY KEY CLUSTERED ([timestamp], [point_id])

это физически даст тот эффект, о котором говорил bfg-9000. Как уже упоминалось выше - вставка в эту таблицу будет происходить быстрее.

Кстати, если нужно что бы имена переменных начинались с "A1.A", то это записывается как LIKE N'A1.A%' (без процента).

 

[kaa_]

Поменяй порядок следования столбцов в первичном ключе для таблицы data_log на следующий
CONSTRAINT [PK_DATA_LOG] PRIMARY KEY CLUSTERED ([timestamp], [point_id])

это физически даст тот эффект, о котором говорил bfg-9000. Как уже упоминалось выше - вставка в эту таблицу будет происходить быстрее.

Кстати, если нужно что бы имена переменных начинались с "A1.A", то это записывается как LIKE N'A1.A%' (без процента).

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

 

[LE]

Есть таблица data_log с полями: point_id, timestamp, _VAL.
Есть таблица unpoints с полем: point. В таблице содержаться имена переменных, которые нужно выбирать из таблицы data_log, и выбирать значения(_VAL)так, чтобы поле timestamp(дата, время) было максимальным, т.е. самым свежим.
Создаю третью таблицу "points" в которой содержаться имена переменных из таблицы unpoints и соответствующие им самые свежие timestamp(из таблицы data_log), затем запросом сопоставляю таблицы "points" и "data_log", чтобы взять самые свежие значения переменных, которые записаны в таблице "unpoints".
Есть таблица points с полями: point, timestamp.

Привожу запрос который есть, но беда в том, что запрос слишком сильно "грузит" сервер, т.к. в таблице data_log несколько миллионов записей:
delete from points;(предварительно очищаю таблицу points)

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;(этот запрос сильно "грузит" сервер)

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;

Вопрос: как оптимизировать запрос или создать другой запрос, чтобы не сильно "грузить" сервер?

Не надо создавать третью таблицу. Попробуй так
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%'

 

[eugene_kr]

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

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 далек от идеала, но что бы понять суть идеи - пример достаточный.

 

[dr. Unk]

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

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