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

Откуда:
Сообщений: 282
Добрый день, коллеги!

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

SQL ЗАПРОС:
+
SELECT a.ApplicationID, a.InstitutionID, a.ApplicationNumber, ast.Name AS StatusName, a.LastCheckDate, a.StatusID, RTRIM(ISNULL(e.LastName, '') 
                      + ' ' + ISNULL(e.FirstName, '') + ' ' + ISNULL(e.MiddleName, '')) AS EntrantName, LTRIM(ISNULL(ed.DocumentSeries, '') + ' ' + ISNULL(ed.DocumentNumber, '')) 
                      AS IdentityDocument, a.RegistrationDate, a.OriginalDocumentsReceived, a.OrderCalculatedRating AS Rating
                      --- 1) проверяем включено ли заявление в список рекомендованных ---
					,CASE WHEN EXISTS(SELECT TOP 1 1 FROM RecomendedLists rs WITH (NOLOCK) WHERE rs.ApplicationID = a.applicationID and rs.InstitutionID = a.InstitutionID) THEN 1 ELSE 0 END AS IsRecommended
                      --- 2) объединяем уникальные названия конкурсных групп для заявления в строку ---
					,SUBSTRING(
                      	(
							SELECT t.CompetitiveGroupName AS [text()]
							FROM (
									SELECT  ', ' + cg.Name AS CompetitiveGroupName
									FROM ApplicationCompetitiveGroupItem acgi WITH (NOLOCK)
										INNER JOIN  CompetitiveGroup cg WITH (NOLOCK) ON acgi.CompetitiveGroupId = cg.CompetitiveGroupId
									WHERE acgi.ApplicationId =a.ApplicationID
									GROUP BY cg.Name
							) t FOR XML PATH ('')), 3, 8000
                      ) AS CompetitiveGroupNames
                      --- 3) проверяем статус приемной кампании - завершена или нет ---
                      ,CASE WHEN (EXISTS(SELECT TOP 1 1 FROM ApplicationCompetitiveGroupItem acgi WITH (NOLOCK) 
									INNER JOIN CompetitiveGroup cg WITH (NOLOCK) ON acgi.CompetitiveGroupId = cg.CompetitiveGroupId AND acgi.ApplicationId = a.applicationId 
									INNER JOIN Campaign cmp WITH (NOLOCK) ON cg.CampaignId = cmp.CampaignId AND cmp.InstitutionID = a.InstitutionID
                                         WHERE cmp.StatusID = 2
                      )) THEN 1 ELSE 0 END AS CampaignIsFinished
					,CASE WHEN(EXISTS (SELECT TOP 1 1 FROM ApplicationCompetitiveGroupItem acgi WITH (NOLOCK) 
					INNER JOIN CompetitiveGroupItem cgi WITH (NOLOCK) on acgi.CompetitiveGroupItemId = cgi.CompetitiveGroupItemID AND acgi.ApplicationId = a.ApplicationId
					                   WHERE cgi.EducationLevelID IN (2, 3, 5, 19) 
										AND acgi.EducationFormId in (11, 12)
										AND acgi.EducationSourceId = 14
					)) then 1 else 0 end as CanIncludeInRecommended					
	FROM dbo.Application AS a WITH (NOLOCK)
		INNER JOIN dbo.ApplicationStatusType AS ast WITH (NOLOCK) ON a.StatusID = ast.StatusID
		INNER JOIN dbo.Entrant AS e WITH (NOLOCK) ON a.EntrantID = e.EntrantID
		INNER JOIN dbo.EntrantDocument AS ed WITH (NOLOCK) ON e.IdentityDocumentID = ed.EntrantDocumentID AND e.EntrantID = ed.EntrantID

Очень много съедает сортировка, индексы и т.д. (ну и конечно внутри запроса, под запросы)

Думаю каким либо способом избавиться от под запросов, но беда в том что реально записей в таблице Application = 500 (тестовых), а имен CompetitiveGroupName может быть любое количество на запись в таблице Application.

Для оптимизации поместил все это в представление (View). Какие есть варианты по оптимизации? (план выполнения могу скинуть)
Спасибо, с уважением!
13 авг 15, 10:42    [18011717]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
wiktor591
Очень много съедает сортировка, индексы
Это что-то новенькое для SELECTа.
wiktor591
Думаю каким либо способом избавиться от под запросов
А смысл? Вы почему-то думаете, что сервер обрабатывает запрос в том же виде, как Вы его написали.
Это не так.
wiktor591
Для оптимизации поместил все это в представление (View).
Оптимизатор раскрывает все VIEW перед оптимизацией. Так что не имеет смысла.

Оптимизация начинается с анализа действительного плана выполнения.
Вы его смотрели?
13 авг 15, 10:53    [18011796]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
wiktor591
Member

Откуда:
Сообщений: 282
iap,
Смотрел, большая часть съедает сортировка [dbo].[CompetitiveGroup].Name. (этот запрос дергают очень часто и количество записей доходит до 100 тысяч ) (разве View не решит немножко проблему из-за частых обращений к запросу?)
13 авг 15, 11:07    [18011881]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
edyaN
Member

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

без плана ничего сказать нельзя.
и не надо псевдооптимизаций вроде
.. EXISTS (SELECT TOP 1 1 ...)

пишите
.. EXISTS (SELECT * ...)

это сильно облегчает чтение запроса.
13 авг 15, 11:10    [18011890]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
wiktor591
Member

Откуда:
Сообщений: 282
План выполнения

К сообщению приложен файл (plan.sqlplan - 147Kb) cкачать
13 авг 15, 11:14    [18011916]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
wiktor591
iap,
Смотрел, большая часть съедает сортировка [dbo].[CompetitiveGroup].Name. (этот запрос дергают очень часто и количество записей доходит до 100 тысяч ) (разве View не решит немножко проблему из-за частых обращений к запросу?)
Нет, не решит.
13 авг 15, 11:14    [18011917]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
wiktor591
iap,
Смотрел, большая часть съедает сортировка [dbo].[CompetitiveGroup].Name. (этот запрос дергают очень часто и количество записей доходит до 100 тысяч ) (разве View не решит немножко проблему из-за частых обращений к запросу?)


Чего то я в плане никаких 100 тыщь не увидел, для начала обновите статистику на таблицах, а то там больше 3 штук записей сервер не предполагает выводить.
13 авг 15, 11:59    [18012204]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
SomewhereSomehow
Member

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

Добрый день.

Запрос довольно объемный, как и план запроса, а значит, много потенциальных мест, где что-то могло пойти не так. Поэтому я напишу те моменты, в которых могут быть потенциальные проблемы, а вы уже пробуйте и смотрите, что из этого можно применить и что сработает.
Картинка с другого сайта.

1. Оценка строк по предикату Seek Keys[1]: Prefix: [gvuz_develop].[dbo].[Entrant].InstitutionID = Scalar Operator((587)) – 880, тогда как реальное число строк 2393. Поскольку дальше идут сортировки – то как минимум 2-е из них из-за недооценки могут сливать данные в tempdb, что медленно. Учитывая, что предикат одно колоночный и константа, возможно, проблема в неактуальной и/или неполной статистике.

Возможное решение: Попробуйте обновить всю статистику на таблице с полным сканированием.

2. Сортировка и Key Lookup. Сортировка из-за недооценки может сливать данные на диск. Лучше бы вообще избавиться от этой сортировки. Причина ее появление в плане – внутренняя оптимизация SQL Server, которые сортирует входные данные по первичному ключу ApplicationID, чтобы превратить доступ к данным в кластерном индексе (Lookup) из случайного в последовательный.

Возможное решение: Избавиться от Lookup, добавив поля из Lookup в индекс idx_EntrantID как INCLUDE (возможно поле StatusID можно включить в ключ самого индекса idx_EntrantID последним).

3.Merge Join – мне он тут не нравится, поскольку является Many-to-Many Merge Join, который использует tempdb и не является лучшим вариантом. Кроме того, он требует отсортированных потоков, поэтому в плане появляются две сортировки, которые, как сказано выше могут делать Spill в tempdb.

Возможное решение: В таблице RecommendationLists создать по полям соединения покрывающий индекс (важно включить все необходимые поля из этой таблицы в создаваемый индекс). Возможно это заставит оптимизатор выбрать Nested Loops соединение. Также посмотрите, можно ли будет сделать этот индекс уникальным, чтобы исключить Many-to-Many в любом случае, даже если выгоднее снова будет Merge Join. Как крайнее средство можно использовать указание option(loops join), но лучше сделать правильный индекс.

4. Параллелизм. У вас внушительная часть плана выполняется параллельно, но если посмотреть, как строки раскидываются по потокам – видно, что в значительной части плана всю работу делает все равно один поток. Например, самый первый Nested Loops (Thread 0 – 0 rows, Thread 1 – 0 rows, Thread 2 – 2393 rows). Тем не менее идут затраты на синхронизацию параллельной работы и итераторы ее обеспечивающие. Это может вызывать паразитные CXPACKET ожидания, что тоже может приводить к замедлению.

Возможное решение: Попробовать ограничить выполнение запроса в 1 поток при помощи OPTION(MAXDOP).

5. NOLOCK. Этот хинт часто используют как «ускоритель» запросов, но иногда это может давать обратный эффект. Долго расписывать не буду, вот тут писал: http://www.queryprocessor.com/nolock-and-top-optimization/
Кроме того, может давать разные эффекты по консистентности данных, но я думаю, вы это знаете и учитываете.

Возможное решение: Отказаться от NOLOCK.

Можно начинать эксперименты с самого первого пункта по обновлению статистики, возможно, уже после этого все станет хорошо и дальнейших манипуляций (2-5) не потребуется. Нужно пробовать.
13 авг 15, 12:18    [18012333]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Проблема в Index Seek насколько я понимаю.... Пробуйте такой вариант:
SELECT
	  a.ApplicationID
	, a.InstitutionID
	, a.ApplicationNumber
	, ast.Name AS StatusName
	, a.LastCheckDate
	, a.StatusID
	, RTRIM(ISNULL(e.LastName, '') + ' ' + ISNULL(e.FirstName, '') + ' ' + ISNULL(e.MiddleName, '')) AS EntrantName
	, LTRIM(ISNULL(ed.DocumentSeries, '') + ' ' + ISNULL(ed.DocumentNumber, '')) AS IdentityDocument
	, a.RegistrationDate
	, a.OriginalDocumentsReceived
	, a.OrderCalculatedRating AS Rating
	, IIF(rs.ApplicationID IS NOT NULL, 1, 0) AS IsRecommended
	, CompetitiveGroupNames = STUFF((
        SELECT DISTINCT ', ' + cg.Name
        FROM ApplicationCompetitiveGroupItem acgi
		JOIN CompetitiveGroup cg ON acgi.CompetitiveGroupId = cg.CompetitiveGroupId
		WHERE acgi.ApplicationId = a.ApplicationID
        FOR XML PATH(''), 1, 2, '')
	, IIF(t2.ApplicationId IS NOT NULL, 1, 0) AS CampaignIsFinished
	, IIF(acgi.ApplicationId IS NOT NULL, 1, 0) CanIncludeInRecommended
FROM dbo.[Application] AS a
JOIN dbo.ApplicationStatusType AS ast ON a.StatusID = ast.StatusID
JOIN dbo.Entrant AS e ON a.EntrantID = e.EntrantID
JOIN dbo.EntrantDocument AS ed ON e.IdentityDocumentID = ed.EntrantDocumentID AND e.EntrantID = ed.EntrantID
LEFT JOIN (
	SELECT DISTINCT ApplicationID, InstitutionID -- хз NOT NULL они или нет, может и DISTINCT тут лишний
	FROM RecomendedLists
) rs ON rs.ApplicationID = a.applicationID and rs.InstitutionID = a.InstitutionID
LEFT JOIN (
	SELECT acgi.ApplicationId, cmp.InstitutionID
	FROM ApplicationCompetitiveGroupItem acgi
	JOIN CompetitiveGroup cg ON acgi.CompetitiveGroupId = cg.CompetitiveGroupId AND acgi.ApplicationId = a.applicationId 
	JOIN Campaign cmp ON cg.CampaignId = cmp.CampaignId AND cmp.InstitutionID = a.InstitutionID
	WHERE cmp.StatusID = 2
	GROUP BY acgi.ApplicationId, cmp.InstitutionID
) t2 ON t2.ApplicationId = a.ApplicationId AND t2.InstitutionID = a.InstitutionID
LEFT JOIN (
	SELECT DISTINCT acgi.ApplicationId
	FROM ApplicationCompetitiveGroupItem acgi
	JOIN CompetitiveGroupItem cgi on acgi.CompetitiveGroupItemId = cgi.CompetitiveGroupItemID 
	WHERE cgi.EducationLevelID IN (2, 3, 5, 19) 
		AND acgi.EducationFormId in (11, 12)
		AND acgi.EducationSourceId = 14
) acgi ON acgi.ApplicationId = a.ApplicationId


не поможет... структуру таблиц в студию... поскольку зная зависимости между таблица есть возможность сократить количество чтений... из ApplicationCompetitiveGroupItem и CompetitiveGroupItem
13 авг 15, 12:21    [18012351]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
wiktor591
Member

Откуда:
Сообщений: 282
SomewhereSomehow, спасибо.
13 авг 15, 12:39    [18012490]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL - запроса  [new]
wiktor591
Member

Откуда:
Сообщений: 282
AlanDenton, спасибо сейчас попробую.
13 авг 15, 12:39    [18012493]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить