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

Откуда:
Сообщений: 199
Парни, всем привет. Очень сильно нуждаюсь в помощи или консультации относительно реализации одного апдейта, который канает меня уже не один десяток дней.
В общем, начнем: есть таблица, которая содержит в себе порядка 4-5 миллионов строк. Данные в ней могут содержать несколько вариантов записей об одном и том же объекте, но с разными значениями некоторых полей. Причем, у каждой записи есть номер приоритета, по которому мы должны извлечь наиболее приоритетные для поля данные и обновить его, заполним все значения поля ими. Например, есть в таблице такой блок:

ID_1 ID_2 INN NAME DATA PRIORITY
X1 A-A1 123456789 Test name 1 0.555 2
A-A1 123456789 Test name 2 0.111 3
X1 123456789 Test name 0.123 2
X1 A-A1 Test name 3 0.222 4
123456789 Test name 0.123 2
X1 Test name MP 0.333 1

И мы должны проапдейтить ее и затем выбрать только одну строчку (оно и ясно, ведь после апдейта все строки будут иметь одинаковые данные), чтобы получить:

ID_1 ID_2 INN NAME DATA
X1A-A1123456789 Test name MP 0.333


В таблице 20 полей, в итоге получается, что мы делаем около 90-та апдейтов (если ID_1, ID_2 и INN не пустые, плюс все вариации - когда только один пустой, только два и т.д.). Разумеется, это неприемлимо и работает очень медленно.
Вопрос скорее в совете - что можно улучшить или как можно было бы сделать это другим, более адекватным методом?
Спасибо.
31 окт 13, 14:43    [15058946]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
SiNtez_26
В таблице 20 полей, в итоге получается, что мы делаем около 90-та апдейтов (если ID_1, ID_2 и INN не пустые, плюс все вариации - когда только один пустой, только два и т.д.). Разумеется, это неприемлимо и работает очень медленно.
Вопрос скорее в совете - что можно улучшить или как можно было бы сделать это другим, более адекватным методом?
Непонятно, зачем 90 апдейтов, когда можно сделать один???

Кроме того, что бы удалить лишние строки, апдэйта недостаточно, нужен DELETE

Вообще в таких случаях (полное изменение таблицы) обычно выгоднее сделать загрузку данных в новую таблицу, а потом переименовать. Это будет быстрее, ну и проверить можно перед переименованием.
31 окт 13, 15:04    [15059075]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Касаемо более адекватного метода, я имел ввиду, что можно и заменить апдейт на что-то другое, к ниму мы не привязаны. Однажды пробовал выбирать наиболее приоритетное значение использует селект + топ 1, результаты аналогично плачевные :(
31 окт 13, 15:05    [15059080]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
alexeyvg
Непонятно, зачем 90 апдейтов, когда можно сделать один???

Кроме того, что бы удалить лишние строки, апдэйта недостаточно, нужен DELETE

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

Лишние строки удалять нет нужды, мы селектим после апдейта все ()по одной уникальной строке) в другую таблицу.
31 окт 13, 15:06    [15059086]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Вообщем, приведу свой кривой апдейт одного из полей:
UPDATE t1
    SET t1.NAME = src.NAME
    FROM TABLE AS t1
    INNER JOIN TABLE AS src ON t1.INN = src.INN
                                           AND src.NAME IS NOT NULL
                                           AND src.PRIORITY = (SELECT MIN(PRIORITY)
                                                                        FROM TABLE
                                                                        WHERE NAME IS NOT NULL AND INN = src.INN)
31 окт 13, 15:10    [15059103]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
obj_idID_1ID_2INNNAMEDATAPRIORITY
7X1Test name MP0.3331
7123456789Test name0.1232
7X1A-A1123456789Test name 10.5552
7X1123456789Test name0.1232
7A-A1123456789Test name 20.1113
7X1A-A1Test name 30.2224
+
-- if object_id('tempdb..#data') is not null drop table #data
if object_id('tempdb..#data') is null begin
	create table #data (obj_id int default 7, ID_1 varchar(100),ID_2 varchar(100),INN varchar(100),NAME varchar(100),DATA  varchar(100),PRIORITY int)
	insert #data (ID_1,ID_2,INN,NAME,DATA,PRIORITY) values 
      ('X1','A-A1','123456789','Test name 1','0.555','2'),
      ('','A-A1','123456789','Test name 2','0.111','3'),
      ('X1','','123456789','Test name','0.123','2'),
      ('X1','A-A1','','Test name 3','0.222','4'),
      ('','','123456789','Test name','0.123','2'),
      ('X1','','','Test name MP','0.333','1')
end

select * from #data order by obj_id, PRIORITY

if OBJECT_ID('tempdb..#update')<>0 drop table #update
select * into #update from #data where 1=0

insert #update
select 
   obj_id, 
   STUFF(MIN(p + NULLIF(ID_1,'')),1,5,'') ID_1, 
   STUFF(MIN(p + NULLIF(ID_2,'')),1,5,'') ID_2, 
   STUFF(MIN(p + NULLIF(INN,'')),1,5,'') INN, 
   STUFF(MIN(p + NULLIF(NAME,'')),1,5,'') NAME, 
   STUFF(MIN(p + NULLIF(DATA,'')),1,5,'') DATA,
   MIN(PRIORITY)PRIORITY
from #data d
cross apply (select CONVERT(char(5),PRIORITY)p ) c
group by obj_id
having COUNT(*)>1

delete d
from #data d
join #update u on u.obj_id=d.obj_id

insert #data
select * from #update

drop table #update

select * from #data
drop table #data
obj_idID_1ID_2INNNAMEDATAPRIORITY
7X1A-A1123456789Test name MP0.3331
31 окт 13, 15:22    [15059180]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Спасибо большое, сейчас опробуем.
31 окт 13, 15:26    [15059202]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
проправка:
...
from #data d
-- cross apply (select CONVERT(char(5),PRIORITY)p ) c
cross apply (select RIGHT(100000+PRIORITY,5)p ) c
...
31 окт 13, 15:43    [15059302]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
Спасибо
31 окт 13, 15:46    [15059318]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
SiNtez_26,

Форум по MySQL давно превратился в "Напиши за меня SELECT".
А тут, значит, -- ещё круче, напиши за меня UPDATE ?
31 окт 13, 15:47    [15059329]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
SiNtez_26
Member

Откуда:
Сообщений: 199
MasterZiv
Форум по MySQL давно превратился в "Напиши за меня SELECT".
А тут, значит, -- ещё круче, напиши за меня UPDATE ?

Ну, вообще, вопрос был в совете, какие варианты в данном случае еще есть, а не в "Напишите за меня UPDATE"
31 окт 13, 15:49    [15059348]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
Cygapb-007
Member

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

когда есть время и интересно - почему бы и не написать:) ?

Никто не заставляет...
31 окт 13, 15:54    [15059382]     Ответить | Цитировать Сообщить модератору
 Re: Сложный апдейт двух десятков полей при 4 млн. записей  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
по привкдённому в первом посте примеру и update у ТС в таблице не 1 ключ субъекта obj_id(как написал Сударь-007) ,а 3 альтернативных ключа объекта ID_1,ID_2,INN некоторые из которых могут отсутствовать в записи
Тогда нужно до группировки сначала получить составной ключ
if object_id('tempdb..#data') is not null drop table #data
create table #data (ID_1 varchar(100),ID_2 varchar(100),INN varchar(100),NAME varchar(100),DATA  varchar(100),PRIORITY int)

insert #data (ID_1,ID_2,INN,NAME,DATA,PRIORITY) values 
      ('X1','A-A1','123456789','Test name 1','0.555','22'),
      (null,'A-A1','123456789','Test name 2','0.111','23'),
      ('X1',null,'123456789','Test name','0.123','22'),
      ('X1','A-A1',null,'Test name 3','0.222','24'),
      (null,null,'123456789','Test name','0.123','22'),
      ('X1',null,null,'Test name MP','0.333','9')


if object_id('tempdb..#data_new') is not null drop table #data_new
;WITH CommonID(ID_1,ID_2,INN) AS
(select 
 ISNULL(d1.ID_1,d3.ID_1) as ID_1
,ISNULL(d1.ID_2,d2.ID_2) as ID_2
,ISNULL(d2.INN,d3.INN)   as INN
from #data d1
join  #data d2 ON d2.ID_2 =d1.ID_2
join #data d3 ON d3.INN  =d2.INN OR d3.ID_1 = d1.ID_1
where d1.ID_1 is not null and d1.ID_2 is not null
   and d2.ID_2 is not null and d2.INN is not null
   and d3.ID_1 is not null and d3.INN is not null
GROUP BY 
 ISNULL(d1.ID_1,d3.ID_1)
,ISNULL(d1.ID_2,d2.ID_2)
,ISNULL(d2.INN,d3.INN))

SELECT c.ID_1,c.ID_2,c.INN
,   STUFF(MIN(p + NULLIF(NAME,'')),1,5,'') NAME
,   STUFF(MIN(p + NULLIF(DATA,'')),1,5,'') DATA
INTO #data_new
FROM CommonID c
JOIN #data d	ON d.ID_1=c.ID_1 OR d.ID_2=c.ID_2 OR d.INN =c.INN
cross apply (select RIGHT(100000+PRIORITY,5)p ) pr
group by c.ID_1,c.ID_2,c.INN


select * from #data
select * from #data_new
1 ноя 13, 07:54    [15062295]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить