Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Время выполнения не всегда примерно одинаково  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
RomanH
Member

Откуда:
Сообщений: 539
План для 1 раза

К сообщению приложен файл (Plans.zip - 48Kb) cкачать
15 янв 14, 16:54    [15420104]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
RomanH
Member

Откуда:
Сообщений: 539
В архиве Plans.zip планы для
случая когда скрипт выполнялся 20 секунд - Opt1
и для случая когда выполнялся 2 минуты Opt3
Еще раз спасибо!
15 янв 14, 17:00    [15420158]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
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:
+

1. Окно 1.
begin tran
select c from t1 with(holdlock);

Эмитируем, что на момент обновления, есть какой-то читающий запрос который держит блокировку на строках ix_c.

2. Окно 2.
update t1 set a = 0, c = 0
option(
	recompile
	,querytraceon 8790 -- Force Wide Update Plan
);


3. Окно 2. Наблюдаем ожидание запроса на обновление, пока читающий запрос не снимет свои блокировки по индексу ix_c.

4. Окно 1. Откатываем транзакцию.

5. Окно 2. Наблюдаем, что обновление успешно завершено (если его запустить повторно, то теперь, обновлять будет по сути нечего).

6. Окно 1. Повторяем действия.
begin tran
select c from t1 with(holdlock);


7. Окно 2. Повторно запускаем тот же запрос на обновление.
update t1 set a = 0, c = 0
option(
	recompile
	,querytraceon 8790 -- Force Wide Update Plan
);


8. Окно 2. Наблюдаем, что сейчас он выполнился мгновенно, т.к. ждать нечего, индекс ix_c мы не трогаем, запрос из Окна 1 никак на обновление не влияет.

9. Окно 1. Откатываем транзакцию.


- Посмотрите что у вас вообще со скоростью записи?
- Я, конечно, не знаю задачи, но посмотрите, может быть можно заменить все ваши запросы одним Merge?

П.С.
А что делает третий запрос в пакете? Зачем апдейтить таблице саму себя, теми же самыми значениями?
+
UPDATE [dbo].[GoodsSale]
	SET 		 
		 [SalesDocCount] = T.[SalesDocCount]
		,[ChequesDocCount] = T.[ChequesDocCount]
		,[SalesSaledQuantity] = T.[SalesSaledQuantity]
		,[ChequesSaledQuantity] = T.[ChequesSaledQuantity]
		,[SalesSupplierSum] = T.[SalesSupplierSum]
		,[ChequesSupplierSum] = T.[ChequesSupplierSum]
		,[SalesSalesSum] = T.[SalesSalesSum]
		,[ChequesSalesSum] = T.[ChequesSalesSum]
		,[IdSalesPoint] = T.IdSalesPoint			
	FROM #DATA c
	INNER JOIN [dbo].[GoodsSale] T ON T.IdGoods = c.IdGoods AND T.IdStore = c.IdStore AND T.Date = c.Date
16 янв 14, 16:07    [15425506]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

поправочка, в оригинальном запросе этого условия не было
... and gs.[IdSalesPoint] = 1

но на точность стрельбы не влияет, осталось от моего эксперимента, когда лепил план как у ТС
16 янв 14, 17:45    [15426033]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
Это оптимизация, которая призвана фильтровать не обновляющие обновления, впервые была представлена в SQL Server 2005.
Прикольная вещь.
Очень логичная. Стоит практически 0 ресурсов, но экономит жуть.
Спасибо, что обращаете внимания на такие тонкие вещи.

Хотя начальник в таких ситуациях говорит: На одну пиздюдину меньше. (с)
Простите мой французский.


Вспоминается, по этому поводу у меня была тема: UPDATE, динамический SET
Встаёт вопрос об индексах на представлениях - работает ли и там это ?
Ну и конечно хорошо бы если бы это как-то влияло на функцию Updated() в триггерах (если по всем строкам не менялось).
17 янв 14, 00:34    [15427598]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
RomanH
Member

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

Есть несколько вопросов.
- Какая версия сервера?
- У вас приведены планы для всего пакета в целом, какой именно запрос тормозит (используйте set statistics time, чтобы определить)?

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

        UPDATE [dbo].[GoodsSale]
	SET 		
		 [SalesDocCount] = 0
		.............................	



Спасибо Вам что обратили внимание на мою проблему!
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]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения не всегда примерно одинаково  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
RomanH,

Добрый день! Рад, если мой анализ вам помог, и натолкнул на идею решения проблемы.

Программа для просмотра планов SQL Sentry Plan Explorer - имхо, действительно удобнее чем штатный механизм SSMS. К тому же, программа бесплатная (хотя, есть и платная версия, в которой больше плюшек, но пока мне хватает и бесплатной).
17 янв 14, 19:26    [15431925]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить