Что-то зачастил я сюда... Ну да ладно.
Имеем:

CREATE TABLE [dbo].[Science_data](
    [id] [bigint] NOT NULL,
    [UTC] [datetime] NOT NULL,
    [SC2] [int] NOT NULL,
    [SC2avg] [float],
 CONSTRAINT [PK_Science_data] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Нужно в SC2avg для каждой записи просчитать среднее в окне +/- 2 часа вокруг текущего фрейма. Вопрос не в том, _КАК_ делать, а в том, _как бы побыстрее_.
Запрос "в лоб" вида

UPDATE Science_data SET SC2avg = (SELECT AVG(SC2) FROM Science_data WHERE UTC BETWEEN DATEADD(hour, -1, UTC) AND DATEADD(hour, 1, UTC))

Выполняется неприлично долго. Т.е. просто SELECT AVG(SC2)... выполняется порядка секунды. Объемы базы — по записи в секунду (т.е. 2 часа — 7200 строк), среднее надо прописывать для каждого ряда в пределах полугода-года.

Пробовал пихать текущее окно в переменную-таблицу и работать с курсором на реальной таблице. Т.е. нечто вроде:

DECLARE @window TABLE (UTC datetime, SC2 int);
INSERT @window (UTC, SC2) (SELECT UTC, SC2 WHERE UTC BETWEEN @someStart AND @someEnd);

DECLARE sci CURSOR FOR
  SELECT UTC FROM Science_data WHERE UTC BETWEEN @A AND @B

OPEN sci;
FETCH NEXT FROM sci INTO @curUTC;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @avg = AVG(SC2) FROM @window WHERE UTC between DATEADD(hour, -1, @curUTC) AND DATEADD(hour, 1, @curUTC)
  UPDATE Science_data SET SCavg = @avg WHERE CURRENT OF sci;
  FETCH NEXT FROM sci INTO @curUTC;
END

CLOSE sci;
DEALLOCATE sci;

Там есть ещё попсации по обновлению содержимого самой таблицы @window (я её обновляю раз в час, чтобы не каждый раз в цикле), но опять же — запрос SELECT @avg = AVG(SC2) FROM @window выполняется порядка 25 мс (что конечно гораздо лучше по сравнению с секундой, но) 25 мс * 86400 рядов в день = 0,6 часов на обработку 1 дня. Я конечно готов с таким жить, но очень не хочется.

Буду благодарен любым мыслям, как можно увеличить скорость... Новое железо не предлагать )

Здравствуйте, amalakhov, Вы писали:

A>Что-то зачастил я сюда... Ну да ладно.
A>Имеем:

A>

A>CREATE TABLE [dbo].[Science_data](
A>    [id] [bigint] NOT NULL,
A>    [UTC] [datetime] NOT NULL,
A>    [SC2] [int] NOT NULL,
A>    [SC2avg] [float],
A> CONSTRAINT [PK_Science_data] PRIMARY KEY CLUSTERED 
A>(
A>    [id] ASC
A>) ON [PRIMARY]
A>) ON [PRIMARY]
A>

A>Нужно в SC2avg для каждой записи просчитать среднее в окне +/- 2 часа вокруг текущего фрейма. Вопрос не в том, _КАК_ делать, а в том, _как бы побыстрее_.
A>Запрос "в лоб" вида

A>

A>UPDATE Science_data SET SC2avg = (SELECT AVG(SC2) FROM Science_data WHERE UTC BETWEEN DATEADD(hour, -1, UTC) AND DATEADD(hour, 1, UTC))
A>

A>Выполняется неприлично долго. Т.е. просто SELECT AVG(SC2)... выполняется порядка секунды. Объемы базы — по записи в секунду (т.е. 2 часа — 7200 строк), среднее надо прописывать для каждого ряда в пределах полугода-года.

я так понял индексов у тебя нет, кроме кластерного первичного ключа?

Сделай для этого варианта покрывающий индекс по UTC и SC2.

Здравствуйте, amalakhov, Вы писали:

A>Что-то зачастил я сюда... Ну да ладно.
A>Имеем:

A>

A>CREATE TABLE [dbo].[Science_data](
A>    [id] [bigint] NOT NULL,
A>    [UTC] [datetime] NOT NULL,
A>    [SC2] [int] NOT NULL,
A>    [SC2avg] [float],
A> CONSTRAINT [PK_Science_data] PRIMARY KEY CLUSTERED 
A>(
A>    [id] ASC
A>) ON [PRIMARY]
A>) ON [PRIMARY]
A>

A>Нужно в SC2avg для каждой записи просчитать среднее в окне +/- 2 часа вокруг текущего фрейма. Вопрос не в том, _КАК_ делать, а в том, _как бы побыстрее_.
A>Запрос "в лоб" вида

A>

A>UPDATE Science_data SET SC2avg = (SELECT AVG(SC2) FROM Science_data WHERE UTC BETWEEN DATEADD(hour, -1, UTC) AND DATEADD(hour, 1, UTC))
A>

A>Выполняется неприлично долго. Т.е. просто SELECT AVG(SC2)... выполняется порядка секунды. Объемы базы — по записи в секунду (т.е. 2 часа — 7200 строк), среднее надо прописывать для каждого ряда в пределах полугода-года.


DATEADD для оптимизатора — черный ящик. Вычисляй значение диапазона дат заранее и подставляй в запрос.


A>Буду благодарен любым мыслям, как можно увеличить скорость... Новое железо не предлагать )
Может проще курсором за один проход все посчитать?

Здравствуйте, Toughpheeckouse, Вы писали:

T>Здравствуйте, amalakhov, Вы писали:

T>я так понял индексов у тебя нет, кроме кластерного первичного ключа?
T>Сделай для этого варианта покрывающий индекс по UTC и SC2.

Там всё несколько сложнее, увы... SC2 — на самом деле varbinary(32), int'ы из которого в селектах достаются через substring. А во-вторых, ставить индекс на varbinary в таблицу размером в 30 млн строк... мне страшно ) И опять же, это вопрос приоритетов — это скользящее среднее не так часто нужно высчитывать, чем например добавлять туда данные, так что я лучше буду ждать по 30 минут, чем поставлю ещё один индекс... Ну — имхо есессно.

Здравствуйте, gandjustas, Вы писали:

G>DATEADD для оптимизатора — черный ящик. Вычисляй значение диапазона дат заранее и подставляй в запрос.
секунда — это как раз случай когда WHERE прописано статично (вне процедуры игрался). Если ставить DATEADD — то там всё печально, и правда

A>>Буду благодарен любым мыслям, как можно увеличить скорость... Новое железо не предлагать )
G>Может проще курсором за один проход все посчитать?
Я б с радостью, но алгоритм в голову не лезет — вроде как всё равно для каждого ряда нужно складывать все значения +/- час вокруг, а для этого либо без AVG либо без 2го курсора вроде как не обойтись?

Здравствуйте, amalakhov, Вы писали:

A>>>Буду благодарен любым мыслям, как можно увеличить скорость... Новое железо не предлагать )
G>>Может проще курсором за один проход все посчитать?
A>Я б с радостью, но алгоритм в голову не лезет — вроде как всё равно для каждого ряда нужно складывать все значения +/- час вокруг, а для этого либо без AVG либо без 2го курсора вроде как не обойтись?

Действительно, при нефиксированном количестве элементов усреднения будет два прохода.

Использовать OLAP (SSAS) для этих целей не пробовали?

Здравствуйте, amalakhov, Вы писали:

A>Там всё несколько сложнее, увы... SC2 — на самом деле varbinary(32), int'ы из которого в селектах достаются через substring.
Это печально. Возможно, на "доставание" много времени и уходит. А нормализовать не получится?

A>А во-вторых, ставить индекс на varbinary в таблицу размером в 30 млн строк... мне страшно ) И опять же, это вопрос приоритетов — это скользящее среднее не так часто нужно высчитывать, чем например добавлять туда данные, так что я лучше буду ждать по 30 минут, чем поставлю ещё один индекс... Ну — имхо есессно.

Возможно помог бы индекс по UTC и что-то типа

 select sd.id, avg(av.SC2)
   from Science_data sd
   join Science_data av 
     on av.UTC between DATEADD(hour, -1, sd.UTC) AND DATEADD(hour, 1, sd.UTC)
  where sd.UTC between <ваши полгода>
  group by sd.id

Кстати выбор кластерного индекса тоже вызывает сомнение. Доступ к отдельным отсчетам по id это частая операция?

Если таблицу трогать не хочется, можно предложить вот что. Для расчета делать выборку за полгода в отдельную таблицу с нормализацией (можно задействовать динамический SQL). На этой таблице построить индекс по UTC, сделать расчет запросом выше и обновить исходную таблицу.

Здравствуйте, amalakhov, Вы писали:

G>>Может проще курсором за один проход все посчитать?
A>Я б с радостью, но алгоритм в голову не лезет — вроде как всё равно для каждого ряда нужно складывать все значения +/- час вокруг, а для этого либо без AVG либо без 2го курсора вроде как не обойтись?

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

То есть реализовать ту недостающую оконную функцию, которую в MS посчитали не очень нужной даже в 2008 (если ничего не путаю).

Здравствуйте, wildwind, Вы писали:

W>Возможно помог бы индекс по UTC и что-то типа

В другой теме увидел, что индекс все-таки есть.

Здравствуйте, amalakhov, Вы писали:
A>Буду благодарен любым мыслям, как можно увеличить скорость... Новое железо не предлагать )
А ODBC ( ->OleDB->Ado ) не подойдет ?

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, amalakhov, Вы писали:

G>>>Может проще курсором за один проход все посчитать?
A>>Я б с радостью, но алгоритм в голову не лезет — вроде как всё равно для каждого ряда нужно складывать все значения +/- час вокруг, а для этого либо без AVG либо без 2го курсора вроде как не обойтись?

W>Можно организовать "очередь" на временной таблице, в ней держать элементы текущего окна; добавлять из курсора и удалять выбывшие. Так за один проход и посчитать.
Не получится, ибо "текущее окно" определяется не количеством элементов, а предикатом, который в вообще говоря требует еще одного прохода для вычисления нужных элементов.

W>То есть реализовать ту недостающую оконную функцию, которую в MS посчитали не очень нужной даже в 2008 (если ничего не путаю).
В SQL оконная функция не нужна вообще, SQL (как язык) работает с неупорядоченными наборами строк. Все операции, которые требуют относительного порядка элементов должны делаться в middleware.

Здравствуйте, gandjustas, Вы писали:

W>>Можно организовать "очередь" на временной таблице, в ней держать элементы текущего окна; добавлять из курсора и удалять выбывшие. Так за один проход и посчитать.
G>Не получится, ибо "текущее окно" определяется не количеством элементов, а предикатом, который в вообще говоря требует еще одного прохода для вычисления нужных элементов.

Не понял, почему не получится и для чего еще один проход.
Под временной таблицей я подразумевал табличную переменную.

G>В SQL оконная функция не нужна вообще, SQL (как язык) работает с неупорядоченными наборами строк. Все операции, которые требуют относительного порядка элементов должны делаться в middleware.
Разработчики последних версий ANSI стандарта и ведущих СУБД с вами не согласны

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, gandjustas, Вы писали:

W>>>Можно организовать "очередь" на временной таблице, в ней держать элементы текущего окна; добавлять из курсора и удалять выбывшие. Так за один проход и посчитать.
G>>Не получится, ибо "текущее окно" определяется не количеством элементов, а предикатом, который в вообще говоря требует еще одного прохода для вычисления нужных элементов.

W>Не понял, почему не получится и для чего еще один проход.
Ну попробуй алгоритм написать.


W>Под временной таблицей я подразумевал табличную переменную.
Неважно.

G>>В SQL оконная функция не нужна вообще, SQL (как язык) работает с неупорядоченными наборами строк. Все операции, которые требуют относительного порядка элементов должны делаться в middleware.
W>Разработчики последних версий ANSI стандарта и ведущих СУБД с вами не согласны
Даже стандарт 92 года далеко не всеми поддерживается, а ведущие субд как раз реализуют middleware с помощью своих расширений языка.