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

Откуда:
Сообщений: 201
Есть таблица с правами доступа вида:
tablename(
  userID int not null,
  field1 int null,
  field2 int null,
  field3 int null
)

В таблице поле userID всегда имеет значение, а из остальных полей только одно заполнено, остальные имеют значение null. Т.е., например:
(1, 1, null, null),
(1, null, 1, null)

Таблица небольшая, всего 40000 строк. При обновлении выполняется запрос вида:
DELETE FROM
	tbl
FROM
	db.dbo.tablename AS tbl
	INNER JOIN db_temp.dbo.tablename_temp AS temp
		ON tbl.userID = temp.userID 
		AND ISNULL(tbl.field1, 0) = ISNULL(temp.field1, 0)
		AND ISNULL(tbl.field2, 0) = ISNULL(temp.field2, 0)
		AND ISNULL(tbl.field3, 0) = ISNULL(temp.field3, 0)

Обычно этот запрос выполняется за секунду. Но иногда он может выполняться по несколько минут. Таблица пока не используется, я в неё только загружаю данные из исходной базы. Т.е. одновременно к ней выполняется только один запрос и вариант когда запросы блокируют друг друга исключен. Запрос простой и данных мало, так что я не вижу причин для таких скачков во времени выполнения.
Почему запрос выполняется так долго?
21 дек 15, 03:42    [18586559]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Первичный ключ на userID обязательно сделать надо. Потом смотреть план запроса. Желательно из джойнов isnull убрать.
21 дек 15, 04:20    [18586566]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

Откуда:
Сообщений: 201
isnull я специально вставил, т.к. нужно сравнивать значения null в строках.
Можно оставить:
		tbl.field1 = temp.field1
		OR tbl.field2 = temp.field2
		OR tbl.field3 = temp.field3

, но я на всякий случай ищу полное совпадение по всем полям. Могу заменить
ISNULL(tbl.field1, 0) = ISNULL(temp.field1, 0)

на:
tbl.field1 = temp.field1 or (tbl.field1 is null and temp.field1 is null)

, но, как мне показалось, производительность от такого не изменится.

Есть кластеризованный уникальный индекс по всем полям, и некластеризованные уникальные по полям: (field1, userID), (field2, userID), (field3, userID)
21 дек 15, 04:42    [18586575]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

Откуда:
Сообщений: 201
Для запроса
SELECT *
FROM
	db.dbo.tablename AS tbl
	INNER JOIN db_temp.dbo.tablename_temp AS temp
		ON tbl.userID = temp.userID 
		AND ISNULL(tbl.field1, 0) = ISNULL(temp.field1, 0)
		AND ISNULL(tbl.field2, 0) = ISNULL(temp.field2, 0)
		AND ISNULL(tbl.field3, 0) = ISNULL(temp.field3, 0)

план выполнения выдал что 93% затрат занял Clustered index seek по кластеризованному индексу (как я уже писал индекс включает все поля)
21 дек 15, 04:50    [18586577]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
Скорее всего, после вставки сбивается статистика и строится неверный план. Делайте update statistics tablename, либо пропишите в запросе хинт.
21 дек 15, 04:53    [18586578]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

Откуда:
Сообщений: 201
3unknown, спасибо, попробую
21 дек 15, 05:06    [18586583]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Mike_za
Member

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

А во второй таблице сколько строк? Выложите план запроса
21 дек 15, 10:45    [18587280]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
3unknown
Первичный ключ на userID обязательно сделать надо. Потом смотреть план запроса. Желательно из джойнов isnull убрать.
AND EXISTS(SELECT tbl.field1,tbl.field2,tbl.field3 INTERSECT temp.field1,temp.field2,temp.field3)
21 дек 15, 10:57    [18587338]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
Но иногда он может выполняться по несколько минут.

Рост журнала транзакций, например.

Max_11111
Т.е. одновременно к ней выполняется только один запрос и вариант когда запросы блокируют друг друга исключен.

Один ваш коннект не означает того, что больше нет блокировок.
21 дек 15, 11:05    [18587384]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

Откуда:
Сообщений: 201
3unknown
update statistics tablename

Не помогло. Обновление статистики зависло и отвалилось по таймауту.
Какие именно хинты вы бы написали в запросе?

Mike_za
А во второй таблице сколько строк?

10-20

iap
AND EXISTS(SELECT tbl.field1,tbl.field2,tbl.field3 INTERSECT temp.field1,temp.field2,temp.field3)

А для инструкции DELETE/UPDATE INTERSECT будет работать?
DELETE FROM
	tbl
FROM
	db.dbo.tablename AS tbl
	INNER JOIN db_temp.dbo.tablename_temp AS temp
		ON tbl.userID = temp.userID 
		AND ISNULL(tbl.field1, 0) = ISNULL(temp.field1, 0)
		AND ISNULL(tbl.field2, 0) = ISNULL(temp.field2, 0)
		AND ISNULL(tbl.field3, 0) = ISNULL(temp.field3, 0)


Glory
Один ваш коннект не означает того, что больше нет блокировок.

Данные я загружаю 1 раз в сутки. В один момент загружаются данные только в одну таблицу. операции insert/update/delete выполняются тоже последовательно. SELECT к этой таблице не делаются вообще. Что еще может блокировать данную таблицу?

Glory
Рост журнала транзакций, например.

Уточните, пожалуйста, вашу мысль. При чем тут рост журнала?
22 дек 15, 07:30    [18592110]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
aleks2
Guest
iap
3unknown
Первичный ключ на userID обязательно сделать надо. Потом смотреть план запроса. Желательно из джойнов isnull убрать.
AND EXISTS(SELECT tbl.field1,tbl.field2,tbl.field3 INTERSECT temp.field1,temp.field2,temp.field3)


Полумеры не нужны.

DELETE tbl
   FROM db.dbo.tablename AS tbl
   where EXISTS(SELECT tbl.field1,tbl.field2,tbl.field3 INTERSECT field1, field2, field3 from b_temp.dbo.tablename_temp )
22 дек 15, 07:59    [18592154]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
Данные я загружаю 1 раз в сутки. В один момент загружаются данные только в одну таблицу. операции insert/update/delete выполняются тоже последовательно. SELECT к этой таблице не делаются вообще. Что еще может блокировать данную таблицу?

Меня всегда удивляли люди, которые используют свой мозг вместо документированных средств мониторинга.
Как ваше объяснение ваших действий что-то вообще говорит о наличии и количестве блокировок ?

Max_11111
Уточните, пожалуйста, вашу мысль. При чем тут рост журнала?

Потому, что все изменения пишутся в журнал. А для этого нужно место. А место не появляется вдруг.
22 дек 15, 08:07    [18592171]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

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

Места на диске для журнала достаточно, к тому же я его время от времени усекаю.

Как можно проверить наличие блокировок на таблице?
22 дек 15, 08:13    [18592181]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
Места на диске для журнала достаточно,

При чем тут место на диске ? Когде кончится место на диске вы об этом узнаете из явного сообщения об ошибке

Max_11111
Как можно проверить наличие блокировок на таблице?

Сюрприз - средствами мониторинга. Хотя бы Activity Manager
22 дек 15, 08:21    [18592192]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
aleks2
Guest
Max_11111
к тому же я его время от времени усекаю.


Когда коту делать нечего - он усекает журнал.

ЗЫ. Помимо блокировок, сервер может быть занят и другими делами. Так что не ищите черную кошку в темной комнате - нема ее там.
Лучше запрос напишите по челеовечеси и индексы на таблицах обеспечте.
22 дек 15, 08:25    [18592198]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

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

По монитору активности проверял, другие запросы к таблице не выполнялись
22 дек 15, 08:25    [18592200]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
По монитору активности проверял, другие запросы к таблице не выполнялись

Вас не просят проверять другие запросы
Вас просят проверять ваш запрос в тот момент,когда " скачет время выполнения запроса"
22 дек 15, 08:27    [18592211]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

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

aleks2
Лучше запрос напишите по человечески и индексы на таблицах обеспечьте


Уникальные индексы есть по каждому полю + уникальный кластеризованный по всем полям.
Предложите свой вариант запроса. Я написал тот, который мне казался наиболее правильным.
22 дек 15, 08:30    [18592220]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
Уникальные индексы есть по каждому полю + уникальный кластеризованный по всем полям.

Наличие индекса не означает его автоматическое использование
Тем более, когда в запросе у вас сравниваютяс не поля, включенные в индексы, а какие то вычисляемые выражения, которых в индексе никогда не было.
22 дек 15, 08:34    [18592230]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
Не помогло. Обновление статистики зависло и отвалилось по таймауту.

Странно. Фрагментацию посмотрите. Возможно стоит индексы перестроить.
22 дек 15, 19:28    [18596084]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
Какие именно хинты вы бы написали в запросе?


Судя по тому, что вторая табличка содержит совсем мало записей, Вам подойдет hash join. Я так понимаю, запрос иногда вместо него начинает использовать loop join, от этого все дольше работает. Поэтому предложил Вам апдейтить статистику, но странно, что она у Вас не апдейтится.
22 дек 15, 21:23    [18596435]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Max_11111
3unknown
update statistics tablename

Не помогло. Обновление статистики зависло и отвалилось по таймауту.
Какому еще таймауту? У вас что в SSMS таймаут включен на запросы?
Max_11111
Какие именно хинты вы бы написали в запросе?
Те которые нужны исходя из плана выполнения, который вы нам еще вчера любезно предоставили.
Max_11111
Mike_za
А во второй таблице сколько строк?

10-20
На этой таблице обновление статистики тоже падает?
22 дек 15, 23:58    [18597058]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
Max_11111
Member

Откуда:
Сообщений: 201
3unknown
Странно. Фрагментацию посмотрите. Возможно стоит индексы перестроить.

На такой маленькой табличке, по моему мнению, фрагментация индексов особо не скажется на скорости выполнения запросов. У настроена автоматическая реиндексация/дефрагментация индексов ежедневно и еженедельно, так что фрагментация в пределах допустимого.

План выполнения для запроса DELETE прикрепляю.

Учитывая, что из полей field1, field2, field3 только одно не null, и null не равно null решил изменить соединение:
		ON tbl.userID = temp.userID 
		AND (tbl.field1 = temp.field1
			OR tbl.field2 = temp.field2 
			OR tbl.field3 = temp.field3)

Результат выполнения такой же.

К сообщению приложен файл (tt.sqlplan - 79Kb) cкачать
23 дек 15, 03:49    [18597298]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
План показывает, что в большой таблице estimated number of rows = 1. Исходя из этого выбирается nested loops inner join.
Говорю же, у вас со статистикой проблема. Почему не апдейтите? Либо ставьте хинт inner hash join.
23 дек 15, 05:05    [18597311]     Ответить | Цитировать Сообщить модератору
 Re: Почему скачет время выполнения запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
3unknown
План показывает, что в большой таблице estimated number of rows = 1. Исходя из этого выбирается nested loops inner join.
Говорю же, у вас со статистикой проблема. Почему не апдейтите? Либо ставьте хинт inner hash join.
Не сбивайте ТС с толка.
Для внутренней стороны NL оценочное число строк есть estimated number of rows * estimated number of executions.


Max_11111,

Вы утверждаете, что запрос, план которого вы привели, выполнялся больше минуты?
Тогда ищите причины долгого выполнения вне его.
Соберите статистику ожиданий индивидуально для этого запроса, тогда сразу будет видно где проблема.
23 дек 15, 10:24    [18597874]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить