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

Откуда: Женева
Сообщений: 1751
задачка такая: есть две таблицы одинаковой структуры: часть полей - составной ПК, остальные - числовые поля с данными.
Надо найти какие записи изменились, удалились и добавились.
Сравнение происходит по совпадению полей из ПК и по неравенству остальных полей

вопрос: какие индексы лучше создать? т.к. всего на 2млн записей запрос работает 26 минут :-(

п.с. кто узнал задачу инкрементального обновления хранилища - может подскажете другие методы?
30 ноя 05, 18:17    [2127709]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
Я создаю для этого update таблицу.
Способом select into и фулл джойном
Описывать подробно, как, не вижу смысла, потому как сильно зависит от структур таблиц и задачи инкрементального обновления :)

Затем первую (основную, со старыми данными) сливаю с апдэйт таблицей. И опять же select into заливаю результат в таблицу, которую затем переименовываю и накладываю индексы.

При модели recovery=simple, эта схема работает гораздо быстрее чем update,delete,insert. И зачастую не нужны никакие индексы, кроме финальных
30 ноя 05, 19:17    [2128077]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сравнение происходит по совпадению полей из ПК и по неравенству остальных полей
Тут кроме кластерного индекса, имхо, ничего не поможет.

п.с. кто узнал задачу инкрементального обновления хранилища - может подскажете другие методы?
Флаг, показывающий что запись была изменена. Измененная запись просто обновляет запись в хранилище без всяких сравнений.
30 ноя 05, 19:29    [2128131]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
Glory
Сравнение происходит по совпадению полей из ПК и по неравенству остальных полей
Тут кроме кластерного индекса, имхо, ничего не поможет.
кластерный индекс по полям ПК уже есть. неужели нет ничего оптимальнее? ну не верю я что джойн по кластерным индексам так долго работает :-(

Glory
п.с. кто узнал задачу инкрементального обновления хранилища - может подскажете другие методы?
Флаг, показывающий что запись была изменена. Измененная запись просто обновляет запись в хранилище без всяких сравнений.
к сожалению не получится... т.к. ОЛТП система ничего не знает ни о загрузках в хранилище ни тем более о самом хранилище... да и нагружать её тригерами, имхо, неправильно...
30 ноя 05, 20:32    [2128300]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
Shurgenz
Я создаю для этого update таблицу.
Способом select into и фулл джойном
Описывать подробно, как, не вижу смысла, потому как сильно зависит от структур таблиц и задачи инкрементального обновления :)
Я вообщем-то не обсуждаю, как... я так и делаю. вопрос в том, почему так медленно джойнятся таблицы по ПК?

Shurgenz

Затем первую (основную, со старыми данными) сливаю с апдэйт таблицей. И опять же select into заливаю результат в таблицу, которую затем переименовываю и накладываю индексы.

При модели recovery=simple, эта схема работает гораздо быстрее чем update,delete,insert. И зачастую не нужны никакие индексы, кроме финальных
У меня всё несколько сложнее, т.к. есть отстойник данных (staging area), в который данные сливаются из многих ОЛТП систем и не просто сливаются, а с разными преобразиваниями
30 ноя 05, 20:38    [2128304]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Dmitry Biryukov
Glory
Сравнение происходит по совпадению полей из ПК и по неравенству остальных полей
Тут кроме кластерного индекса, имхо, ничего не поможет.
кластерный индекс по полям ПК уже есть. неужели нет ничего оптимальнее? ну не верю я что джойн по кластерным индексам так долго работает :-(

А вы план то выполнения своего запроса смотрели ?

Dmitry Biryukov

Glory
п.с. кто узнал задачу инкрементального обновления хранилища - может подскажете другие методы?
Флаг, показывающий что запись была изменена. Измененная запись просто обновляет запись в хранилище без всяких сравнений.
к сожалению не получится... т.к. ОЛТП система ничего не знает ни о загрузках в хранилище ни тем более о самом хранилище... да и нагружать её тригерами, имхо, неправильно...

Тогда по сути дела у вас не будет инкремантального обновления. Потому, что каждый раз вам придется работать с полным набором данных их базы OLTP.
Как раз для того, чтобы динамически сформировать такой "флаг".
Уж не знаю, сравнимы ли эти затраты с затратами при ввдении триггеров. Или столбцов timestamp. Смотрите сами.
30 ноя 05, 21:08    [2128335]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
Dmitry Biryukov
... неужели нет ничего оптимальнее? ну не верю я что джойн по кластерным индексам так долго работает :-(...
Попробуйте так:

1. Создаём тестовые таблицы
set nocount on
use tempdb
go

if object_id('tA', 'U') > 0 drop table tA
go
create table tA(id int identity not null, col1 varchar(36) not null, col2 varchar(36) not null)
go

insert into tA(col1, col2)
    select top 2000000 newid(), newid()
    from master..sysobjects t1(nolock), master..syscomments t2(nolock)
go
-- 19 sec
-- select count(*) from tA -- 2000000

alter table tA
    add constraint PK_tA primary key clustered (id)
-- 5 sec
-- sp_helpindex tA

if object_id('tB', 'U') > 0 drop table tB
go
create table tB(id int not null, col1 varchar(36) not null, col2 varchar(36) not null)
go
insert into tB(id, col1, col2)
    select id, col1, col2 from tA
go
-- 12 sec
-- select count(*) from tB -- 2000000

alter table tB
    add constraint PK_tB primary key clustered (id)
go
-- 4 sec
-- sp_helpindex tB

2. Экспериментируем:
-- #1 equal
select tA.*, tB.* 
from tA full join tB on
    tA.id = tB.id
where tA.id is null or tB.id is null  
-- 2 sec
go

delete tA
where id in(select top 10 id from tA order by newid())
delete tB
where id in(select top 5 id from tB order by newid())
go

-- #2 find missed records
select tA.*, tB.* 
from tA full join tB on
    tA.id = tB.id
where tA.id is null or tB.id is null  
-- 2 sec
go

update tA set col1 = newid() 
where id in(select top 3 id from tA order by newid())
update tB set col2 = newid() 
where id in(select top 2 id from tB order by newid())
go

-- #2 find different records
select t1.*, t2.*
from (select *, binary_checksum(*) as cs1, binary_checksum(*)-0xFF as cs2 from tA) t1 
    inner join 
    (select *, binary_checksum(*) as cs1, binary_checksum(*)-0xFF as cs2 from tB) t2 on
    t1.id = t2.id
where t1.cs1 <> t2.cs1 or t1.cs2 <> t2.cs2
go
-- 7 sec
30 ноя 05, 23:14    [2128499]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
Glory
Тогда по сути дела у вас не будет инкремантального обновления. Потому, что каждый раз вам придется работать с полным набором данных их базы OLTP.
Как раз для того, чтобы динамически сформировать такой "флаг".
Уж не знаю, сравнимы ли эти затраты с затратами при ввдении триггеров. Или столбцов timestamp. Смотрите сами.
Инкрементальное обновление будет! делай раз - это ОЛТП системы, делай два - отстойник с трансформированными-полуагрегированными данными, делай три - хранилище.

А вот насчет, что выгоднее: тригеры, timestamp или запрос на сравнение таблиц - я и сам не знаю. думал тут люди бывалые - сталкивались с такими задачами..

суть задачи - передавать в хранилище как можно меньше данных (т.е. только изменения). Сравнивать с исходными ОЛТП системами проблематично, поэтому и ищется решение как завязать формирование пакета обновлений на основании staging area.
1 дек 05, 00:19    [2128555]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
2 VladRUS.ca
эксперемент интересный, но...
у меня ПК по 4-м полям varchar(50) (к сожалению, не обсуждается) и два поля float сравниваются на <>

запросы примерно такие:

--diff
insert into incr_table
select tA.* from tA inner join tB on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tA.val1<>tB.val1 OR tA.val2<>tB.val2

--new
insert into incr_table
select tA.* from tA left outer join tB on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tB.id1 is NULL

--deleted
insert into incr_table
select <список полей ПК из таблицы tB>, <нули для числовых полей> from tB left outer join tA on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tA.id1 is NULL

все три запроса суммарно выполняются 26 мин

замеры делаю на winXP+sp2,sql2k+sp3, P4 3.0 HT, 1Gb
1 дек 05, 00:38    [2128575]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
и результаты(примерно):
80 тыс обновлений
50 тыс новых записей
20 тыс удалённых
1 дек 05, 00:43    [2128578]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
А таблица incr_table с индексами?
1 дек 05, 00:49    [2128585]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
фактически нет,
хотя есть один: на identity поле, которое не участвует в сравнениях
(перед вставкой делается truncate table)
1 дек 05, 01:09    [2128600]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
VladRUS.ca
Member

Откуда: Toronto
Сообщений: 1172
автор
...у меня ПК по 4-м полям varchar(50)...

да... varchar(50) да ещё 4 поля - ПК надо менять однозначно.
1 дек 05, 01:17    [2128608]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Esli radi udobstva vy gotovy primiritsia s nebolschoi poterei proisvoditelnosti, to ispolsuite konstrukziiiu vida

SELECT  Column1, Column2, ...., ColumnN, FROM 
(
  SELECT distinct Column1, Column2, ...., ColumnN, 1 as TAB_ID from #A
  UNION ALL 
  SELECT distinct Column1, Column2, ...., ColumnN, 2 as TAB_ID from #B
)SUPERTAB 
group by FName 
HAVING SUM(TAB_ID) in (OP_CODE)
Gde OP_CODE = 3 (dlia sapisei obschich dlia obeich tabliz)
Gde OP_CODE = 1 (Dlia sapisei tolko is tablizy #A )
Gde OP_CODE = 2 (Dlia sapisei tolko is tablizy #B)

Eta konstrukziia korrektno rabotaet s NULLs.
Krome togo, podstavliaa rasnye OP_CODE vy poluchaete rasnye operazii ne meniaja sam select.
Edinstvennoe ogranichenie: Stroki ne doljny dublirovatsia!

------------
Esli hotite, mogu vyslat prozeduru dlia sravnebiia tabliz.
Mylte.
1 дек 05, 02:10    [2128640]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
А почему бы не объединить запросы Inserted и Deleted в один запрос с FULL JOIN и условием a.id1 is null OR b.id1 is null?
Еще попробуйте добавить в селекты хинт MERGE джойна.
1 дек 05, 09:44    [2128982]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
VladRUS.ca
автор
...у меня ПК по 4-м полям varchar(50)...

да... varchar(50) да ещё 4 поля - ПК надо менять однозначно.
Я ж сказал - не обсуждается. хранилище уже есть и его структуру менять нельзя
1 дек 05, 10:11    [2129142]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
Alexander Us
Esli radi udobstva vy gotovy primiritsia s nebolschoi poterei proisvoditelnosti, to...
Наоборот, я готов мирится с неудобствами ради выйгрыша в производительности
1 дек 05, 10:13    [2129153]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Dmitry Biryukov
Member

Откуда: Женева
Сообщений: 1751
WiRuc
А почему бы не объединить запросы Inserted и Deleted в один запрос с FULL JOIN и условием a.id1 is null OR b.id1 is null?
попробую...
WiRuc
Еще попробуйте добавить в селекты хинт MERGE джойна.
а как это сделать?
1 дек 05, 10:15    [2129162]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
WiRuc
Еще попробуйте добавить в селекты хинт MERGE джойна.
а как это сделать?

Либо в конце селекта дописать OPTION(MERGE JOIN), либо вместо INNER JOIN написать INNER MERGE JOIN.
1 дек 05, 10:28    [2129243]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Glory
Member

Откуда:
Сообщений: 104760
Dmitry Biryukov
Инкрементальное обновление будет! делай раз - это ОЛТП системы, делай два - отстойник с трансформированными-полуагрегированными данными, делай три - хранилище.

А вот насчет, что выгоднее: тригеры, timestamp или запрос на сравнение таблиц - я и сам не знаю. думал тут люди бывалые - сталкивались с такими задачами..


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

Вот я вам еще раз и говорю, что по-моему мнению при таких объемах добавление операции установки флага изменения записи в системе OLTP будет выгоднее.
1 дек 05, 10:30    [2129263]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Dmitry Biryukov
Alexander Us
Esli radi udobstva vy gotovy primiritsia s nebolschoi poterei proisvoditelnosti, to...
Наоборот, я готов мирится с неудобствами ради выйгрыша в производительности

Vy vypolniaete 3 ! operazii odna sa drugoi sa 26 minut.

Esli vy sdelaete (odin ! ras)
SELECT  Column1, Column2, ...., ColumnN, SUM(TAB_ID) FLAG into FULL_TAB FROM 
(
  SELECT distinct Column1, Column2, ...., ColumnN, 1 as TAB_ID from #A
  UNION ALL 
  SELECT distinct Column1, Column2, ...., ColumnN, 2 as TAB_ID from #B
)SUPERTAB 
group by FName 
HAVING SUM(TAB_ID) 
to FLAG v resultirujuschei tablize i pokajet vam k kakim ishodnym tablizam prinadlejit sapis:

select * from FULL_TAB where FLAG = 1 -- Sapisi, prinadlejaschie tolko Tablize #A
select * from FULL_TAB where FLAG = 2 -- Sapisi, prinadlejaschie tolko Tablize #B
select * from FULL_TAB where FLAG = 3 -- Sapisi, prinadlejaschie obeim Tablizam: #A i #B

P.S: No v pervuju ochered na vashem meste ia by poproboval sdelat to, chto sovetuet GLORY.
1 дек 05, 10:58    [2129451]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
Dmitry Biryukov
Shurgenz
Я создаю для этого update таблицу.
Способом select into и фулл джойном
Описывать подробно, как, не вижу смысла, потому как сильно зависит от структур таблиц и задачи инкрементального обновления :)
Я вообщем-то не обсуждаю, как... я так и делаю. вопрос в том, почему так медленно джойнятся таблицы по ПК?

Shurgenz

Затем первую (основную, со старыми данными) сливаю с апдэйт таблицей. И опять же select into заливаю результат в таблицу, которую затем переименовываю и накладываю индексы.

При модели recovery=simple, эта схема работает гораздо быстрее чем update,delete,insert. И зачастую не нужны никакие индексы, кроме финальных
У меня всё несколько сложнее, т.к. есть отстойник данных (staging area), в который данные сливаются из многих ОЛТП систем и не просто сливаются, а с разными преобразиваниями


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

Замечено, что если Хранилище уже распухло, а обновлять его все же приходится, потому как некоторые из свежих данных из инкрементального обновления "уточняют" исторические данные, то менее затратно и более быстро делать select into практически для всех таблиц. Особенно для жирной факт-таблицы.

Говорил и еще раз скажу:

При RECOVERY=SIMPLE, гораздо быстрее сделать SELECT INTO + CREATE INDEX (INDEXES).

Для примера: UPDATE 10% жирной факт-таблицы с 80 млн записями с джойном по индексу занял на 4-х головом сервере 26 часов. SELECT INTO занял ~1,5 часа + индексы ~2,5.

То же самое касается INSERT и DELETE. Забудте о них, если работаете с хранилищами. Или пользуйтесь в тех случаях, когда выигрыш от них очевиден.

Все это мое ИМХО, хотя и проверенное экспериментально.
1 дек 05, 12:22    [2130089]     Ответить | Цитировать Сообщить модератору
 Re: сравнение таблиц: индексы  [new]
rhs
Guest
use tempdb
go

if object_id('tA', 'U') > 0 drop table tA
go
create table tA
	(id1 varchar(50) not null, 
	id2 varchar(50) not null,
	id3 varchar(50) not null,
	id4 varchar(50) not null,
	val1 float not null,
	val2 float not null
	)
go

insert into tA(id1, id2, id3, id4, val1, val2)
    select top 2000000 newid(), newid(), newid(), newid(), rand(0), rand()
    from master..sysobjects t1(nolock), master..syscomments t2(nolock)
go

alter table tA
    add constraint PK_tA primary key clustered (id1, id2, id3, id4)
--sp_helpindex tA

if object_id('tB', 'U') > 0 drop table tB
go
create table tB
	(id1 varchar(50) not null, 
	id2 varchar(50) not null,
	id3 varchar(50) not null,
	id4 varchar(50) not null,
	val1 float not null,
	val2 float not null
	)
go
insert into tB select * from tA

alter table tB
    add constraint PK_tB primary key clustered (id1, id2, id3, id4)

delete tA
where id1 in(select top 1000 id1 from tA order by newid())
delete tB
where id1 in(select top 2000 id1 from tB order by newid())
go

update tA set id1 = newid() 
where id1 in(select top 10000 id1 from tA order by newid())
update tB set id2 = newid() 
where id2 in(select top 20000 id2 from tB order by newid())
go
update tA set val1 = rand() 
where id1 in(select top 10000 id1 from tA order by newid())
update tB set val2 = rand() 
where id2 in(select top 20000 id2 from tB order by newid())
go

-- проверяем время выполнения указанных запросов

select tA.* from tA inner join tB on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tA.val1<>tB.val1 OR tA.val2<>tB.val2
-- время выполнения 1:23

select tA.* from tA 
left outer join tB on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tB.id1 is NULL
-- время выполнения 1:07

select tB.*
from tB left outer join tA on tA.id1 = tB.id1 and tA.id2 = tB.id2 and tA.id3 = tB.id3 and tA.id4 = tB.id4
where tA.id1 is NULL
-- время выполнения 1:08

Предположения:
  • А есть ли в действительности ПК на ваших исходных таблицах?
  • Неподходящая структура ПК, например кроме 4 сравниваемых полей затесалось еще какое-нибудь
  • А может проблема в INSERT-e? Может там триггер какой имеется?
  • 1 дек 05, 18:25    [2132669]     Ответить | Цитировать Сообщить модератору
     Re: сравнение таблиц: индексы  [new]
    Dmitry Biryukov
    Member

    Откуда: Женева
    Сообщений: 1751
    rhs
    -- время выполнения 1:23

    -- время выполнения 1:07

    -- время выполнения 1:08
    а это на каком железе?
    1 дек 05, 23:33    [2133291]     Ответить | Цитировать Сообщить модератору
     Re: сравнение таблиц: индексы  [new]
    VladRUS.ca
    Member

    Откуда: Toronto
    Сообщений: 1172
    автор
    ...замеры делаю на winXP+sp2,sql2k+sp3, P4 3.0 HT, 1Gb..

    У меня точно такая же машина и конфигурация. Памяти только 2 GB.

    Проранил скрипт rhs:
    -- время выполнения 0:13
    -- время выполнения 0:11
    -- время выполнения 0:11
    
    2 дек 05, 01:15    [2133340]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить