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

Откуда:
Сообщений: 69
Как оптимальнее всего оптимизировать такого рода запрос?

use temp_db

UPDATE t
SET elist = en.level3_iid
FROM
	temp_db.dbo.TMP_et_sales t
	,temp_db.dbo.cy_elist_sales_f eo
	,temp_db.dbo.elist_mrk en
WHERE 
	t.elist = eo.level3_iid
	AND eo.level3_name = en.level3_name



Время выполнения такого запроса достигает 5-8 часов (((

количество строк в
TMP_et_sales - 45675744
cy_elist_sales_f - 177
elist_mrk - 947

На сколько сильно в данном случае помогут индексы?
17 май 13, 18:44    [14313741]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Если нет блокировок, то особо никак. Разве что поставить побольше дисков.

Ну и план покажите.
17 май 13, 18:46    [14313756]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Bladeless
Как оптимальнее всего оптимизировать такого рода запрос?

Разбить таблицу TMP_et_sales на блоки (скажем, по миллиону строк в порядке возрастания кластерного индекса), апдейтить в цикле по одному блоку за итерацию цикла, коммитить изменения после каждой итерации.

Кроме плана выполнения, покажите DDL для таблиц.
17 май 13, 18:56    [14313794]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

Откуда:
Сообщений: 69
Гавриленко Сергей Алексеевич,

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Update(OBJECT:([ng_cognos_dwh].[dbo].[TMP_et_sales1]), SET:([ng_cognos_dwh].[dbo].[TMP_et_sales1].[elist] = [ng_cognos_dwh].[dbo].[elist_mrk].[level3_iid] as [en].[level3_iid]) WITH UNORDERED PREFETCH)
       |--Top(ROWCOUNT est 0)
            |--Hash Match(Aggregate, HASH:([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000]) DEFINE:([en].[level3_iid]=ANY([ng_cognos_dwh].[dbo].[elist_mrk].[level3_iid] as [en].[level3_iid])))
                 |--Hash Match(Inner Join, HASH:([eo].[level3_iid])=([t].[elist]))
                      |--Hash Match(Inner Join, HASH:([eo].[level3_name])=([en].[level3_name]), RESIDUAL:([ng_cognos_dwh].[dbo].[cy_elist_sales_f].[level3_name] as [eo].[level3_name]=[ng_cognos_dwh].[dbo].[elist_mrk].[level3_name] as [en].[level3_name]))
                      |    |--Table Scan(OBJECT:([ng_cognos_dwh].[dbo].[cy_elist_sales_f] AS [eo]))
                      |    |--Table Scan(OBJECT:([ng_cognos_dwh].[dbo].[elist_mrk] AS [en]))
                      |--Table Scan(OBJECT:([ng_cognos_dwh].[dbo].[TMP_et_sales1] AS [t]))


К сообщению приложен файл. Размер - 20Kb
17 май 13, 19:10    [14313848]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
aleks2
Guest
use temp_db

declare @t table( n_level3_iid, o_level3_iid, primary key clustered(o_level3_iid) );
insert @t
select 
  from temp_db.dbo.cy_elist_sales_f eo inner join temp_db.dbo.elist_mrk en
       on eo.level3_name = en.level3_name;

UPDATE t
SET elist = n_level3_iid
FROM
	temp_db.dbo.TMP_et_sales t inner join @t on t.elist = o_level3_iid;
17 май 13, 19:16    [14313877]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
aleks2
Guest
Пардон...
use temp_db

declare @t table( n_level3_iid, o_level3_iid, primary key clustered(o_level3_iid) );
insert @t
select en.level3_iid, eo.level3_iid
  from temp_db.dbo.cy_elist_sales_f eo inner join temp_db.dbo.elist_mrk en
       on eo.level3_name = en.level3_name;

UPDATE t
SET elist = n_level3_iid
FROM
	temp_db.dbo.TMP_et_sales t inner join @t on t.elist = o_level3_iid;
17 май 13, 19:19    [14313893]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
aleks2
Пардон...
use temp_db

declare @t table( n_level3_iid, o_level3_iid, primary key clustered(o_level3_iid) );
insert @t
select en.level3_iid, eo.level3_iid
  from temp_db.dbo.cy_elist_sales_f eo inner join temp_db.dbo.elist_mrk en
       on eo.level3_name = en.level3_name;

UPDATE t
SET elist = n_level3_iid
FROM
	temp_db.dbo.TMP_et_sales t inner join @t on t.elist = o_level3_iid;
А в чем предполагается выигрыш? План оптимальнее вряд ли получится. Да и первичный ключ на временных таблицах в 90% случаев как козе баян, поиска по нему все равно не будет. 45 миллионов поисков по индексу это перебор, так что все равно будет хэш.
17 май 13, 19:27    [14313916]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
aleks2,

Или это чтобы избавиться от Hash Match?
17 май 13, 19:28    [14313923]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Crimean
Member

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

возможно, чуть-чуть поможет добавочка "and t.elist != en.level3_iid"
ну и если возможно можно попробоват индексами к merge стратегии свести
но если обновлений де-факто реально очень много, то совсем никак, только сменит дизайн данных и отказаться от таких объемных обновлений.. да, чушь, но..
17 май 13, 19:42    [14313972]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Ennor Tiegael
Member

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

Это у вас кучи? Сделайте хотя бы кластерные индексы, что ли.
17 май 13, 19:43    [14313977]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

Откуда:
Сообщений: 69
Гость333
Bladeless
Как оптимальнее всего оптимизировать такого рода запрос?

Разбить таблицу TMP_et_sales на блоки (скажем, по миллиону строк в порядке возрастания кластерного индекса), апдейтить в цикле по одному блоку за итерацию цикла, коммитить изменения после каждой итерации.

Кроме плана выполнения, покажите DDL для таблиц.


спасибо, попробую реализовать.

DDL еще актуален?
18 май 13, 01:03    [14315177]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

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

на сколько предложенный вариант уменьшит время выполнения запроса?
18 май 13, 02:21    [14315349]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Bladeless
aleks2,

на сколько предложенный вариант уменьшит время выполнения запроса?
Ну таблицы у вас, возьмите да померяйте.
18 май 13, 02:40    [14315367]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

Откуда:
Сообщений: 69
Гавриленко Сергей Алексеевич,

Уже проверяю. Я хотел узнать результат основанный уже на полученном опыте )))
18 май 13, 03:27    [14315433]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Bladeless
Гавриленко Сергей Алексеевич,

Уже проверяю. Я хотел узнать результат основанный уже на полученном опыте )))
Вряд ли вы найдете кого-то с опытом, у кого таблицы с вашей структурой, данные с вашим распределением и железо, аналогичное вашему.
18 май 13, 03:57    [14315447]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Crimean
Member

Откуда:
Сообщений: 13147
после рассмотрения плана я бы так боролся

К сообщению приложен файл. Размер - 27Kb
18 май 13, 11:12    [14315656]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Алексей Куренков
Member [заблокирован]

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

create index ix1 on TMP_et_sales (elist)
18 май 13, 12:08    [14315782]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3685
Алексей Куренков
Bladeless,

create index ix1 on TMP_et_sales (elist)

это чтобы еще и индекс апдейтить??
18 май 13, 13:54    [14316149]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Ivan Durak,

))))). Точно. Апдейт индекса будет дешевле чем скан таблицы, хэш связки.
18 май 13, 14:28    [14316214]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Bladeless
DDL еще актуален?

Актуален, если ни один из предложенных способов не дал желаемой оптимизации.
19 май 13, 03:25    [14318098]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

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

невероятно!!!
запрос выполняется за 10-20 минут.

К сообщению приложен файл. Размер - 36Kb
19 май 13, 23:51    [14318671]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Exproment
Member

Откуда:
Сообщений: 416
Bladeless, Приведите скрипты создания таблиц. Данное вам решение вылечило симптом, а не болезнь.
20 май 13, 09:41    [14319247]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Bladeless
Member

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

каждый раз TMP_et_sales создается копированием из другой таблицы:
SELECT * INTO TMP_et_sales FROM public_sales_f.dbo.et_sales WHERE 1=1

все остальные созданы давно.

Как определить симптомы?
20 май 13, 12:57    [14320663]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3685
Bladeless
Exproment,

каждый раз TMP_et_sales создается копированием из другой таблицы:
SELECT * INTO TMP_et_sales FROM public_sales_f.dbo.et_sales WHERE 1=1

все остальные созданы давно.

Как определить симптомы?


Мощно.
Ну можно хотябы сразу же в этот момент и заджойнить temp_db.dbo.cy_elist_sales_f и temp_db.dbo.elist_mrk
чтобы elist сразу нужный и получить.
20 май 13, 15:07    [14321609]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимальнее всего оптимизировать такого рода запрос ...  [new]
Exproment
Member

Откуда:
Сообщений: 416
Bladeless
Exproment,
каждый раз TMP_et_sales создается копированием из другой таблицы:
все остальные созданы давно.

Вас просят привести инструкции Create Table, дабы понять как выглядят ваши таблицы, каких типов и с какими индексами. Для этого, если вы используете SSMS: Находите в Object Explorer данные таблицы, правой кнопкой Script table as -> Create table. А то что написали вы - это скрипт заполнения таблицы.

SELECT * INTO TMP_et_sales FROM public_sales_f.dbo.et_sales WHERE 1=1

каков сакральный смысл предварительного копирования 45 675 744 записей ?

Ну и исходя из всего что выше напрашиваются вопросы:
  • Что это за апдейт ? Что он делает исходя из человеческой логики.
  • Как часто он должен выполнятся ?
  • 20 май 13, 16:08    [14322074]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить