ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Большой UPDATE
23 сообщений из 48, страница 2 из 2
  2  все
Большой UPDATE
    #40026410
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
alexeyvg,

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

Вы говорите "нужно считать каждую неделю с учетом предыдущих данных", а в запросе вы делаете другое - считаете все следующие записи по текущей записи, потом считаете все следующие записи по следующей записи, и т.д.
Это очень сильно отличается.

"нужно считать каждую неделю с учетом предыдущих данных" в какой то степени похоже на "накопительный итог", это можно считать, не апдэйтя данные за всё время столько раз, сколько строк в таблице ,а за один проход.
И, конечно, не апдэйтя таблицу, а вставляя в новую.
Тогда у вас общее время пересчёта по сути будет равно одному скану таблицы (правда, будут затраты памяти, для хранения накопительных счётчиков, по числу сочетаний StoreID и GoodID)
То есть в тыщи раз быстрее, чем сейчас.
...
Рейтинг: 0 / 0
10.12.2020, 15:23
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026431
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg,

alexeyvg,

Запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
             UPDATE [snp]
               SET 
                   [DevReg] =   IIF([snp].[Price] = 0, 0, ROUND(([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price], 2)), 
                   [DevPromo] = IIF([snp].[Price] = 0, 0, ROUND(([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price], 2)), 
                   [isPromo] =  IIF([snp].[Price] <> 0
                                    AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                    AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								1, 0), 
                   [PriceReg] = IIF([snp].[Price] <> 0
                                    AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                    AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								 [snpPrev].[PriceReg], [snp].[Price]), 
                   [PricePromo] = IIF([snp].[Price] <> 0
                                     AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                     AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								  [snp].[Price], [snpPrev].[PricePromo])
             FROM [#salesPromo] AS [snp]
             -- Поиск значений предыдущей записи
                  INNER JOIN [#salesPromo] AS [snpPrev] ON 1 = 1
                                  AND [snp].[dt] > [snpPrev].[dt]
                                  AND [snpPrev].[dt] = @dt
                                  AND [snp].[StoreID] = [snpPrev].[StoreID]
                                  AND [snp].[GoodID] = [snpPrev].[GoodID]
								  AND snp.ChildGoodID = snpPrev.ChildGoodID
             WHERE [snp].[dt] BETWEEN @dtnext AND @curperiodend



В идеале вместо WHERE [snp].[dt] BETWEEN @dtnext AND @curperiodend нужно WHERE [snp].[dt] =@dtnext
Т.е. брать данные одной недели и апдейтить их данными предыдущей.
Но на предыдущей неделе может не быть данных по товару - они были 5/6/12... недель назад. Поэтому делается апдейт всего что после текущей (@dt), а как дойдет до недели где опять есть этот товар, то по нему проапдейтится сново все далее...

Одним запросом это сделать - не могу сообразить как.
Ведь данные по каждой неделе постоянно меняются и 10 неделя до начала расчета будет не той как после 2, 3 и тп шагов...

По идее вот я получил данные по товару Х за 1 неделю и мне нужно не все со 2-й по 50-ю неделю обновлять, а только первую неделю где есть этот товар. Лишь в этом я вижу возможность уменьшить число строк для обновления.
Т.е. нечто типа OUTER APPLY (SELECT TOP 1 ... )
...
Рейтинг: 0 / 0
10.12.2020, 15:54
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026454
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
Сделал так, чтобы наоборот для выбранной недели находились прошлые данные

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
    UPDATE [snp]
               SET 
                   [DevReg] =   IIF([snp].[Price] = 0, 0, ROUND(([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price], 2)), 
                   [DevPromo] = IIF([snp].[Price] = 0, 0, ROUND(([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price], 2)), 
                   [isPromo] =  IIF([snp].[Price] <> 0
                                    AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                    AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								1, 0), 
                   [PriceReg] = IIF([snp].[Price] <> 0
                                    AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                    AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								 [snpPrev].[PriceReg], [snp].[Price]), 
                   [PricePromo] = IIF([snp].[Price] <> 0
                                     AND ([snp].[Price] - [snpPrev].[PriceReg]) / [snp].[Price] < -0.11
                                     AND ([snp].[Price] - [snpPrev].[PricePromo]) / [snp].[Price] < 0.23, 
								  [snp].[Price], [snpPrev].[PricePromo])
             FROM [#salesPromo] AS [snp]

            OUTER APPLY (SELECT TOP 1 [dt], [StoreID], [GoodID], [ChildGoodID], 
				                  ISNULL([Price], 0) [Price],ISNULL([DevReg], 0) [DevReg],
						  ISNULL([DevPromo], 0) [DevPromo],
                                                  ISNULL([isPromo], 0) [isPromo],
						  ISNULL([PriceReg], 0) [PriceReg],
						  ISNULL([PricePromo], 0) [PricePromo] 				  
				  FROM [#salesPromo] AS b 
                                  WHERE 1 = 1
                                            AND b.[dt] >= @startweek AND b.[dt] < [snp].[dt]
                                            AND [snp].[StoreID] = b.[StoreID]
                                            AND [snp].[GoodID] = b.[GoodID]
				           AND snp.ChildGoodID = b.ChildGoodID
				  ORDER BY b.Dt DESC
                                ) [snpPrev]
             WHERE [snp].[dt] = @dt and [snpPrev].dt IS NOT NULL



Стало медленнее раза в 3.
В добавок, OUTER APPLY при больших объемах у нас уже вызывал полное зависание (проц = 0%, диск = 0% и тп) при этом ошибок не давал.
...
Рейтинг: 0 / 0
10.12.2020, 16:46
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026487
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
автор OUTER APPLY (SELECT TOP 1 ...)
да по куче, да с таким количеством предикатов...
...
Рейтинг: 0 / 0
10.12.2020, 17:54
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026506
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Oleg_SQL,

OUTER APPLY ... order by

откуда там взяться производительности.
...
Рейтинг: 0 / 0
10.12.2020, 18:35
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026520
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
Одним запросом это сделать - не могу сообразить как.
Функция
LAG (Price) OVER ( [ partition by StoreID, GoodID order by dt) вернёт вам значение Price для тех же StoreID и GoodID за предыдущий день
...
Рейтинг: 0 / 0
10.12.2020, 18:53
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026529
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
Стало медленнее раза в 3.
В добавок, OUTER APPLY при больших объемах у нас уже вызывал полное зависание (проц = 0%, диск = 0% и тп) при этом ошибок не давал.
И при этом был кластерный индекс на dt, StoreID, GoodID, ChildGoodID? (хотя бы на первые 2-3 поля)

Без индекса это неприемлемо (как и вариант с LAG)
...
Рейтинг: 0 / 0
10.12.2020, 19:04
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026532
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
затраты по времени на построение индекса иногда съедают всю выгоду от них (не менее часа, думаю будет длится)
Индексы должны быть в базе.
Не для расчёта, а вообще, и не надо их строить-удалять
...
Рейтинг: 0 / 0
10.12.2020, 19:06
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026690
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg
Oleg_SQL
Стало медленнее раза в 3.
В добавок, OUTER APPLY при больших объемах у нас уже вызывал полное зависание (проц = 0%, диск = 0% и тп) при этом ошибок не давал.
И при этом был кластерный индекс на dt, StoreID, GoodID, ChildGoodID? (хотя бы на первые 2-3 поля)

Без индекса это неприемлемо (как и вариант с LAG)


Я конечно не профессор, но кластерный индекс был построен по всем указанным полям.
В данный момент я добавил при вставке во временную таблицу поле rn (row_number) и добавил его в кластерный индекс.
Теперь добавил условие в INNER JOIN : AND snp.RN = snpPrev.RN + 1
Обработка взлетела ))
Вот только много времени уходит на саму вставку в #salesPromo со всеми индексами и row_number(OVER(PARTITION BY... ORDER BY...).
...
Рейтинг: 0 / 0
11.12.2020, 09:13
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026739
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg
Oleg_SQL
Одним запросом это сделать - не могу сообразить как.
Функция
LAG (Price) OVER ( [ partition by StoreID, GoodID order by dt) вернёт вам значение Price для тех же StoreID и GoodID за предыдущий день



Код: sql
1.
2.
3.
4.
DECLARE @T TABLE (dt int, val int)
INSERT @T VALUES (20200101, 100), (20200108, NULL), (20200115, NULL), (20200122, 120)
SELECT *, LAG(Val) OVER(ORDER BY dt) as lastvalue
FROM @T



dt val lastvalue
----------- ----------- -----------
20200101 100 NULL
20200108 NULL 100
20200115 NULL NULL
20200122 120 NULL

а мне нужно для 20200122 значение lastvalue = 100. Последнее не пустое значение , а не значение прошлой строки.
...
Рейтинг: 0 / 0
11.12.2020, 11:26
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026743
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg
Oleg_SQL
затраты по времени на построение индекса иногда съедают всю выгоду от них (не менее часа, думаю будет длится)
Индексы должны быть в базе.
Не для расчёта, а вообще, и не надо их строить-удалять


я же в расчете использую временную таблицу... как им там быть то?
...
Рейтинг: 0 / 0
11.12.2020, 11:31
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026776
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Oleg_SQL,

вы индекс строите после заполнения таблицы или до?
...
Рейтинг: 0 / 0
11.12.2020, 12:36
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026813
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
felix_ff
Oleg_SQL,

вы индекс строите после заполнения таблицы или до?


Конечно же ДО
...
Рейтинг: 0 / 0
11.12.2020, 13:39
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026819
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Oleg_SQL,

В таком случае что там у вас с планом запроса наполнения временной таблицы? есть ли оператор сортировки?

Для такого некислого объема вставка по определению может быть не особо быстрой.
...
Рейтинг: 0 / 0
11.12.2020, 13:47
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026831
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
felix_ff
Oleg_SQL,

В таком случае что там у вас с планом запроса наполнения временной таблицы? есть ли оператор сортировки?

Для такого некислого объема вставка по определению может быть не особо быстрой.


Со вставкой уже ничего не поделаешь - манипулировать с рабочей таблицей (индексы и тп) мне не дают.
...
Рейтинг: 0 / 0
11.12.2020, 14:17
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026862
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
а мне нужно для 20200122 значение lastvalue = 100. Последнее не пустое значение , а не значение прошлой строки.
Подождите, вы же в последнем запросе ищите именно "значение прошлой строки", и не "Последнее не пустое значение "
Код: sql
1.
2.
3.
4.
5.
OUTER APPLY (SELECT TOP 1 [dt], [StoreID], [GoodID], [ChildGoodID], 
...
	FROM [#salesPromo] AS b 
...
	ORDER BY b.Dt DESC
...
Рейтинг: 0 / 0
11.12.2020, 15:09
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026866
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
alexeyvg
пропущено...
Индексы должны быть в базе.
Не для расчёта, а вообще, и не надо их строить-удалять


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

Другое дело, что схема исходных таблиц сложнее, и по ней не получится так легко сделать интересующий сложный запрос.
...
Рейтинг: 0 / 0
11.12.2020, 15:12
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026877
0wl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
0wl
Гость
Oleg_SQL,

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

Если вы построите индекс по существующим данным, вместе с индексом построится актуальная статистика. А если наоборот - останется только надеяться на автопересчет статистик.
...
Рейтинг: 0 / 0
11.12.2020, 15:28
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026880
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
[quot alexeyvg#22246312]
Oleg_SQL
а мне нужно для 20200122 значение lastvalue = 100. Последнее не пустое значение , а не значение прошлой строки.
Подождите, вы же в последнем запросе ищите именно "значение прошлой строки", и не "Последнее не пустое значение "

Да, всё верно. Я уже заметил и поправил. На скорость это не повлияло.
Но это был тестовый запрос для успокоения души ))
...
Рейтинг: 0 / 0
11.12.2020, 15:33
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026884
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
0wl
Oleg_SQL,

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

Если вы построите индекс по существующим данным, вместе с индексом построится актуальная статистика. А если наоборот - останется только надеяться на автопересчет статистик.



Записать 500 млн записей, а потом начать строить кластерный индекс? Это будет реально быстрее?
...
Рейтинг: 0 / 0
11.12.2020, 15:39
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026979
0wl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
0wl
Гость
Oleg_SQL



Записать 500 млн записей, а потом начать строить кластерный индекс? Это будет реально быстрее?


Я бы и рад сказать «да, я был прав» или «нет, я ошибся», но тут возможны варианты.

Если сначала залить данные в кучу, а потом построить по ним индекс, они запишутся 2 раза. И нужно будет выделить место в файле данных для создания индекса. Зато можно использовать BULK INSERT (ну или SELECT … INTO) и ускорить первоначальную вставку в кучу. (см. https://docs.microsoft.com/en-US/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver15#table-requirements-for-minimally-logging-bulk-import-operations)

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

В общем, я бы замерил варианты. Ну и если выбирать второй вариант, то по окончании заливки пересчитал бы статистику с FULLSCAN, чтобы гарантировать, что план будет эффективным.
...
Рейтинг: 0 / 0
11.12.2020, 17:50
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40027013
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
0wl
Зато можно использовать BULK INSERT (ну или SELECT … INTO) и ускорить первоначальную вставку в кучу.
С минимальным логированием можно и не в кучу записывать.
Но надо одной операцией, а не "пачками".
...
Рейтинг: 0 / 0
11.12.2020, 19:17
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40027015
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
Да, всё верно. Я уже заметил и поправил. На скорость это не повлияло.
Но это был тестовый запрос для успокоения души ))
А вот тут вы тогда тоже ошиблись?
Oleg_SQL
В идеале вместо WHERE [snp].[dt] BETWEEN @dtnext AND @curperiodend нужно WHERE [snp].[dt] =@dtnext
Т.е. брать данные одной недели и апдейтить их данными предыдущей.
Но на предыдущей неделе может не быть данных по товару - они были 5/6/12... недель назад. Поэтому делается апдейт всего что после текущей (@dt), а как дойдет до недели где опять есть этот товар, то по нему проапдейтится сново все далее...

Как я понял, у вас отсутствие данных означает не наличие записи во значением поля Price, равным NULL, а именно отсутствие записи.

Если "отсутствие данных" означает "отсутствие записи", то цикл апдэйтов заменяется на рекурсивный CTE по датам, с заменой OUTER APPLY на LAG, и на итоговую вставку в таблицу результата, соответственно, с возможностью использовать индексы постоянных таблиц

Тогда всё будет кардинально быстрее.

Ну или вообще нужно менять модель данных, на что выше намекали, но на это вы вряд ли пойдёте, это уже не "оптимизация", а "перепроектирование системы".
...
Рейтинг: 0 / 0
11.12.2020, 19:28
| Ответить | Цитировать | Написать  
23 сообщений из 48, страница 2 из 2
  2  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Большой UPDATE
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]