Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите плиз написать самый эффективный апдейт.  [new]
abrashka
Member

Откуда:
Сообщений: 521
День добрый!
Есть относительно большая таблица транзакций(более 20 миллионов строк), скажем:
create table #t
(id int, serverid int, closedate datetime, customerid int, flag int)

insert into #t
select 1,1,'20130612',999,0 union
select 2,1,'20130601',999,0 union
select 1,2,'20130612',333,0 union
select 3,1,'20130612',999,0 union
select 5,1,'20130612',999,0 union
select 6,1,'20130612',333,0 union
select 7,1,'20130612',999,0 union
select 3,2,'20130612',333,0 union
select 4,2,'20130508',333,0 union
select 10,2,'20130312',333,0 

Есть кластеризованый индекс id+serverid+closetime

Нужно как можно эффективно проапдейтить поле flag=1 у каждой первой(по closedate) транзакции клиента.

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

По быстрому сделал так:
with FIRST_CTE
as
(
select customerid, serverid, MIN(closedate)as closedate
from #t
group by customerid, serverid
)
update #t
set flag=1
from #t a, FIRST_CTE m
where a.customerid=m.customerid
and a.serverid=m.serverid
and a.closedate=m.closedate


Но работает крайне медленно, помогите плз написать более эффективный скрипт.

Спасибо!
26 июн 13, 16:33    [14487779]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
яб при таком размере вместо цте сделал бы еще одну времную таблицук с индексом по ней ,и патом применил бы ее как цте
26 июн 13, 16:36    [14487792]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Версия сервера осталась неизвестной
26 июн 13, 16:38    [14487815]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
может так
;with forUpdate as(
select top 1 with ties
flag
,newFlag =1 
from #t
order by row_number() over (partition by customerid,serverid order by closedate asc)
)
update forUpdate set flag= newFlag
26 июн 13, 16:39    [14487823]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Сорри:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
26 июн 13, 16:41    [14487842]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
В качестве сырой идеи:
WITH CTE(N,flag) AS(SELECT ROW_NUMBER()OVER(PARTITION BY serverid,customerid ORDER BY closedate),flag FROM #t)
UPDATE CTE SET flag=1;
Но насчёт эффективности совсем не уверен
26 июн 13, 16:42    [14487845]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
за скорость ничего не скажу, просто вопрос - уникально ли closedate в пределах customerid и serverid ?
26 июн 13, 16:43    [14487857]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
В качестве сырой идеи:
WITH CTE(N,flag) AS(SELECT ROW_NUMBER()OVER(PARTITION BY serverid,customerid ORDER BY closedate),flag FROM #t)
UPDATE CTE SET flag=1;

Но насчёт эффективности совсем не уверен
WHERE забыл:
WITH CTE(N,flag) AS(SELECT ROW_NUMBER()OVER(PARTITION BY serverid,customerid ORDER BY closedate),flag FROM #t)
UPDATE CTE SET flag=1 WHERE N=1;
26 июн 13, 16:44    [14487860]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Всем спасибо, буду пробовать.

Паганель,

Да, уникально
26 июн 13, 16:45    [14487870]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
abrashka
Да, уникально
тогда row_number
26 июн 13, 16:47    [14487888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
UPDATE T
SET T.flag=1
FROM #t T
WHERE NOT EXISTS
(
 SELECT *
 FROM #t TT
 WHERE TT.serverid=T.serverid AND TT.customerid=T.customerid AND TT.closedate<T.closedate
);
26 июн 13, 17:01    [14487987]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
abrashka, есть еще "антинаучный" метод и, скорее всего, самый быстрый

declare @customerid int = 0
declare @serverid int = 0
declare @flag int 

update t set @flag = case when @customerid <> customerid or @serverid <> serverid then 1 else 0 end, flag = @flag, @customerid = customerid,  @serverid = serverid
from #t t with (index (ix1))


ЗЫЖ хотя участники форума и не приветствуют данный метод. Где-то даже была статья или ветка форума по этому методу
26 июн 13, 17:32    [14488226]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Выкинуть вообще столбец flag. Вместо него сделать таблицу "первых" транзакций и поддерживать ее триггером.
26 июн 13, 18:22    [14488445]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
abrashka
Member

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

Выкинуть сложно, есть много репортов, которые этот flag используют, если его убрать, то и репорты прийдется "чинить"
26 июн 13, 18:53    [14488565]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
abrashka,

Ну не выкидывайте. Можете его периодически актуализировать по таблице "первых" транзакций. А лучше всего "починить" отчеты.
26 июн 13, 19:42    [14488700]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
invm
Выкинуть вообще столбец flag. Вместо него сделать таблицу "первых" транзакций и поддерживать ее триггером.
Тогда можно и актуальность flag поддерживать триггером, без дополнительной таблицы.
26 июн 13, 21:20    [14488942]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плиз написать самый эффективный апдейт.  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mind
Тогда можно и актуальность flag поддерживать триггером, без дополнительной таблицы.
Можно. Возможно это будет, в условиях ТС'а, оптимальным вариантом.
26 июн 13, 21:42    [14489005]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить