Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
RomanH Member Откуда: Сообщений: 539 |
Добрый день! Помогите пожалуйста решить проблему. Имеется таблица: CREATE TABLE [dbo].[GoodsSale] ( [Id] INT IDENTITY(1,1) NOT NULL, [IdGoods] INT NOT NULL, [IdStore] INT NOT NULL, [IdSalesPoint] INT NOT NULL, [Date] DATETIME NOT NULL, [SalesDocCount] INT NOT NULL, [ChequesDocCount] INT NOT NULL, [SalesSaledQuantity] MONEY NOT NULL, [ChequesSaledQuantity] MONEY NOT NULL, [SalesSupplierSum] MONEY NOT NULL, [ChequesSupplierSum] MONEY NOT NULL, [SalesSalesSum] MONEY NOT NULL, [ChequesSalesSum] MONEY NOT NULL, [RowVersion] TIMESTAMP NOT NULL, CONSTRAINT [PK_GoodsSale] PRIMARY KEY NONCLUSTERED ([Id]), CONSTRAINT [UQ_GoodsSale$IdGoods$IdStore$Date] UNIQUE CLUSTERED ([IdGoods],[IdStore],[Date]), CONSTRAINT [FK_GoodsSale$IdGoods] FOREIGN KEY(IdGoods) REFERENCES Goods(Id), CONSTRAINT [FK_GoodsSale$IdStore] FOREIGN KEY(IdStore) REFERENCES Store(Id), CONSTRAINT [FK_GoodsSale$IdSalesPoint] FOREIGN KEY(IdSalesPoint) REFERENCES SalesPoint(Id), ) CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date$IdGoods$IdSalesPoint] ON [dbo].[GoodsSale] ([Date]) INCLUDE ([IdGoods],[IdSalesPoint],[SalesDocCount],[ChequesDocCount]) CREATE NONCLUSTERED INDEX [IX_GoodsSale$Date$IdGoods$IdStore] ON [dbo].[GoodsSale] ([Date]) INCLUDE ([IdGoods],[IdStore],[SalesDocCount],[ChequesDocCount]) CREATE NONCLUSTERED INDEX [IX_GoodsSale$IdSalesPoint$Date] ON [dbo].[GoodsSale] ([IdSalesPoint],[Date]) INCLUDE ([IdGoods],[SalesDocCount],[ChequesDocCount],[SalesSaledQuantity],[ChequesSaledQuantity]) Которую необходимо обновить, добавить новые записи, и удалить некоторые записи. В таблице GoodsSale порядка 15 млн. записей. Также имеется временная таблица #GoodsSale по структуре идентична GoodsSale, за исключением индексов и поля Id. Кол-во записей в #GoodsSale 134 тыс. записей. Время выполнения скрипта, который обновляет данные в GoodsSale, не всегда примерно одинаковое. Т.е. Первый раз выполняется - 20 секунд Второй раз выполняется - 2 минуты. Третий раз может выполнится за 10 секунд. При этом кол-во данных в таблицах не меняю, сервер установлен локально. Есть планы для первого раза и второго(Выложу через минуту). Помогите сделать так, чтобы скрипт выполнялся всегда 20 секунд. Спасибо! |
15 янв 14, 16:52 [15420080] Ответить | Цитировать Сообщить модератору |
RomanH Member Откуда: Сообщений: 539 |
План для 1 раза К сообщению приложен файл (Plans.zip - 48Kb) cкачать ![]() |
15 янв 14, 16:54 [15420104] Ответить | Цитировать Сообщить модератору |
RomanH Member Откуда: Сообщений: 539 |
В архиве Plans.zip планы для случая когда скрипт выполнялся 20 секунд - Opt1 и для случая когда выполнялся 2 минуты Opt3 Еще раз спасибо! |
15 янв 14, 17:00 [15420158] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
RomanH, Добрый день. Есть несколько вопросов. - Какая версия сервера? - У вас приведены планы для всего пакета в целом, какой именно запрос тормозит (используйте set statistics time, чтобы определить)? Пока скажу то, что видно из планов сейчас. Предположительно проблема во втором запросе (по крайней мере, там в планах есть отличия в количестве строк). UPDATE [dbo].[GoodsSale] SET [SalesDocCount] = 0 ,[ChequesDocCount] = 0 ,[SalesSaledQuantity] = 0 ,[ChequesSaledQuantity] = 0 ,[SalesSupplierSum] = 0 ,[ChequesSupplierSum] = 0 ,[SalesSalesSum] = 0 ,[ChequesSalesSum] = 0 FROM [dbo].[GoodsSale] gs INNER JOIN (SELECT IdStore, IdGoods, DATE = MIN(DATE) FROM #DATA GROUP BY IdStore, IdGoods) a ON a.IdGoods = gs.IdGoods AND a.IdStore = gs.IdStore WHERE gs.Date >= a.Date and gs.[IdSalesPoint] = 1 Обратите внимание на число строк: ![]() Предоставленные вами планы обновления являются per-index (или еще называют wide) update планами. Это означает что каждый индекс обновляется отдельно. В плане можно увидеть обновление кластерного индекса (самая правая иконка), после этого обновляются еще три не кластерных индекса. В случае медленного плана, эти три индекса реально обновляются, обновляется 200 000 строк, плюс к этому, предварительно эти строки сортируются, возможно, в этом кроется причина медленного выполнения. В случае быстрого плана, обновляется кластерный индекс, но не кластерные индексы реально не обновляются. Во всяком случае, это самая большая разница, которую я заметил. Кроме того, поведение подходит под описание «один раз медленно, другой быстро», объясню почему. Оператор фильтра Откуда взялся и что делает оператор фильтра, после которого наблюдается такая разница в строках? Это оптимизация, которая призвана фильтровать не обновляющие обновления, впервые была представлена в SQL Server 2005. Суть ее заключается в том, чтобы предотвратить обновление строк в не кластерных индексах, которые реально ничего не обновляют, например, нет смысла обновлять значение 0 на 0. Как это работает лучше разобрать на простом примере. use tempdb; create table t1(a int not null,b int not null,c int not null); go create unique clustered index cix_a on t1(a,b); create index ix_c on t1(c); go insert t1(a,b,c) select number,number,number from master..spt_values where type = 'p'; go begin tran; set statistics io,xml on; --1 time update t1 set a = 0, c = 0 option( recompile ,querytraceon 8790 -- Force Wide Update Plan ); --2 time update t1 set a = 0, c = 0 option( recompile ,querytraceon 8790 -- Force Wide Update Plan ); set statistics io,xml off; rollback tran; go drop table t1; В плане нас интересуют два Compute Scalar, при помощи этих операторов сервер определяет, было ли реально обновлено значение на 0. ![]() 1) Первый Compute Scalar проверяет: «Равно ли значение поля a нулю (т.е. обновляемому значению)», «Равно ли значение поля b нулю» 2) Второй Compute Scalar проверяет, равны ли оба поля нулю 3) И, наконец, Filter пропускает к обновлению только те строки, в которых столбцы не равны нулю, т.к. строки равные нулю обновлять на ноль смысла нет. Наконец, число логических чтений. В случае, когда данные обновляются: Table 't1'. Scan count 1, logical reads 16391 При повторном обновлении, когда обновлять уже фактически нечего: Table 't1'. Scan count 1, logical reads 8203 Что делать - Прежде всего, выяснить, действительно ли затык в этом, т.к. вроде бы конечно, похоже, но возможно я ошибаюсь, посмотрите, что именно скрипт тормозит. - Посмотрите на ожидания, если индексы не затрагиваются, то запрос не будет ждать и наоборот. Чтобы это пронаблюдать, в моем простом примере, выполните последовательность действий в двух окнах SSMS:
- Посмотрите что у вас вообще со скоростью записи? - Я, конечно, не знаю задачи, но посмотрите, может быть можно заменить все ваши запросы одним Merge? П.С. А что делает третий запрос в пакете? Зачем апдейтить таблице саму себя, теми же самыми значениями?
|
||
16 янв 14, 16:07 [15425506] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
SomewhereSomehow, поправочка, в оригинальном запросе этого условия не было ... and gs.[IdSalesPoint] = 1 но на точность стрельбы не влияет, осталось от моего эксперимента, когда лепил план как у ТС |
16 янв 14, 17:45 [15426033] Ответить | Цитировать Сообщить модератору |
Mnior Member Откуда: Кишинёв Сообщений: 6723 |
Очень логичная. Стоит практически 0 ресурсов, но экономит жуть. Спасибо, что обращаете внимания на такие тонкие вещи. Хотя начальник в таких ситуациях говорит: На одну пиздюдину меньше. (с) Простите мой французский. Вспоминается, по этому поводу у меня была тема: UPDATE, динамический SET Встаёт вопрос об индексах на представлениях - работает ли и там это ? Ну и конечно хорошо бы если бы это как-то влияло на функцию Updated() в триггерах (если по всем строкам не менялось). |
||
17 янв 14, 00:34 [15427598] Ответить | Цитировать Сообщить модератору |
RomanH Member Откуда: Сообщений: 539 |
Спасибо Вам что обратили внимание на мою проблему! 1.Версия сервера на котором были построены планы : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) Но к сожалению, я должен поддерживать 2005 сиквел, поэтому MERGE мне не подходит. 2.Все верно, указанный Вами update является проблемным местом. Внимательно прочитав (несколько раз) ваш ответ, сделал вывод для себя(двоешника). И переписал логику работы моего скрипта. А именно(постараюсь вкратце описать как было и как стало). Итак имеется таблица Table(15 млн. записей) и временная таблица #Table(140 тыс. записей,структура идентичная Table). Необходимо в Table: Добавить новые, обновить измененные и удалить те записи, которых нет во временной но есть в основной начинаяя от определенной даты. Было 1. В основной таблице помечал на удаление все записи начинаяя от определенной даты(индексы перестраиваются). 2. Обновлял все записи в основной таблице используя данные из временной(опять индексы перестраиваются). Приходилось обновлять очень большой диапазон, для того чтобы восстановить записи помеченные в п.1 3. Добавлял новые данные в основную таблицу(опять индексы перестраиваются). 4. Удалял из основной таблицы, те записи которые не обновились(и опять индексы перестраиваются!!!). Стало. 1. Из основной таблицы копируется не большая часть данных во временную таблицу(130 тыс.). Назову ее как #Part 2. В этой временной таблице(#Part) отмечаю те записи которые необходимо удалить, и те которые реально изменились. Как раз таки тот случай(Зачем ноль обновлять на ноль). 3. Удаляю из основной(Table) с фильтрацией по #Part(индексы перестраиваются). 4. Обновляю основную(но только те записи которые реально изменились) опять таки же с фильтрацие по #Part(индексы перестраиваются). 5. Добавляю новые данные в основную таблицу из #Table(индексы перестраиваются). Время выполнения запроса - всегда 8-10 секунд. Да, телодвижений стало больше, но индексы теперь перестраиваются уже не 4 раза, а 3. SomewhereSomehow, Вы были правы! Тормоза возникают при перестроении 3 индексов. Отказаться я от них, к сожалению, не могу, ну и минимзировать кол-во операций, которые перестраивают индексы тоже не могу. Нужны именно Insert, Update и Delete. Был бы 2008 сиквел, насколько я понимаю, индексы в моем случае перестраивались бы только два раза на Merge и Delete. SomewhereSomehow, еще раз огромное спасибо, Вы навели меня на более правильную реализацию. P.S. Еще хотел спросить в какой программе вы просматриваете планы запросов? Просто у вас нагляднее выглядит чем в SSMS. |
||
17 янв 14, 18:07 [15431669] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
RomanH, Добрый день! Рад, если мой анализ вам помог, и натолкнул на идею решения проблемы. Программа для просмотра планов SQL Sentry Plan Explorer - имхо, действительно удобнее чем штатный механизм SSMS. К тому же, программа бесплатная (хотя, есть и платная версия, в которой больше плюшек, но пока мне хватает и бесплатной). |
17 янв 14, 19:26 [15431925] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |