Как повысить производительность SQL Server Выберите запрос?

Следующий запрос T-SQL занимает 54 секунды для выполнения:

SELECT top (3) a.c1, b.c2, c.c3, d.c4 FROM table1 as a WITH (NOLOCK) JOIN table2 as b WITH (NOLOCK) ON a.c1 = b.c4 LEFT JOIN table3 as c WITH (NOLOCK) ON a.c1 = c.c4 LEFT JOIN table4 as d WITH (NOLOCK) ON b.c3 = d.c1 WHERE b.source = '8R' ORDER BY b.RecvdDate ASC 

Если один и тот же запрос, отсортированный по убыванию, выполняется в течение секунды

 SELECT top (3) a.c1, b.c2, c.c3, d.c4 FROM table1 as a WITH (NOLOCK) JOIN table2 as b WITH (NOLOCK) ON a.c1 = b.c4 LEFT JOIN table3 as c WITH (NOLOCK) ON a.c1 = c.c4 LEFT JOIN table4 as d WITH (NOLOCK) ON b.c3 = d.c1 WHERE b.source = '8R' ORDER BY b.RecvdDate DESC 

Итак, как я могу улучшить производительность запроса для заказов сортировки?

Я использую Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) Standard Edition (64-разрядная версия) в Windows NT 6.2 (сборка 9200:)

План выполнения с использованием SET STATISTICS PROFILE ON следующим образом:

 Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------------------------------------------- -------- ------------------ 3 1 SELECT top 3 a.c1, b.c2, c.c3, d.c4 FROM table1 as a WITH (NOLOCK) JOIN table2 as b WITH (NOLOCK) ON a.c1 = b.c1 LEFT JOIN table3 as c WITH (NOLOCK) ON 1 1 0 NULL NULL NULL NULL 3 NULL NULL NULL 0.1093194 NULL NULL SELECT 0 NULL 3 1 |--Top(TOP EXPRESSION:((3))) 1 2 1 Top Top TOP EXPRESSION:((3)) NULL 3 0 3E-07 93 0.1093194 [a].[c1], [table2].[c2], [c].[c3], [d].[c4] NULL PLAN_ROW 0 1 3 1 |--Nested Loops(Left Outer Join, WHERE:([DB].[dbo].[table3].[c3] as [table3].[c3]=[DB].[dbo].[table4].[c1] as [d].[c1])) 1 3 2 Nested Loops Left Outer Join WHERE:([DB].[dbo].[table2].[c3] as [table2].[c3]=[DB].[dbo].[table4].[id] as [d].[id]) NULL 3 0 221.8893 101 0.1093191 [a].[c1], [table2].[c2], [table2].[RecvdDate], [c].[c3], [d].[c4] NULL PLAN_ROW 0 1 3 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH) 1 4 3 Nested Loops Left Outer Join OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH NULL 3 0 18.49077 99 0.1057252 [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3], [c].[c3] NULL PLAN_ROW 0 1 3 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH) 1 6 4 Nested Loops Inner Join OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH NULL 3 0 18.78465 31 0.09258068 [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3] NULL PLAN_ROW 0 1 27 1 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH) 1 8 6 Nested Loops Inner Join OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH NULL 22.09678 0 136.1955 41 0.07926979 [table2].[c4], [table2].[c2], [table2].[RecvdDate], [table2].[C3] NULL PLAN_ROW 0 1 19928681 1 | | | |--Index Scan(OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD) 1 10 8 Index Scan Index Scan OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD [table2].[c1], [table2].[RecvdDate] 22.09678 66.78609 35.84109 19 0.003349548 [table2].[c1], [table2].[RecvdDate] NULL PLAN_ROW 0 1 27 19928681 | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]), 1 12 8 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]), WHERE:([DB].[dbo].[table2].[Source] [table2].[c4], [table2].[c2], [table2].[C3] 3.047679 0.003125 0.0001581 77 0.07582787 [table2].[c4], [table2].[c2], [table2].[C3] NULL PLAN_ROW 0 23.09678 3 3 | | |--Index Seek(OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD) 1 20 6 Index Seek Index Seek OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD [a].[c1] 1 0.003125 0.0001581 11 0.02297952 [a].[c1] NULL PLAN_ROW 0 7.000085 3 3 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD) 1 21 4 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD [c].[c3] 1 0.003125 0.0001581 75 0.01313197 [c].[c3] NULL PLAN_ROW 0 4 36 3 |--Table Scan(OBJECT:([DB].[dbo].[table4] AS [d])) 1 22 3 Table Scan Table Scan OBJECT:([DB].[dbo].[table4] AS [d]) [d].[id], [d].[c4] 12 0.0032035 9.17E-05 17 0.0035703 [d].[id], [d].[c4] NO STATS:([table4].[c1]) PLAN_ROW 0 3.999999 (11 row(s) affected) 

Ссылка на план выполнения в формате xml:

table2 имеет 32 582 700 строк.

Расчетный план выглядит следующим образом.

введите описание изображения здесь

У вас есть индекс RecvdDate на RecvdDate и у плана есть ключевые поиски, чтобы получить отсутствующие столбцы и оценить предикат на source = '8R' .

SQL Server оценивает, что ему нужно будет делать 22 -23 таких запросов, прежде чем он найдет TOP 3 сопоставимых строк и сможет выйти.

Это предполагает, что строки, соответствующие предикату 8R , рассеиваются равномерно по отношению к дате. В вашем случае это не так, и все они на поздних датах. Фактически это фактически составляет 19 928 681 поисковых запросов (из полученного отчета STATISTICS PROFILE ), а не оценивается примерно 20.

Самый простой способ исправить это – предоставить индекс для source, RecvdDate . Что касается этого запроса, не имеет значения, в каком направлении. Любая из четырех возможностей будет работать.

 source asc, RecvdDate asc source asc, RecvdDate desc source desc, RecvdDate asc source desc, RecvdDate desc 

Он может по-прежнему выполнять поиск равенства по source и перемещаться по совпадающим строкам вперед или назад по мере необходимости, чтобы получить RecvdDate ASC или RecvdDate DESC .

Соответствует ли эта производительность? С такой большой разницей я предполагаю, что у вас есть сводный индекс на

 source, RecvdDate DESC 

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

Вы можете добавить отдельные индексы для source и RecvdDate которые повысят производительность (с индексом одного столбца, порядок не имеет значения) или добавьте еще один составной индекс с RecvdDate в порядке возрастания .

  • Почему я получаю ошибку ограничения внешнего ключа
  • Низкая производительность ITVF по сравнению с различными параметрами vs ad hoc
  • Таблица T-SQL - XML-SQL Server
  • Ошибка подтверждения подтвержденного подтверждения SQL Server
  • Как изменить настройки предупреждения SSMS на SQL без каких-либо запросов?
  • Проверка пакетов dtsx за пределами Visual Studio
  • Блок TRY / CATCH vs SQL проверяет
  • Возвращать записи с подсчетом за последние 24 часа
  • Запуск всех файлов SQL в нескольких каталогах
  • sql-запрос для расчета времени прибытия автобуса в зависимости от времени вылета, времени от начала и остановки
  • группировка и переключение столбцов и строк
  • Interesting Posts

    получение недопустимой ошибки столбца в SQL Server

    SQL Novice – подзапрос

    Какие другие преподаватели учат курсы, которые учат Джон Каллен.SQL

    EFCore поведение отката транзакции, если процесс убит

    Большая разница производительности SQL при использовании SELECT TOP x, даже когда x намного выше выбранных строк

    SQL Query, чтобы найти последний день месяца через 6 месяцев

    Различные схемы производят ошибку получения данных из одного или нескольких осколков. Исходное сообщение об ошибке было: Недопустимое имя объекта

    SQL Server NOLOCK с JOIN, массовая загрузка

    SqlDependency / Service Broker в LocalDB, реализующий Signal R

    Возможно ли предотвратить пакетное обновление на уровне базы данных sql?

    Подключитесь непосредственно через экземпляр в C #

    Устранение неполадок параметров

    linq и параллелизм – SQL Server

    Веб-пользователи, которые ищут слишком много данных

    Понимание рекурсивной проверки окончания CTE

    Давайте будем гением компьютера.