ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Большой UPDATE
25 сообщений из 48, страница 1 из 2
  1  все
Большой UPDATE
    #40026026
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
Всем добра.

Имеется процедура, в которой создается и наполняется временная таблица, которая затем "апдейтится".

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
     CREATE TABLE [#sales]
     (
      [dt]          [INT], 
      [StoreID]     [INT], 
      [GoodID]      [INT], 
      [Price]       [FLOAT], 
     );
...
WHILE .... (цикл по датам понедельников за год - около 50)
  UPDATE A
     SET Price = a.Price - b.Price
     FROM [#sales] A
    INNER JOIN [#sales] B ON a.StoreID = b.StoreID AND a.GoodID = b.GoodID AND b.dt = @dt
   WHERE a.dt > b.dt



Приведены не все поля и формула (SET) естественно более сложная, не суть.

В таблице порядка 500 млн строк. Каждый шаг порядка 15 минут (около 200 млн строк затрагивается)
Попробовал добавить индексы StoreID и GoodID - заметного прироста не получилось, а вот на построение индекса времени ушло...

Вопрос - поможет ли в данном случае добавление ID INT IDENTITY(1,1) PRIMARY KEY?
Или нужно дробить запросы на более мелкие (по StoreID или GoodID)
...
Рейтинг: 0 / 0
09.12.2020, 17:06
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026041
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
Oleg_SQLВопрос - поможет ли в данном случае добавление ID INT IDENTITY(1,1) PRIMARY KEY?
У вас нет кластерного индекса на таблице?

Сначала стоит определиться, у вас соединение долго выполняется или только обновление. Сделайте SELECT вместо UPDATE.
Вместо цикла почему не соединяться с календариком понедельников? Это будет одно выполнение вместо десятков.

А вообще не очень понятен INNER JOIN таблицы самой с собой по одним и тем же полям. Какой тут смысл, кроме дублирующихся полей и фильтра по дате?

Код: sql
1.
2.
SELECT * FROM [#sales] A
    INNER JOIN [#sales] B ON a.StoreID = b.StoreID AND a.GoodID = b.GoodID AND b.dt = @dt
...
Рейтинг: 0 / 0
09.12.2020, 17:32
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026045
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
PizzaPizza,

в настоящий момент это куча.
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
формула сложная (нужна для дальнейшего анализа).
Подключиться к календарю нельзя, т.к. требуется сначала обновить данные с учетом 1-й недели, а ЗАТЕМ по 2-й, ЗАТЕМ по 3-й и тд
Данные с каждой итерацией меняются.
...
Рейтинг: 0 / 0
09.12.2020, 17:38
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026063
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Oleg_SQL
PizzaPizza,

в настоящий момент это куча.
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
формула сложная (нужна для дальнейшего анализа).
Подключиться к календарю нельзя, т.к. требуется сначала обновить данные с учетом 1-й недели, а ЗАТЕМ по 2-й, ЗАТЕМ по 3-й и тд
Данные с каждой итерацией меняются.


Увы и ах. Этот бред ~50 раз напрасно перелопачивает "500 млн строк".

Код: sql
1.
2.
3.
4.
5.
UPDATE A
     SET Price = a.Price - b.Price
     FROM [#sales] A
    INNER JOIN [#sales] B ON a.StoreID = b.StoreID AND a.GoodID = b.GoodID AND b.dt = @dt
   WHERE a.dt > b.dt



Пойдите учиться на курсы "SQL для чайников".
...
Рейтинг: 0 / 0
09.12.2020, 18:11
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026066
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Oleg_SQL
PizzaPizza,

в настоящий момент это куча.
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
формула сложная (нужна для дальнейшего анализа).
Подключиться к календарю нельзя, т.к. требуется сначала обновить данные с учетом 1-й недели, а ЗАТЕМ по 2-й, ЗАТЕМ по 3-й и тд
Данные с каждой итерацией меняются.


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

индекс по storeId и GooID делать бессмысленно у вас нет такого предиката позволяющего отсечь из выборки превалирующий объем строк. как мне кажется индекс по dt, StoreId, GoodID больше подходит в этом плане
...
Рейтинг: 0 / 0
09.12.2020, 18:20
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026070
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
Может, тогда нужно сделать кластерный индекс по dt? (Возможно, лучше по dt, StoreID, GoodID)
Отдельные индексы по StoreID, GoodID смысла не имеют.
...
Рейтинг: 0 / 0
09.12.2020, 18:33
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026071
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
alexeyvg
Oleg_SQL
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
Может, тогда нужно сделать кластерный индекс по dt? (Возможно, лучше по dt, StoreID, GoodID)
Отдельные индексы по StoreID, GoodID смысла не имеют.


Консилиум у постели мертворожденного?

ЗЫ. Прежде чем индексы строгать - запросы надо писать научиться.
...
Рейтинг: 0 / 0
09.12.2020, 18:37
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026082
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Oleg_SQL,

правильно ли я понял
вы взяли цену в первый понедельник и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
затем вы взяли цену во второй понедельник (судя по всему, уже изменённую) и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
...
Рейтинг: 0 / 0
09.12.2020, 18:57
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026088
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
andreymx
правильно ли я понял
вы взяли цену в первый понедельник и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
затем вы взяли цену во второй понедельник (судя по всему, уже изменённую) и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
Oleg_SQL
Приведены не все поля и формула (SET) естественно более сложная
...
Рейтинг: 0 / 0
09.12.2020, 19:02
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026091
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
Вопрос - поможет ли в данном случае добавление ID INT IDENTITY(1,1) PRIMARY KEY?
Или нужно дробить запросы на более мелкие (по StoreID или GoodID)
А может, лучше одним запросом, без цикла, перелить данные в другую таблицу, сделав все расчёты, а потом сделать переименование?
...
Рейтинг: 0 / 0
09.12.2020, 19:04
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026145
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
alexeyvg
andreymx
правильно ли я понял
вы взяли цену в первый понедельник и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
затем вы взяли цену во второй понедельник (судя по всему, уже изменённую) и отняли ее у всех строк с таким StoreID и GoodID с более поздней датой
Oleg_SQL
Приведены не все поля и формула (SET) естественно более сложная
пока непонятна цель расчетов - советы бесполезны
...
Рейтинг: 0 / 0
09.12.2020, 21:59
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026250
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg
А может, лучше одним запросом, без цикла, перелить данные в другую таблицу, сделав все расчёты, а потом сделать переименование?


так данные и перелиты в отдельную временную таблицу [#sales]
...
Рейтинг: 0 / 0
10.12.2020, 09:08
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026251
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
aleks222
Oleg_SQL
PizzaPizza,

в настоящий момент это куча.
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
формула сложная (нужна для дальнейшего анализа).
Подключиться к календарю нельзя, т.к. требуется сначала обновить данные с учетом 1-й недели, а ЗАТЕМ по 2-й, ЗАТЕМ по 3-й и тд
Данные с каждой итерацией меняются.


Увы и ах. Этот бред ~50 раз напрасно перелопачивает "500 млн строк".

Код: sql
1.
2.
3.
4.
5.
UPDATE A
     SET Price = a.Price - b.Price
     FROM [#sales] A
    INNER JOIN [#sales] B ON a.StoreID = b.StoreID AND a.GoodID = b.GoodID AND b.dt = @dt
   WHERE a.dt > b.dt



Пойдите учиться на курсы "SQL для чайников".


Научитесь ВНИМАТЕЛЬНО читать для начала.
Приведены не все поля и формула (SET) естественно более сложная, не суть.
...
Рейтинг: 0 / 0
10.12.2020, 09:12
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026256
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg
Oleg_SQL
смысл цикла что то вроде накопления - берутся данные по неделе и обновляются более поздние данные и так далее...
Может, тогда нужно сделать кластерный индекс по dt? (Возможно, лучше по dt, StoreID, GoodID)
Отдельные индексы по StoreID, GoodID смысла не имеют.



Спасибо, попробую.
Почему собственно вопрос то и возник - затраты по времени на построение индекса иногда съедают всю выгоду от них (не менее часа, думаю будет длится). И одной только теорией, без пробы не обойтись.
...
Рейтинг: 0 / 0
10.12.2020, 09:36
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026262
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Oleg_SQL
alexeyvg
А может, лучше одним запросом, без цикла, перелить данные в другую таблицу, сделав все расчёты, а потом сделать переименование?


так данные и перелиты в отдельную временную таблицу [#sales]
Так и не надо там их апдэйтить. Переливайте ещё в одну таблицу.
Или сразу заливайте в эту с расчётом, чего бы сразу не посчитать правильно, зачем потом корректировать?
Oleg_SQL
Спасибо, попробую.
Почему собственно вопрос то и возник - затраты по времени на построение индекса иногда съедают всю выгоду от них (не менее часа, думаю будет длится). И одной только теорией, без пробы не обойтись.
Тут вопрос, во первых, в самой операции апдэйта - это нелёгкая операция для сервера, во вторых, в массивном и многократном джойне таблицы самой с собой - зачем апдэйтить много раз, если можно посчитать сразу? Вы сколько раз обновляете таблицу, столько, сколько дней за всё время??? Это же ужас.

Другое дело, что, возможно, логически вычисления очень непростые, и сложно их переделать на один проход.
Но другого выхода нет, придётся поскрипеть мозгами, ибо такой апдэйт - это трэш :-)
...
Рейтинг: 0 / 0
10.12.2020, 09:56
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026266
L_argo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
L_argo
Участник
Никакие индексы-шминдексы не помогут, ИМХО.
Реально ускориться можно одним способом - как-то реорганизовать работу с данными.

Н-р где-то отдельно делать расчеты, а потом накатывать готовые результаты на сабжевую таблицу.
...
Рейтинг: 0 / 0
10.12.2020, 10:21
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026273
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
alexeyvg,


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

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

Можно менять данные не для всего периода, а только для конкретной недели - это все будет на порядок быстрее.
Как для 500 млн товаров (по точкам) проапдейтить данные на основе последних показателей по каждому я знаю. Но конструкция выборки этих последних данных выглядит более зловеще )))
В любом случае придется попробовать и его.
...
Рейтинг: 0 / 0
10.12.2020, 10:36
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026276
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Oleg_SQL,

У вашего подхода есть две проблемы:
1. Из-за отсутствия ограничений/индексов оптимизатор считает, что одна и та же строка может быть обновлена несколько раз. Поэтому в план будет включено исключение таких дубликатов.
2. Такой update предполагает наличие halloween problem. Соответственно в план будет включена защита, скорее всего в виде Eager Table Spool

Если критерий уникальности - (dt, StoreID, GoodID), то сделайте у #sales PK по этим столбцам, dt обязательно должен быть первым.
Тогда первая проблема отпадет.
Для избавления от второй, предварительно отбирайте строки по dt = @dt во временную таблицу и соответствующим образом перепишите update.
...
Рейтинг: 0 / 0
10.12.2020, 10:46
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026278
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Для таких алгоритмов надо где-то хранить эти промежуточные данные и считать каждый месяц, например
Рассчитывать их каждый раз - моветон
...
Рейтинг: 0 / 0
10.12.2020, 10:48
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026283
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
andreymx
Для таких алгоритмов надо где-то хранить эти промежуточные данные и считать каждый месяц, например
Рассчитывать их каждый раз - моветон


Я это все прекрасно понимаю. Но вот данные меняют задним числом (приходят корректировки и тп).
Это в 1С есть понятие закрытий период. В данной системе такого нет. Пересчет за 2 месяца выполняется 1 раз в месяц - не так критично.
...
Рейтинг: 0 / 0
10.12.2020, 10:59
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026289
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Oleg_SQL,

а как вы бухгалтерскую отчетность поддерживаете? Я понимаю, что так поступали в 90х, но сейчас без закрытия периода никак нельзя.
...
Рейтинг: 0 / 0
10.12.2020, 11:25
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026298
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
Владислав Колосов
Oleg_SQL,

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


Это данные не для строгой отчетности.
...
Рейтинг: 0 / 0
10.12.2020, 11:49
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026312
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Олег
А сам расчёт работает корректно, Но медленно?
...
Рейтинг: 0 / 0
10.12.2020, 12:17
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026314
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
andreymx
Олег
А сам расчёт работает корректно, Но медленно?
дождался кто-нибудь окончания? :)
...
Рейтинг: 0 / 0
10.12.2020, 12:17
| Ответить | Цитировать | Написать  
Большой UPDATE
    #40026322
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg_SQL
Гость
andreymx
andreymx
Олег
А сам расчёт работает корректно, Но медленно?
дождался кто-нибудь окончания? :)


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

P.S. Кластерный индекс строится уже более часа...
...
Рейтинг: 0 / 0
10.12.2020, 12:35
| Ответить | Цитировать | Написать  
25 сообщений из 48, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Большой UPDATE
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=39&tid=1685305]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 35ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 284ms
total:  426ms
созд. / загр.: 426ms
x
x
Закрыть


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