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

Откуда:
Сообщений: 183
Два идентичных запроса. Первый оборачивается в sp_executesql.
1-й запрос отрабатывает за аж 3 секунды, второй запрос моментально. В независимости от подставляемых параметров.

Все индексы присутствуют.

Вопрос! Отчего тормозит запрос обёрнутый в sp_executesql??

exec sp_executesql N'SELECT
  [Kladr].[KladrId] AS [KladrId], 
  ([Kladr].[Name] + '' ('' + [KladrType].[code] + '')'') AS [Expression]
FROM
  [Kladr] AS [Kladr]
  JOIN [KladrType] AS [KladrType] ON [KladrType].[KladrTypeId] = [Kladr].[KladrTypeId]
  LEFT JOIN [Kladr] AS [KladrRegion] ON [Kladr].[MainKladr1Id] = [KladrRegion].[KladrId]
  LEFT JOIN [Kladr] AS [KladrArea] ON [Kladr].[MainKladr2Id] = [KladrArea].[KladrId]
  LEFT JOIN [Kladr] AS [KladrPlace] ON [Kladr].[MainKladr3Id] = [KladrPlace].[KladrId]
WHERE
      (
      ([KladrRegion].[Name] = @Region)
   OR (([Kladr].[MainKladr1Id] IS NULL) AND (@Region IS NULL))
      )
  AND (
      ([KladrArea].[Name] = @Area)
   OR (([Kladr].[MainKladr2Id] IS NULL) AND (@Area IS NULL))
      )
  AND (
      ([KladrPlace].[Name] = @Place)
   OR (([Kladr].[MainKladr3Id] is NULL) AND (@Place IS NULL))
      )
ORDER BY
  [Kladr].[Name]',N'@Region nvarchar(6),@Area nvarchar(4000),@Place nvarchar(6)',@Region=N'Москва',@Area=NULL,@Place=N'Москва'


SELECT 
  [Kladr].[KladrId] AS [KladrId], 
  ([Kladr].[Name] + ' (' + [KladrType].[code] + ')') AS [Expression]
FROM
  [Kladr] AS [Kladr]
  JOIN [KladrType] AS [KladrType] ON [KladrType].[KladrTypeId] = [Kladr].[KladrTypeId]
  LEFT JOIN [Kladr] AS [KladrRegion] ON [Kladr].[MainKladr1Id] = [KladrRegion].[KladrId]
  LEFT JOIN [Kladr] AS [KladrArea] ON [Kladr].[MainKladr2Id] = [KladrArea].[KladrId]
  LEFT JOIN [Kladr] AS [KladrPlace] ON [Kladr].[MainKladr3Id] = [KladrPlace].[KladrId]
WHERE
      (
      ([KladrRegion].[Name] = 'Москва')
   OR (([Kladr].[MainKladr1Id] IS NULL) AND ('Москва' IS NULL))
      )
  AND (
      ([KladrArea].[Name] = NULL)
   OR (([Kladr].[MainKladr2Id] IS NULL) AND (NULL IS NULL))
      )
  AND (
      ([KladrPlace].[Name] = 'Москва')
   OR (([Kladr].[MainKladr3Id] is NULL) AND ('Москва' IS NULL))
      )
ORDER BY
  [Kladr].[Name]
  
8 сен 09, 18:39    [7633537]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3650
Хм... А планы ?
8 сен 09, 18:45    [7633571]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
если избавиться от параметров, т.е. просто 2й запрос обернуть в sp_excecutesql, то всё отрабатывает также моментально...
8 сен 09, 18:45    [7633572]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
iljy
Member

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

планы смотрите. Во втором случае план строится с учетом явных значений параметров, в первом без. И тут может сказываться плохая статистика, фрагментация, фаза луны и прочее. Можно использовать явные подсказки, указания индекса или OPTIMIZE FOR.
8 сен 09, 18:46    [7633583]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
план обёрнутого запроса не рисуется(

К сообщению приложен файл. Размер - 0Kb
8 сен 09, 18:49    [7633593]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
добавление в 1й запрос
OPTION (OPTIMIZE FOR (@Region=''Москва'',@Area=NULL,@Place=''Москва'')
эффекта не даёт
8 сен 09, 19:08    [7633681]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
если у 1-го запроса параметр @Place=N'Москва' внести вовнутрь, обычным текстом, то он тоже отрабатывает моментом...
если передаётся как параметр, то 3 секунды(

Что можно сделать?
8 сен 09, 19:36    [7633781]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
iljy
Member

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

(([Kladr].[MainKladr1Id] IS NULL) AND ('Москва' IS NULL))

вот такие условия оптимизатор скорее всего убирает - они всегда FALSE. И соответственно не требуется никаких проверок по колонкам MainKladrId, и можно использовать узкие индексы. Вероятнее всего причина именно в этом. Что делать - таки выцепить план запроса с параметрами, например сделав его процедурой. Только в теле надо сделать объявления переменных типа
declare @_Place nvarchar(6)
select @_Place = @Place
для всех параметров, и в запросе использовать локальные переменные. думаю план вас удивит.
8 сен 09, 19:43    [7633803]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
План запроса с параметрами
1 часть

К сообщению приложен файл. Размер - 0Kb
8 сен 09, 19:56    [7633859]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
2 часть

К сообщению приложен файл. Размер - 0Kb
8 сен 09, 19:57    [7633862]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
Как бы оптимизаторами заставить запрос выполняться по плану второго запроса?
8 сен 09, 19:58    [7633865]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
iljy
Member

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

планы надо выкладывать либо в виде текста, либо в формате .sqlplan. Картинку увы анализировать не получиться.
8 сен 09, 20:02    [7633885]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
Glory
Member

Откуда:
Сообщений: 104760
mrc
если избавиться от параметров, т.е. просто 2й запрос обернуть в sp_excecutesql, то всё отрабатывает также моментально...

Вообще то константа 'Москва' имеет тип данных вовсе не nvarchar(6)
8 сен 09, 20:05    [7633898]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
Glory
mrc
если избавиться от параметров, т.е. просто 2й запрос обернуть в sp_excecutesql, то всё отрабатывает также моментально...

Вообще то константа 'Москва' имеет тип данных вовсе не nvarchar(6)


ADO.NET автоматом проставляет именно этот тип с именно этой размерности...
да и в тысячах других подобных запросах к БД с типом проблем нет

А проблема есть именно с этим запросом (
голово сломал... оптимизаторы джоина перепробовал - нуль эффекту...

кто-нить поймет по плану как разрулить тонкие места запроса?
8 сен 09, 21:33    [7634142]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
Glory
Member

Откуда:
Сообщений: 104760
mrc
Glory
mrc
если избавиться от параметров, т.е. просто 2й запрос обернуть в sp_excecutesql, то всё отрабатывает также моментально...

Вообще то константа 'Москва' имеет тип данных вовсе не nvarchar(6)


ADO.NET автоматом проставляет именно этот тип с именно этой размерности...
да и в тысячах других подобных запросах к БД с типом проблем нет

А проблема есть именно с этим запросом (
голово сломал... оптимизаторы джоина перепробовал - нуль эффекту...

кто-нить поймет по плану как разрулить тонкие места запроса?

Причем тут ADO.NET, если вы сравниваете
во-первых, запрос с константами с запросом с переменными
во-вторых, запросы в которых константы и переменные имеют разные типы
8 сен 09, 21:35    [7634152]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
Glory
mrc
Glory
mrc
если избавиться от параметров, т.е. просто 2й запрос обернуть в sp_excecutesql, то всё отрабатывает также моментально...

Вообще то константа 'Москва' имеет тип данных вовсе не nvarchar(6)


ADO.NET автоматом проставляет именно этот тип с именно этой размерности...
да и в тысячах других подобных запросах к БД с типом проблем нет

А проблема есть именно с этим запросом (
голово сломал... оптимизаторы джоина перепробовал - нуль эффекту...

кто-нить поймет по плану как разрулить тонкие места запроса?

Причем тут ADO.NET, если вы сравниваете
во-первых, запрос с константами с запросом с переменными
во-вторых, запросы в которых константы и переменные имеют разные типы


Завтра на работе приведу константы и переменные в запросах к одному типу... Но сомневаюсь, что это повлияет на план запроса...
8 сен 09, 21:46    [7634172]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
Приведение во 2м запросе Москвы к nvarchar к тормозам не привело...

Может как-то 1-й запрос переделать можно, чтобы план поменялся?
9 сен 09, 12:19    [7636274]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
iljy
Member

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

если вы наконец выложите планы в пригодном для анализа виде - возможно все
9 сен 09, 14:29    [7637216]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
Glory
Member

Откуда:
Сообщений: 104760
mrc
Приведение во 2м запросе Москвы к nvarchar к тормозам не привело...


Константы надо к переменным привести
9 сен 09, 14:30    [7637221]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
iljy
mrc,

если вы наконец выложите планы в пригодном для анализа виде - возможно все


так я же выложил! выше планы обоих запросов!
9 сен 09, 14:32    [7637230]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
Glory
Member

Откуда:
Сообщений: 104760
mrc
iljy
mrc,

если вы наконец выложите планы в пригодном для анализа виде - возможно все


так я же выложил! выше планы обоих запросов!

И как прикажите по этим картинкам посмотреть параметры отдельныз шагов плана ?
9 сен 09, 14:34    [7637247]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
план тормознутого запроса в XML

Модератор: Используйте опцию Приложить файл


Сообщение было отредактировано: 9 сен 09, 15:11
9 сен 09, 15:09    [7637511]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
вернее это сразу план 2х запросов
тормоза и быстрого
первый с параметрами, второй без
9 сен 09, 15:10    [7637514]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
mrc
Member

Откуда:
Сообщений: 183
ну и в таком виде, если нужно...

К сообщению приложен файл (1.sqlplan - 91Kb) cкачать
9 сен 09, 15:11    [7637527]     Ответить | Цитировать Сообщить модератору
 Re: Отчего тормозит запрос обёрнутый в sp_executesql?  [new]
iljy
Member

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

посмтрите на план внимательно - у вас в быстром варианте нет проверок условий (([Kladr].[MainKladr1Id] IS NULL) AND ('Москва' IS NULL)) и (([Kladr].[MainKladr3Id] is NULL) AND ('Москва' IS NULL)). Как я и сказал - оптимизатор их убрал. Соответственно это позволяет ему избежать лишних выборок данных, используя Index Seek вместо Clustered Index Scan.
Как бороться - для на начала попробуйте включить нужные колонки в индексы, используемые в быстром плане.
9 сен 09, 15:39    [7637727]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить