Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
Доброго времени суток.
Вопрос предназначет тем кто имеет опыт работы с циклическими updat'ами.
Собственно вопрос:
Существует табличка:

create table dbo.#Table1
(
id int,
account_id int,
serv_id tinyint,
sewer_id tinyint,
action_date smalldatetime
)

допустим, существует кластерный индекс (account_id, serv_id),
а так же 2 некластерных (serv_id, sewer_id) и (action_date, id).

Выполняя циклический update:
update dbo.#Table1
set
@iType= @iType + 1,
Type = @iType

не всегда получаешь Update по кластерному индексу (видимо из-за того что данные в любом случае находятся в не упорядоченном состоянии).
ВОПРОС: Как организовать циклический update, что бы он проходил 100% проходил по заданным упорядоченным полям?
7 сен 09, 06:58    [7624320]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
https://www.sql.ru/faq/faq_topic.aspx?fid=125
7 сен 09, 07:00    [7624323]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
tpg
https://www.sql.ru/faq/faq_topic.aspx?fid=125


Большое спасибо, эту ссылку видел, но ничего полезного для себя не нашел т.к. в приведенном примере индекс как таковой вообще отсутствует, а упорядоченность в куче "#t" обусловлена лишь последовательностью вставки т.е. стоит изменить последовательность вставки и весь циклический update начнет считать ерунду. Использование стандартный агрегатных функций SQL Server (в принципе как и любых других) так же недопустимо в связи с ощутимым снижением производительности (В таблице более 1 000 000 записей). Вопрос состоит в том, как правильно организовать структуру таблицы что бы циклический Update прошел по строго отсортированной последовательности. И какие еще факторы влияют на последовательность по которой пойдет циклический update.
7 сен 09, 07:32    [7624338]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
aleks2
Guest
Foxishe,

100% гарантируется только отсутствие гарантий. И это документировано в BOL.
7 сен 09, 07:37    [7624345]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
aleks2
Foxishe,

100% гарантируется только отсутствие гарантий. И это документировано в BOL.


Спасибо и за эту информацию =) Тем ни менее хотелось бы услышать что-нибудь более или менее способное помочь в решении задачи.
(Вопрос предназначен тем кто имеет опыт работы с циклическими updat'ами.)
7 сен 09, 07:46    [7624352]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Foxishe
...в приведенном примере индекс как таковой вообще отсутствует, а упорядоченность в куче "#t" обусловлена лишь последовательностью вставки т.е. стоит изменить последовательность вставки и весь циклический update начнет считать ерунду...
А он и с кластерным индексом, скорее всего, будет считать ерунду...
Можно, конечно задействовать хинты... Но, опять же, порядок документацией негарантирован.

Сообщение было отредактировано: 7 сен 09, 07:47
7 сен 09, 07:47    [7624354]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
tpg
Foxishe
...в приведенном примере индекс как таковой вообще отсутствует, а упорядоченность в куче "#t" обусловлена лишь последовательностью вставки т.е. стоит изменить последовательность вставки и весь циклический update начнет считать ерунду...
А он и с кластерным индексом, скорее всего, будет считать ерунду...
Можно, конечно задействовать хинты... Но, опять же, порядок документацией негарантирован.


create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

--Организуем кластерный индекс
create clustered index ix_#t on #t (id)

--Меняем последовательность вставки id
insert #t(id, sum1) values(3, 1.7)
insert #t(id, sum1) values(1, 1.2)
insert #t(id, sum1) values(4, -0.5)
insert #t(id, sum1) values(2, 1.5)

declare @tot_sum decimal(10,2)
set @tot_sum = 0

update #t set @tot_sum = tot_sum = @tot_sum + ISNULL(sum1, 0)
select * from #t

drop table #t
7 сен 09, 07:55    [7624363]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
И?..
7 сен 09, 08:02    [7624375]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
tpg
И?..


С кластерным индексом - Ерунду не считает
7 сен 09, 08:04    [7624376]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
В данном примере из 4х записей =)
7 сен 09, 08:06    [7624383]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Foxishe
tpg
И?..


С кластерным индексом - Ерунду не считает
А так?
create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

--Организуем кластерный индекс
create clustered index ix_#t on #t (id)

--Меняем последовательность вставки id
insert #t(id, sum1) values(3, 1.7)
insert #t(id, sum1) values(1, 1.2)
insert #t(id, sum1) values(4, -0.5)
insert #t(id, sum1) values(2, 1.5)

declare @tot_sum decimal(10,2)
set @tot_sum = 0

update
	t
set @tot_sum = tot_sum = @tot_sum + ISNULL(sum1, 0)
from #t t with(index(0))
select * from #t

drop table #t
7 сен 09, 08:09    [7624388]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Владимир СА
Member

Откуда:
Сообщений: 7825
А без индекса как SandalTree показал.
create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

insert #t(id, sum1) values(3, 1.7)
insert #t(id, sum1) values(1, 1.2)
insert #t(id, sum1) values(4, -0.5)
insert #t(id, sum1) values(2, 1.5)

select * FROM #t order by id

UPDATE t 
set tot_sum = a.tot_sum
FROM #t t
inner join (
select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
from #t a 
left outer join #t b on b.id < a.id
group by a.id
) as a on t.ID = a.ID 

select * FROM #t order by id

drop table #t
7 сен 09, 08:12    [7624392]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
tpg,

Попробовал явно указать какой индекс использовать при циклическом update'те. На малых объемах данных отработал нормально. Альтернативным решением было перелить все данные в промежуточную табличку с одним кластерным индексом и там свершить злодеяния циклическим update'ом, затем вернуть данные в исходную табличку с несколькими индексами (Как показывает практика, в плане надежности, этот способ наиболее эффективен). Но падение производительности в данном случае заставило прибегнуть к поиску более оптимального решения!
Сейчас запустил на большом объеме данных...

Результат будет чесов через 5 =) ждемс господа ...
7 сен 09, 08:55    [7624468]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
Владимир СА
А без индекса как SandalTree показал.
create table #t(id int, sum1 decimal(10,2), tot_sum decimal(10,2))

insert #t(id, sum1) values(3, 1.7)
insert #t(id, sum1) values(1, 1.2)
insert #t(id, sum1) values(4, -0.5)
insert #t(id, sum1) values(2, 1.5)

select * FROM #t order by id

UPDATE t 
set tot_sum = a.tot_sum
FROM #t t
inner join (
select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
from #t a 
left outer join #t b on b.id < a.id
group by a.id
) as a on t.ID = a.ID 

select * FROM #t order by id

drop table #t


Использование функций на большом объеме данных - смерти подобно! По крайней мере в контексте той задачи, которую я пытаюсь решить. Но все ровно спасибо за Ваш ответ.
7 сен 09, 08:58    [7624477]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Foxishe
...Но падение производительности в данном случае заставило прибегнуть к поиску более оптимального решения!
В факе, в комментариях упоминался способ с курсором...
Может стать, что этот способ окажется самым быстрым.

Сообщение было отредактировано: 7 сен 09, 09:07
7 сен 09, 09:07    [7624497]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Владимир СА
Member

Откуда:
Сообщений: 7825
Foxishe
Использование функций на большом объеме данных - смерти подобно! По крайней мере в контексте той задачи, которую я пытаюсь решить. Но все ровно спасибо за Ваш ответ.
Под функциями я понял ты подразумеваешь SUM, ISNULL, MAX ?
Ты попробуй запрос типа:
select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
from #t a 
left outer join #t b on b.id < a.id
group by a.id
и посмотри план выполнения на миллионе и более записей. Построй необходимые индексы. В данном примере точно нужен индекс по id.
7 сен 09, 09:18    [7624533]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
Владимир СА
Foxishe
Использование функций на большом объеме данных - смерти подобно! По крайней мере в контексте той задачи, которую я пытаюсь решить. Но все ровно спасибо за Ваш ответ.
Под функциями я понял ты подразумеваешь SUM, ISNULL, MAX ?
Ты попробуй запрос типа:
select a.id, max(a.sum1) as sum1, sum(isnull(b.sum1, 0))+max(a.sum1) as tot_sum
from #t a 
left outer join #t b on b.id < a.id
group by a.id
и посмотри план выполнения на миллионе и более записей. Построй необходимые индексы. В данном примере точно нужен индекс по id.


Это всего лишь пример из фака, моя задача это лишь кусочек из хранимки, которую вызывает хранимка, которую ... (и т.д. в зависимости от условий) ... которую вызывает хранимка, в которой необходимо проапдейтить поле циклическим update'ом т.к. этот метод показал наибольшую эффективность. Создание, пардон за мой французский, туевой хучи индексов как раз таки ведет к неадекватному поведению циклического update'та. В контексте этого топика мне был интересен опыт других людей в реализации именно этого метода решения задачи.
7 сен 09, 09:36    [7624600]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
aleks2
Guest
Foxishe,

Ну... вот пожалуй единственный ДОКУМЕНТИРОВАННЫЙ способ для sql2000

declare @t table (iType int identity, id int primary key clustered)
insert @t(id)
select id from dbo.#Table1
ORDER BY <чо хотите>  -- порядок вставки гарантирован BOL.

update T
set Type = @iType+x.iType
from dbo.#Table1 T INNER JOIN @t x on T.id=x.id
для 2005 можно задействовать rownumber.
7 сен 09, 09:53    [7624680]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Foxishe
Member

Откуда:
Сообщений: 15
Всем огромное спасибо за участие.
Наиболее приемлимым показалось решение: циклический update с явным указанием кластерного индекса в качестве используемого.
8 сен 09, 12:33    [7630914]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
PokeMan
Member

Откуда: MOSKAU
Сообщений: 312
Автору настоятельно рекомендую прошерстить форум на предмет обсуждения конструкции update с переменной (циклический update), а точнее поискать пример некорректной работы этой конструкции на больших объемах данных.

Лично столкнулся с проблемой некорректной работы данного способа (правда немного в другом контексте). Там же была ссылка на обсуждение этого вопроса на буржуйском сайте Microsoft.
8 сен 09, 18:44    [7633568]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
PokeMan
Автору настоятельно рекомендую прошерстить форум на предмет обсуждения конструкции update с переменной (циклический update), а точнее поискать пример некорректной работы этой конструкции на больших объемах данных.

Собственно, и на маленьком иногда может работать неправильно:
я бы сказал, совсем неправильно
8 сен 09, 22:13    [7634232]     Ответить | Цитировать Сообщить модератору
 Re: Циклический update (или update с нарастающим результатом)  [new]
aleks2
Guest
Roman S. Golubin
PokeMan
Автору настоятельно рекомендую прошерстить форум на предмет обсуждения конструкции update с переменной (циклический update), а точнее поискать пример некорректной работы этой конструкции на больших объемах данных.

Собственно, и на маленьком иногда может работать неправильно:
я бы сказал, совсем неправильно


Это надо САМОМУ тредстартеру лоб разбить...
А так, можно только посочувствовать пользователям его опуса...
9 сен 09, 07:17    [7634861]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить