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

Откуда:
Сообщений: 190
Всем привет!
Имеются 2 таблицы:
CREATE TABLE [dbo].[MT_Ostatki](
	[TData] [datetime] NULL,
	[TDataInt] [int] NULL,
	[TPodrCode] [int] NULL,
	[TTovarCode] [int] NULL,
	[TPartnerCode] [int] NULL,
	[TPartnerIDDBInt] [bigint] NULL,
	[TAkciaID] [bigint] NULL,
	[TAkcia] [int] NULL,
	[TAkciaSps] [char](20) NULL,
	[TMatricaCode] [int] NULL,
	[TMatricaName] [char](10) NULL,
	[TKol] [decimal](18, 3) NULL,
	[TSumZak] [decimal](18, 3) NULL,
	[TSum] [decimal](18, 3) NULL,
	[TFasovok] [decimal](18, 2) NULL,
	[TVes] [decimal](18, 2) NULL,
	[TObiom] [decimal](18, 2) NULL,
	[TOsnovnoi] [int] NULL,
	[TEmkost] [decimal](18, 3) NULL,
	[TSkladTipCode] [int] NULL,
	[TFlOtricatelnih] [int] NULL,
	[TTovarDataPodrBINT] [bigint] NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TPartnerIDDBInt] ON [dbo].[MT_Ostatki] 
[TTovarDataPodrBINT] ASC
и
CREATE TABLE [dbo].[MT_MatricaOll](
	[TData] [datetime] NULL,
	[TDataInt] [int] NULL,
	[TTovarID] [char](9) NULL,
	[TTovarIDDBInt] [bigint] NULL,
	[TTovarCode] [int] NULL,
	[TPodrID] [char](9) NULL,
	[TPodrCode] [int] NULL,
	[TMatricaID] [char](9) NULL,
	[TMatricaCode] [int] NULL,
	[TMatricaName] [varchar](25) NULL,
	[TTovarDataPodrBINT] [bigint] NULL,
	[TFlNeSchitat] [int] NULL,
	[TFlIzm] [char](1) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TPartnerIDDBInt] ON [dbo].[MT_MatricaOll] 
[TTovarDataPodrBINT] ASC

В первой 25,5 млн. строк, во второй 14 млн.
Надо обновить первую таблицу:
UPDATE  MT_Ostatki SET MT_Ostatki.TMatricaCode=MT_MatricaOll.TMatricaCode 
FROM MT_MatricaOll with (index (IX_TTovarDataPodrBINT)) INNER JOIN
MT_Ostatki ON MT_MatricaOll.TTovarDataPodrBINT = MT_Ostatki.TTovarDataPodrBINT  
Обновление идёт около 30 минут, хотя таблицу в это время никто больше не юзает. Обновление накладывает блокировку на всю таблицу. Лог транзакций базы на отдельном диске, база на RAID10 из 6-ти 15к винтов, RAID LSI 8708 ELP 128мб, 8 гб. для SQL выделено. Размер 1-ой таблицы 4,2 Гб., второй 1,2 Гб. Т.е. памяти для них вполне. Странно, то, что раньше это обновление выполнялось минут за 8.
Filemon показывает множество мелких запросов по 8кб., что создает большую очередь к дискам. Возможно, контроллер в такой конфигурации не любит такие блоки. Какие вообще рекомендации по структуре таблиц, может обновляемый столбец самым правым должен быть?
P.S. Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
21 окт 09, 11:27    [7816223]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760
План выполнения то где ? IO statistics ?
21 окт 09, 11:30    [7816255]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
  |--Table Update(OBJECT:([MTempForOLAP].[dbo].[MT_Ostatki]), SET:([MTempForOLAP].[dbo].[MT_Ostatki].[TMatricaCode] = [MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode]) WITH UNORDERED PREFETCH)
       |--Top(ROWCOUNT est 0)
            |--Hash Match(Aggregate, HASH:([Bmk1004]), RESIDUAL:([Bmk1004] = [Bmk1004]) DEFINE:([MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode]=ANY([MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode])))
                 |--Hash Match(Inner Join, HASH:([MTempForOLAP].[dbo].[MT_MatricaOll].[TTovarDataPodrBINT])=([MTempForOLAP].[dbo].[MT_Ostatki].[TTovarDataPodrBINT]), RESIDUAL:([MTempForOLAP].[dbo].[MT_Ostatki].[TTovarDataPodrBINT]=[MTempForOLAP].[dbo].[MT_MatricaOll].[TTovarDataPodrBINT]))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1016]) WITH UNORDERED PREFETCH)
                      |    |--Sort(ORDER BY:([Expr1015] ASC))
                      |    |    |--Compute Scalar(DEFINE:([Expr1015]=BmkToPage([Bmk1000])))
                      |    |         |--Index Scan(OBJECT:([MTempForOLAP].[dbo].[MT_MatricaOll].[IX_TTovarDataPodrBINT]))
                      |    |--RID Lookup(OBJECT:([MTempForOLAP].[dbo].[MT_MatricaOll]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
                      |--Index Scan(OBJECT:([MTempForOLAP].[dbo].[MT_Ostatki].[IX_TTovarDataPodrBINT]), ORDERED FORWARD)
21 окт 09, 11:36    [7816323]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Извините, пока только план - промазал кнопкой. Статистика будет чуть позже.
21 окт 09, 11:37    [7816338]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760
А ПК нет чтоли ?
Идет обновление одной записи множеством записей ?
21 окт 09, 11:44    [7816393]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
ПК нет, таблица вообще плоская, временная для построения куба.
Т.е. надо обновить в таблице остатков поле TMatricaCode, если в таблице матрицы товаров найдется код, имеющийся в таблице остатков.
21 окт 09, 12:36    [7816897]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760
[quot [-==-]]ПК нет, таблица вообще плоская, временная для построения куба.
Т.е. надо обновить в таблице остатков поле TMatricaCode, если в таблице матрицы товаров найдется код, имеющийся в таблице остатков.[/quot]
А если одной записи из таблице остатков соответствуют все 14млн записей из таблицы товаров ?
21 окт 09, 12:37    [7816915]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
По идеи такого быть не может, т.к. заблицы заполняются уникальными id.
21 окт 09, 12:44    [7816985]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36818
-==-
По идеи такого быть не может, т.к. заблицы заполняются уникальными id.
А сервер-то откуда узнает, что они уникальные?

Сообщение было отредактировано: 21 окт 09, 12:46
21 окт 09, 12:46    [7817006]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
По идеи, он не этого не знает, но у него есть отсортированный индекс, и при вязке таблиц он создаеи некоторую временну, в которой помечает для себя номера строк, которые он должен обновить и собственно значения обновляемого столбца.
21 окт 09, 12:49    [7817036]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760
[quot [-==-]]По идеи, он не этого не знает, но у него есть отсортированный индекс,[/quot]
Индекс у вам НЕуникальный. Поэтому для сервера значения его тоже, как ни странно, неуникальны
21 окт 09, 12:59    [7817125]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Glory
[quot [-==-]]По идеи, он не этого не знает, но у него есть отсортированный индекс,

Индекс у вам НЕуникальный. Поэтому для сервера значения его тоже, как ни странно, неуникальны[/quot]
Ну да. А что это мешает серверу связать и обновить всё по-быстрому? Или он для кадой записи из матрицы прогоняет таблицу остатков при вязке? Сейчас закончит строить статисику, попробую на выборку этот запрос переделать - наверняка вяжет быстро.
21 окт 09, 13:02    [7817147]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36818
Glory
По идеи, он не этого не знает, но у него есть отсортированный индекс,

Индекс у вам НЕуникальный. Поэтому для сервера значения его тоже, как ни странно, неуникальны

Ну да. А что это мешает серверу связать и обновить всё по-быстрому? Или он для кадой записи из матрицы прогоняет таблицу остатков при вязке? Сейчас закончит строить статисику, попробую на выборку этот запрос переделать - наверняка вяжет быстро.
Вредность мешает, не иначе. А то, что вы сообщаете серверу как можно меньше информации о ваших данных, несомненно помогает.
21 окт 09, 13:04    [7817171]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Статистика IO.

К сообщению приложен файл. Размер - 0Kb
21 окт 09, 13:54    [7817603]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760
Статистика IO.

Ибо. IO statisctics - это результат в закладке messages полученный при включенной SET STATISTICS IO
21 окт 09, 13:56    [7817628]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Table 'MT_Ostatki'. Scan count 1, logical reads 21083261, physical reads 68819, read-ahead reads 420963, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MT_MatricaOll'. Scan count 1, logical reads 143777, physical reads 0, read-ahead reads 104975, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Есть ли принципиальная разница какую таблицу указывать первой при вязке -
FROM MT_Ostatki INNER JOIN MT_MatricaOll или наоборот?
Или условие:
MT_Ostatki.TTovarDataPodrBINT=MT_MatricaOll.TTovarDataPodrBINT или наоборт?
21 окт 09, 14:59    [7818252]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Glory
Member

Откуда:
Сообщений: 104760


Есть ли принципиальная разница какую таблицу указывать первой при вязке -
FROM MT_Ostatki INNER JOIN MT_MatricaOll или наоборот?
Или условие:
MT_Ostatki.TTovarDataPodrBINT=MT_MatricaOll.TTovarDataPodrBINT или наоборт?

План выполнения ответит вам на этот вопрос
21 окт 09, 15:01    [7818276]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
План другой... и, видимо, посимпатичнее:
  |--Table Update(OBJECT:([MTempForOLAP].[dbo].[MT_Ostatki]), SET:([MTempForOLAP].[dbo].[MT_Ostatki].[TMatricaCode] = [MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode]) WITH UNORDERED PREFETCH)
       |--Top(ROWCOUNT est 0)
            |--Hash Match(Aggregate, HASH:([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000]) DEFINE:([MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode]=ANY([MTempForOLAP].[dbo].[MT_MatricaOll].[TMatricaCode])))
                 |--Hash Match(Inner Join, HASH:([MTempForOLAP].[dbo].[MT_Ostatki].[TTovarDataPodrBINT])=([MTempForOLAP].[dbo].[MT_MatricaOll].[TTovarDataPodrBINT]), RESIDUAL:([MTempForOLAP].[dbo].[MT_MatricaOll].[TTovarDataPodrBINT]=[MTempForOLAP].[dbo].[MT_Ostatki].[TTovarDataPodrBINT]))
                      |--Index Scan(OBJECT:([MTempForOLAP].[dbo].[MT_Ostatki].[IX_TTovarDataPodrBINT]), ORDERED FORWARD)
                      |--Table Scan(OBJECT:([MTempForOLAP].[dbo].[MT_MatricaOll]))

Сейчас проверяю скорость.
21 окт 09, 15:21    [7818448]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
А не подскажете, как в одном запросе сделать обновление нескольких полей таблицы при вязке 1-о1 таблицы к трём. Т.е., в случе выше, обновляются товары остатков при совпадении с матрицей. А надо чтобы за один проход:
1. обновить товары при совпадении с матрицей
2. обновить коды партнеров при совпадении с партнерами
3. обновить еще что-то при совпадении с др. таблицей чего-то

Или без 3-х проходов не обойтись?
21 окт 09, 16:43    [7819281]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
iljy
Member

Откуда:
Сообщений: 8711
[-==-],

можно все, если вы конкретно поставите задачу - с DDL таблиц, примерами данных и т.п.
21 окт 09, 18:21    [7820077]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
А вообще, запросы подобного типа как можно оптимизировать? Может, надо как-то выравнивать данные по каким-то границам? Странно, что сервер очень редко кэширует почти всю таблицу, при этом запрос обновления 3-4 минуты, он больше предпочитает читать по 8-16 кб за 1 запрос на чтение, при этом процессор стоит, диски трещат, а толку ноль - пол часа при этом тратит. Как его заставить закешировать всю таблицу еще на этапе вставки в неё, чтобы потом обновить её в кэше, а не чиать с диска?
21 окт 09, 21:58    [7820671]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Немного удалось ускорить путем предварительной очистки кэша сервера.
22 окт 09, 17:33    [7825844]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
[quot [-==-]]А вообще, запросы подобного типа как можно оптимизировать?[/quot]Оптимизировать можно все, вплоть до личной жизни. Но надо больше вводных. Какая модель восстановления для начала?
22 окт 09, 17:41    [7825891]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
iljy
Member

Откуда:
Сообщений: 8711
[quot [-==-]]Немного удалось ускорить путем предварительной очистки кэша сервера.[/quot]

Вообще сначала добавляя данные, потом их изменяя - вы в принципе даете на серве двойную нагрузку, потому что сервер стремится данные на диск записать как можно быстрее, а перед записью их все надо занести в журнал. А если там еще и индексы изменением затрагиваются - вообще атас. Можно попробовать например вставлять данные во временную, потом там изменять и уже только потом записывать в постоянную, есть шанс съэкономить на перестройке индексов и логгировании, вроде как в темпдб запись в лог кешируется более гуманно.
22 окт 09, 17:42    [7825908]     Ответить | Цитировать Сообщить модератору
 Re: Обновление таблицы в 25,5 млн. строк  [new]
[-==-]
Member

Откуда:
Сообщений: 190
Можно попробовать например вставлять данные во временную, потом там изменять и уже только потом записывать в постоянную, есть шанс съэкономить на перестройке индексов и логгировании, вроде как в темпдб запись в лог кешируется более гуманно.

Попробовал с временной таблицей, мне показалось, что лоигрования не намного и меньше, странно, зачем ему транзакции вставки логировать в темпдб? А судя по мониторингу записи в его лог такое имеет место быть. Разницы практически никакой по скорости. Счетчик хоть и показывал 99% кэш хит ратио, но увеличение памяти с 8 до 12 и предварительная очистка кэша положительно повлияла, также удалили индексы, т.к. всё равно "лопатится" вся таблица.
26 окт 09, 10:38    [7837085]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить