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

Откуда:
Сообщений: 27
Добрый день, разбираюсь почему запрос долго выбирает данные (4 сек), столкнулся со странной проблемой
сам запрос
SELECT top 40
	[tbl_Problem].[StatusID] AS [StatusID],
	[tbl_ProblemStatus].[Name] AS [StatusName],
	[tbl_ProblemStatus].[IsFinish] AS [IsFinish],
	[tbl_Problem].[AuthorID] AS [AuthorID],
	[Author].[Name] AS [AuthorName],
	[tbl_Problem].[CIServiceID] AS [CIServiceID],
	[tbl_CIService].[Name] AS [CIServiceName],
	[tbl_Problem].[ConfigurationItemID] AS [ConfigurationItemID],
	[tbl_ConfigurationItem].[Name] AS [ConfigurationItemName],
	[tbl_Problem].[AffectsCIID] AS [AffectsCIID],
	[AffectsCI].[Name] AS [AffectsCIName],
	[tbl_Problem].[Solution] AS [Solution],
	[tbl_Problem].[PriorityID] AS [PriorityID],
	[tbl_ProblemPriority].[Name] AS [PriorityName],
	[tbl_Problem].[UrgencyID] AS [UrgencyID],
	[tbl_ProblemUrgency].[Name] AS [UrgencyName],
	[tbl_Problem].[OwnerID] AS [OwnerID],
	[Owner].[Name] AS [OwnerName],
	[tbl_Problem].[ConciseDescription] AS [ConciseDescription],
	[tbl_Problem].[RegistrationDate] AS [RegistrationDate],
	[tbl_Problem].[Number] AS [Number],
	[tbl_Problem].[ProblemTypeID] AS [ProblemTypeID],
	[tbl_ProblemType].[Name] AS [ProblemTypeName],
	[tbl_Problem].[FullDescription] AS [FullDescription],
	[tbl_Problem].[ImpactID] AS [ImpactID],
	[tbl_ProblemImpact].[Name] AS [ImpactName],
	[tbl_Problem].[ImpactDegree] AS [ImpactDegree],
	[tbl_Problem].[ID] AS [ID]
FROM
	[dbo].[tbl_Problem]  AS [tbl_Problem]
LEFT OUTER JOIN
	[dbo].[tbl_ConfigurationItem] AS [tbl_ConfigurationItem] ON [tbl_ConfigurationItem].[ID] = [tbl_Problem].[ConfigurationItemID]
LEFT OUTER JOIN
	[dbo].[tbl_ConfigurationItem] AS [AffectsCI] ON [AffectsCI].[ID] = [tbl_Problem].[AffectsCIID]
LEFT OUTER JOIN
	[dbo].[tbl_Contact] AS [Author] ON [Author].[ID] = [tbl_Problem].[AuthorID]
LEFT OUTER JOIN
	[dbo].[tbl_CIService] AS [tbl_CIService] ON [tbl_CIService].[ID] = [tbl_Problem].[CIServiceID]
LEFT OUTER JOIN
	[dbo].[tbl_ProblemPriority] AS [tbl_ProblemPriority] ON [tbl_ProblemPriority].[ID] = [tbl_Problem].[PriorityID]
LEFT OUTER JOIN
	[dbo].[tbl_ProblemUrgency] AS [tbl_ProblemUrgency] ON [tbl_ProblemUrgency].[ID] = [tbl_Problem].[UrgencyID]
LEFT OUTER JOIN
	[dbo].[tbl_Contact] AS [Owner] ON [Owner].[ID] = [tbl_Problem].[OwnerID]
LEFT OUTER JOIN
	[dbo].[tbl_ProblemType] AS [tbl_ProblemType] ON [tbl_ProblemType].[ID] = [tbl_Problem].[ProblemTypeID]
LEFT OUTER JOIN
	[dbo].[tbl_ProblemImpact] AS [tbl_ProblemImpact] ON [tbl_ProblemImpact].[ID] = [tbl_Problem].[ImpactID]
LEFT OUTER JOIN
	[dbo].[tbl_ProblemStatus] AS [tbl_ProblemStatus] ON [tbl_ProblemStatus].[ID] = [tbl_Problem].[StatusID] 
WHERE
	(
		tbl_Problem.AffectsCIID in (select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F'))
			OR
		[tbl_Problem].[OrganizationID] = 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A' 
			OR
		[tbl_Problem].[OrganizationID] = '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2'
	) 
		AND
		[tbl_ProblemStatus].[IsFinish] = 0

время выполнения - 4 сек, выдает 3 строчки
Но если в условии изменить [tbl_ProblemStatus].[IsFinish] = 0 на [tbl_ProblemStatus].[IsFinish] = 1
выбирается за 0,2 секунды.
в таблице tbl_ProblemStatus 6 строк с isfinish =0 и 3 с isfinish = 1
Выборка с условием [tbl_ProblemStatus].[IsFinish] = 1 - 1300 строк, но выбирается за 0,2 секунды
Выборка с условием [tbl_ProblemStatus].[IsFinish] = 0 - 3 строки, но выбирается за4 секунды
Помогите понять в чем может быть проблема, уже весь мозг сломал
20 янв 17, 09:07    [20125534]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iseekyouu,

а LEFT JOIN на все таблицы потому что .... ? и в частности на tbl_ProblemStatus ? 0/1 как переменные или как в примере? Обновите статистики. Ну и планы показать, было бы полезно
20 янв 17, 09:16    [20125561]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
iseekyouu,

Из мелочей:
1. LEFT OUTER JOIN [dbo].[tbl_ProblemStatus] реально INNER
2. Есть TOP, нет ORDER BY
3. Я не вижу индексов

Из крупного: очень неприятное для оптимизатора условие
tbl_Problem.AffectsCIID in (select ID from f('...)) OR tbl_Problem.OrganizationID = '...' OR tbl_Problem.OrganizationID = '...'

Для советов нужна структура таблиц и индексов, понимание что за fn_GetPathForConfigurationItem, размеры таблицы tbl_Problem и статистика по фильтруемым колонкам
20 янв 17, 09:20    [20125581]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
Speshuric,
Первый же пункт в точку, время выполнения сократилось до 1 сек, как догадались расскажите ?
20 янв 17, 09:34    [20125633]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
iseekyouu,

1 секунда - очень долго. План запроса покажите.
20 янв 17, 09:44    [20125668]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
iseekyouu
Speshuric,
Первый же пункт в точку, время выполнения сократилось до 1 сек, как догадались расскажите ?


Ошибся не тот план посмотрел, время так же 4 сек, вот планы (1.png)

функция fn_GetPathForConfigurationItem - сокращает количество ID
Если вообще убрать условие [tbl_ProblemStatus].[IsFinish] = 0 то выбирает за 0,2 сек.
Остальную запрашиваемую информацию предоставить не знаю как.

К сообщению приложен файл. Размер - 125Kb
20 янв 17, 09:49    [20125694]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
iseekyouu,

1. Создать кластеризованные индексы на все таблицы.
2. Создать индекс по tbl_Problem (StatusID) с инклюдом (OrganizationID, AffectsCIID)
3. Нанять человека, который умеет проектировать БД.
20 янв 17, 09:57    [20125742]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
aleks2
Guest
tbl_Problem.AffectsCIID in (select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F'))
			OR
		[tbl_Problem].[OrganizationID] = 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A' 
			OR
		[tbl_Problem].[OrganizationID] = '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2'


1. Переделать функцию fn_GetPathForConfigurationItem в мультистейтмент и в возвращаемой таблице повесить на ID уникальный индекс.

2. Засунуть 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A' и '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2' в табличную переменную и вместо or сделать inner join.
20 янв 17, 10:01    [20125764]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Добрый Э - Эх
Guest
Speshuric
1. Создать кластеризованные индексы на все таблицы. ...
прям вот так сходу, не зная состава и объема данных и режима эксплуатации таблиц?
20 янв 17, 10:18    [20125826]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
Добрый Э - Эх,

На все те, что в этом запросе: скорее всего - да. Посмотрите на план.
20 янв 17, 10:27    [20125886]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
[quot aleks2]
tbl_Problem.AffectsCIID in (select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F'))
			OR
		[tbl_Problem].[OrganizationID] = 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A' 
			OR
		[tbl_Problem].[OrganizationID] = '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2'


1. Переделать функцию fn_GetPathForConfigurationItem в мультистейтмент и в возвращаемой таблице повесить на ID уникальный индекс. 

2. Засунуть 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A'  и '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2' в табличную переменную и вместо or сделать inner join.[/quot]

Возможно я чего-то не понимаю, но эти условия не влияют на быстродействие, проблема понять почему это условие [tbl_ProblemStatus].[IsFinish] = 0/1 так влияет...
20 янв 17, 10:33    [20125911]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
iseekyouu,

iseekyouu
проблема понять почему это условие [tbl_ProblemStatus].[IsFinish] = 0/1 так влияет

Это как раз не проблема. У вас в плане видно, что разница в 20 раз по количеству записей (428к и 25к)
20 янв 17, 10:44    [20125957]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
дайте в нормальном виде планы
20 янв 17, 10:55    [20126010]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
TaPaK
дайте в нормальном виде планы

Скажите как, я не придумал как по другому, кроме как картинкой
20 янв 17, 11:21    [20126191]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
Speshuric
iseekyouu,

iseekyouu
проблема понять почему это условие [tbl_ProblemStatus].[IsFinish] = 0/1 так влияет

Это как раз не проблема. У вас в плане видно, что разница в 20 раз по количеству записей (428к и 25к)


ага, но количество записей меняется, только при изменении этого условия, другие не меняются
20 янв 17, 11:22    [20126199]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
1) select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F') в табличную переменую результат
2) 2 индекса по tbl_Problem.AffectsCIID и по tbl_Problem.[OrganizationID]
3) подзапрос через UNION ALL

with preSel as(
select ...
FROM
	[dbo].[tbl_Problem]  AS [tbl_Problem]
INNER JOIN @fn_GetPathForConfigurationItem f on tbl_Problem.AffectsCIID = f.ID
INNER JOIN [dbo].[tbl_ProblemStatus] AS [tbl_ProblemStatus] ON [tbl_ProblemStatus].[ID] = [tbl_Problem].[StatusID] 
WHERE [tbl_ProblemStatus].[IsFinish] = 0
UNION ALL
select...
FROM
	[dbo].[tbl_Problem]  AS [tbl_Problem]

INNER JOIN [dbo].[tbl_ProblemStatus] AS [tbl_ProblemStatus] ON [tbl_ProblemStatus].[ID] = [tbl_Problem].[StatusID] 
WHERE [tbl_ProblemStatus].[IsFinish] = 0
AND [tbl_Problem].[OrganizationID] in( 'C8C54382-6B10-4095-B3CC-FFAF1C11BD9A', '7D9BDA55-6194-4B6F-BE8C-31EEE13F8AB2'))

select top 40...
from preSel
LEFT OUTER JOIN
	[dbo].[tbl_ConfigurationItem] AS [tbl_ConfigurationItem] ON [tbl_ConfigurationItem].[ID] = [tbl_Problem].[ConfigurationItemID]
....
20 янв 17, 11:24    [20126206]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
iseekyouu
Скажите как, я не придумал как по другому, кроме как картинкой
Открыть план в студии, ПКМ - > Save Execution Plan as
20 янв 17, 11:27    [20126231]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Мистер Хенки,

дичь какая-то

1) select ID from fn_GetPathForConfigurationItem('FDC83724-F48B-4590-80CA-96F8F158347F') в табличную переменую результат
ну может быть с большой натяжкой, влияние на результат всё равно 0

2) 2 индекса по tbl_Problem.AffectsCIID и по tbl_Problem.[OrganizationID]
и оба индекса будут использоваться в одном предикате?

3) подзапрос через UNION ALL
гори сарай гори и хата....
20 янв 17, 11:31    [20126257]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
iseekyouu
Member

Откуда:
Сообщений: 27
invm
iseekyouu
Скажите как, я не придумал как по другому, кроме как картинкой
Открыть план в студии, ПКМ - > Save Execution Plan as


К сообщению приложен файл (SQL.7z - 12Kb) cкачать
20 янв 17, 11:32    [20126267]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iseekyouu,

у вас AUTO_UPDATE_STATISTICS включён или хоть сами обновляете?
20 янв 17, 11:51    [20126418]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
Мистер Хенки,

дичь какая-то


2) 2 индекса по tbl_Problem.AffectsCIID и по tbl_Problem.[OrganizationID]
и оба индекса будут использоваться в одном предикате?

3) подзапрос через UNION ALL
гори сарай гори и хата....

зачем в одном то.
два запроса с обьединением результатов union all
я же примерно написал как выглядит. В одном поиск по tbl_Problem.AffectsCIID в другом по tbl_Problem.[OrganizationID]
20 янв 17, 12:16    [20126546]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Мистер Хенки

зачем в одном то.
два запроса с обьединением результатов union all
я же примерно написал как выглядит. В одном поиск по tbl_Problem.AffectsCIID в другом по tbl_Problem.[OrganizationID]

Извиняюсь, но наверное даже еще лучше будет индексы на [tbl_Problem]
1)AffectsCIID, [StatusID]
2)[OrganizationID],[StatusID]
20 янв 17, 12:21    [20126564]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
invm
Member

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

Заведите на таблицах ПК, если их нет. Или уникальные индексы.
Иначе бестолку оптимизацией заниматься.
20 янв 17, 12:47    [20126679]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с оптимизацией запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
invm
iseekyouu,

Заведите на таблицах ПК, если их нет. Или уникальные индексы.
Иначе бестолку оптимизацией заниматься.

А кстати да, похоже на половине таблиц из запроса нет первичных ключей- иначе зачем они сканируются, ну и на tbl_Problem не хватает индексов по полям на которых фильтрующие условия есть
20 янв 17, 13:05    [20126779]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить