Высокое время выполнения рассылки при изменении значения сравнения по запросу

У меня проблема с временем выполнения запроса, который меня озадачивает. Я знаю несколько способов решить проблему и получить лучшее и приемлемое время выполнения, но до сих пор не знаю, почему проблема возникает.

Примеры таблиц

У нас есть две таблицы, связанные с внешним ключом.

Таблица 1

| Id | IdTable2 | |:--:|:--------:| | 1 | 4 | | 2 | 7 | | 3 | 8 | | 4 | 6 | | 5 | 4 | | 6 | 1 | | 7 | 1 | | 8 | 6 | | 9 | 7 | | 10 | 1 | 

Таблица 2

 | Id | ValueField | |:--:|:----------:| | 1 | 0 | | 2 | 0 | | 3 | 0 | | 4 | 1 | | 5 | 0 | | 6 | 1 | | 7 | 0 | 

запрос

 SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = ?); 

Где ? может быть 0 или 1

Количество реальных данных

Вышеприведенные таблицы являются просто образцом для упрощения, но реальные значения строк этих таблиц следующие:

  • Таблица 1: 60420 строк
  • Таблица 2: 62 строки

  • Таблица 2 с ValueField 0 : 51 строк

  • Таблица 2 с ValueField 1 : 11 строк

  • Таблица 1 с IdTable2 с ValueField 0 : 599 строк

  • Таблица 1 с IdTable2 с ValueField 1 : 59821 строк

Проблема

 SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0); -- Execution time LOW/INSTANT SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 1); -- Execution time HIGH 

Ну, в первую очередь, я думаю, что подзапрос был борьбой, но если подзапрос является проблемой, разные значения не будут выполняться в так много раз, поэтому я предполагаю, что, вероятно, объем данных, полученных, был проблемой, поэтому я пробую эту :

 SELECT * FROM Table1 WHERE IdTable2 IN (1,2,3,5,7); -- Equivalent of ValueField 0 -- Execution time LOW/INSTANT SELECT * FROM Table1 WHERE IdTable2 IN (4,6); -- Equivalent of ValueField 1 -- Execution time LOW/INSTANT 

Ну … полученные данные не так, попробуем что-то еще:

 SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0); -- Execution time LOW/INSTANT SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 0); -- Execution time LOW/INSTANT 

Что произойдет, если я отменил его?

 SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 1); -- Execution time LOW/INSTANT SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0); -- Execution time LOW/INSTANT 

Hummm …. это в значительной степени говорит мне, что проблема не в подзапросе и ни на данных, но почему сравнение с ValueField = 1 И использование IN вызывает проблему, и ни один из альтернатив не может реплицировать время выполнения HIGH?

Планы выполнения

Для SQL IN ValueField 1 :

 SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1); 

http://s000.tinyupload.com/index.php?file_id=19036217708532467879

Для SQL IN ValueField 0 :

 SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0); 

http://s000.tinyupload.com/index.php?file_id=49593927895920014301

Для SQL NOT IN ValueField 0 :

 SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0); 

http://s000.tinyupload.com/index.php?file_id=03901091628843565847

Для SQL NOT IN ValueField 1 :

 SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1); 

http://s000.tinyupload.com/index.php?file_id=69996775965382534356

Запросы – это то же самое сообщение, что и в примере, но с другими именами, это словарь эквивалентности образца запроса и реального запроса.

  • Таблица 1 : Инциденции
  • Таблица2: EstadosWorkflows
  • IdTable2 : EstadoWorkflow
  • Table2.Id : IdEstadoWorkflow
  • ValueField : Финал

И наоборот для лучшего чтения:

  • Инциденции : Таблица 1
  • EstadosWorkflows : Таблица2
  • EstadoWorkflow : IdTable2
  • IdEstadoWorkflow : Table2.Id
  • Финал : ValueField

Реальные производственные запросы

Эти запросы с планами запросов показывают ту же проблему, но с дополнительными дорогостоящими операциями (такими как огромные существует и объединяются), и проблема ухудшается. Я действительно надеюсь, что я не обманывал вас упрощенными примерами.

Запрос IN со значением 0

 SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil WHERE this_.Instancia = 4 and this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) ORDER BY this_.Fecha desc 

Время выполнения: 266 мс . План выполнения: http://s000.tinyupload.com/index.php?file_id=36115325682943356233

Запрос IN со значением 1

 SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil WHERE this_.Instancia = 4 and this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) ORDER BY this_.Fecha desc 

Время выполнения: 28506мс . План выполнения: http://s000.tinyupload.com/index.php?file_id=72827687005228029776

Запрос NOT IN со значением 0

 SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil WHERE this_.Instancia = 4 and this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) ORDER BY this_.Fecha desc 

Время исполнения: 498 мс . План выполнения: http://s000.tinyupload.com/index.php?file_id=35554889075362686964

Запрос NOT IN со значением 1

 SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil WHERE this_.Instancia = 4 and this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) ORDER BY this_.Fecha desc 

Время исполнения: 386 мс . План выполнения: http://s000.tinyupload.com/index.php?file_id=11500314236594795220

Причина, по которой возникает проблема, заключается в том, что SQL Server не может знать точные значения, которые будут возвращены для in-statement при его оптимизации, поэтому статистика не может быть использована.

Когда у вас есть точные значения в предложении in, их можно сравнить с статистикой, и SQL Server, скорее всего, довольно точную оценку количества строк, которые могут быть, и затем может выбрать лучший план для выполнения.

Я не пробовал это сам, но вы могли бы попытаться создать отфильтрованную статистику для id, отдельно для поля значений 0 и 1, возможно, это улучшит ситуацию.

Обновить

Из последних фотографий видно, что оценки удалены, количество строк оценивается как 1, но на самом деле это 59851 после вложенного цикла:

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

И эта неправильная оценка, по-видимому, вызывает огромное количество сканирований таблиц, поскольку ожидается, что она будет выполнена только один раз:

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

Так как это сканирование таблицы, а не кластеризованное сканирование индекса, похоже, что в этой таблице нет кластерного индекса, и ни один другой индекс, который может быть использован. Не могли бы вы что-нибудь сделать? Не знаю о количестве данных, но может помочь индекс для borrado с включенным или нормальным столбцом idperfil . Это то же самое происходит и в плане значений 0, но поскольку число строк составляет всего 605, сканирование таблицы 605 занимает не так много времени, но когда вы делаете это почти в 100 раз больше, это начинает занимать время.

Если посмотреть на неплановую, то структура поиска будет совершенно иной, скорее всего, потому что приблизительное количество строк ближе к тому, что есть на самом деле, и SQL Server использует этот вид плана:

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

Другим решением могло бы стать создание временной таблицы из Usuarios_Perfiles (с пермилами-ограничением), что могло бы помочь, так как это всего лишь 1179 строк.

Без вывода статистики IO, на 100% не уверен, где тратится время, но выглядит так, как будто это вызвано сканированием таблицы.

Interesting Posts

Что произойдет, если первичный ключ с автоинкрементами выходит за пределы диапазона при вставке новой строки, но существуют неиспользованные значения пробелов?

Установить лимит для строк таблицы в SQL

C # добавление datatable в SQL с автоинкрементным ключом

Источник Excel как соединение преобразования преобразования

В SQL Server я хочу использовать один и тот же запрос для всех таблиц в базе данных

Сохраненная функция кодирования базы данных SQL Server

Использование JSON в SQL Server 2012

Граф человека с возрастом для отображения в диаграмме

Удаление и замена записей в таблице с помощью SQL Server

Рекомендации по использованию нескольких табличных соединений

Изоляция транзакций и чтение из нескольких таблиц в SQL Server Express и SQL Server 2005

SQL Server 2008 Хранимый Proc не возвращает результат динамического запроса

выбор данных рекурсивно из таблицы sql

Как разрешить «неправильный синтаксис рядом с ошибкой« = »в SSRS?

Как создать все записи посещаемости для каждого пользователя в SQL Server

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