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

Откуда:
Сообщений: 10398
есть запрос, обернут хранимой процедурой
из SSMS запрос выполняется 1 сек
с сайта от 30 сек и дольше

хранимка одна и та же
пользователь от имени которого выполняется запрос и в студии и на сайте один и тот же

Почему так происходит?

скриншоты профайлера прилагаю


DECLARE  @query VARCHAR(255)='"привет*"'
DECLARE  @limit INT=1000000
DECLARE  @count INT=0
DECLARE  @taskNumber INT=0



	DECLARE @roles typeGuidList
	DECLARE @emp_id UNIQUEIDENTIFIER = dbo.fnEmployeeId()
	INSERT INTO @roles(id) SELECT id FROM dbo.fnEmployeeRoles(@emp_id)


		SELECT TOP(@limit) 'Tasks'
			, obj_id
			, [name]
			, [desc]
			, [text]
			, [mail]
			, fl_deleted
			, @count + ROW_NUMBER() OVER (ORDER BY fl_created DESC)
		FROM (
			SELECT DISTINCT t.task_id AS obj_id
				, 'Задача № ' + CAST(t.task_number AS VARCHAR(24)) + ISNULL('/' + CAST(t.task_subnum AS VARCHAR(24)), '') + ' от ' +  dbo.fnFormatDate(t.fl_created) + ISNULL(', ' + e.emp_name, '') AS [name]
				, t.task_name AS [desc]
				, t.task_desc AS [text]
				, e.emp_name AS [mail]
				, t.fl_deleted
				, t.fl_created
			FROM Tasks (NOLOCK) AS t
				LEFT JOIN Employee (NOLOCK) AS e ON e.emp_id = t.task_owner
				LEFT JOIN SignStaff (NOLOCK) AS signs ON signs.sstaff_object_name = 'Tasks' AND signs.sstaff_object_id = t.task_id AND signs.fl_deleted IS NULL AND signs.sstaff_role_id IN (SELECT id FROM @roles)
				LEFT JOIN TaskStaff (NOLOCK) AS staff ON staff.tstaff_task_id = t.task_id AND staff.fl_deleted IS NULL AND staff.tstaff_emp_id = @emp_id
				LEFT JOIN TaskTypes (NOLOCK) AS ttype ON ttype.ttype_id = task_type AND ttype.ttype_manager_role_id IN (SELECT id FROM @roles)
				LEFT JOIN Discussion (NOLOCK) AS d ON d.discus_obj_name = 'Tasks' AND d.discus_obj_id = t.task_id
			WHERE (CONTAINS((t.task_name, t.task_desc, t.task_text), @query)
						OR CONTAINS(d.discus_message, @query) 
						OR t.task_number = @taskNumber
						--OR t.task_name LIKE '%'+ @text +'%'
						--OR t.task_desc LIKE '%'+ @text +'%'
						--OR t.task_text LIKE '%'+ @text +'%'
						--OR d.discus_message LIKE '%'+ @text +'%'
					)
					AND t.fl_deleted IS NULL
					AND (t.task_owner = @emp_id 
						OR ttype.ttype_id IS NOT NULL 
						OR signs.sstaff_id IS NOT NULL 
						OR staff.tstaff_id IS NOT NULL 
						OR t.task_number = @taskNumber)
			) AS Tasks


К сообщению приложен файл. Размер - 26Kb
6 июл 16, 14:27    [19375509]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
MSSQLAndDotNet
Member

Откуда:
Сообщений: 10398
блокировки

К сообщению приложен файл. Размер - 42Kb
6 июл 16, 14:30    [19375527]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
у вас процедура явно с параметрами, а вы это не показали.
короче, планов 2, они с разными прослушанными параметрами.
варианты:
1) вы запускаете оба раза как процедуры, но в студии другие настройки соединения типа arithabort
2) вы выдернули запрос, как и сейчас на форуме, превратив параметры в переменные, и это пустили из студии.
не процедурой, а запросом
6 июл 16, 14:46    [19375636]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
MSSQLAndDotNet
Member

Откуда:
Сообщений: 10398
o-o
у вас процедура явно с параметрами, а вы это не показали.
короче, планов 2, они с разными прослушанными параметрами.
варианты:
1) вы запускаете оба раза как процедуры, но в студии другие настройки соединения типа arithabort
2) вы выдернули запрос, как и сейчас на форуме, превратив параметры в переменные, и это пустили из студии.
не процедурой, а запросом


спасибо за ответ

на скриншоте профайлера видно как я выполняю именно хранимки и из студии и с сайта

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

разбираюсь как из профайлера получить план запроса и как его прочитать)
правда мне не понятно, почему планы для одного и того же запроса и пользователя могут быть разные
6 июл 16, 14:54    [19375709]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
Хе-хе )
Guest
MSSQLAndDotNet,
parameter sniffing погугли
6 июл 16, 15:01    [19375770]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

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

посмотрите на ожидания. Во что-то "оборачиваете" вызов с сайта?

автор
parameter sniffing погугли

нет
6 июл 16, 15:04    [19375796]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
MSSQLAndDotNet
на скриншоте профайлера видно как я выполняю именно хранимки и из студии и с сайта

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

потому что при компиляции сверяются параметры сессии и традиционно в студии arithabort on,
а в приложении off.
поэтому план строится заново.
в результате у вас у этой хранимки минимум 2 плана и каждый построен с разными значениями параметров.
в плане их видно, если смотреть свойства самого левого оператора.
планы можно из кэша вынуть
6 июл 16, 15:04    [19375799]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
TaPaK
автор
parameter sniffing погугли

нет

что нет, пускай гуглит.
или готовое читает: Медленно в приложении, быстро в SSMS
6 июл 16, 15:10    [19375837]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
MSSQLAndDotNet
Member

Откуда:
Сообщений: 10398
спасибо за советы
честно говоря я плохо понимаю как сиквел строит и кеширует планы выполнения
на выходных обязательно разберусь

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

автор
создаются локальные переменные в теле процедуры и им присваиваются значения входных параметров.



https://habrahabr.ru/post/109580/

я правильно понял?
6 июл 16, 15:11    [19375852]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o
TaPaK
пропущено...

нет

что нет, пускай гуглит.
или готовое читает: Медленно в приложении, быстро в SSMS

автор
выполняю именно хранимки и из студии и с сайта

перескажите вкратце главу с этого красивого сайта про это
6 июл 16, 15:13    [19375861]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
MSSQLAndDotNet
спасибо за советы
честно говоря я плохо понимаю как сиквел строит и кеширует планы выполнения
на выходных обязательно разберусь

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

автор
создаются локальные переменные в теле процедуры и им присваиваются значения входных параметров.



https://habrahabr.ru/post/109580/

я правильно понял?

нет, неправильно.
у вас 2 разных плана получились,
теперь он из студии все время один план долбит, из приложения другой.
вам надо понять, у вас как часто меняются параметры.
если все время и планы правда разные нужны, и можете себе позволить перекомпилировать каждый раз,
то сразу после запроса option(recompile) вас спасет.
если рекомпиляции нежелательны, надо понять, с какими параметрaми скомпилировался "хороший" план
и прописать хинтом эти параметры
6 июл 16, 15:17    [19375887]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o
теперь он из студии все время один план долбит, из приложения другой.

sql прибивает план к клиентскому приложению?
6 июл 16, 15:20    [19375910]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
кстати, всплыла весёлая тема с auto_close у вас случаем не установлен?
6 июл 16, 15:21    [19375927]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
TaPaK
o-o
теперь он из студии все время один план долбит, из приложения другой.

sql прибивает план к клиентскому приложению?

Сказали же - настройки соединения (set'ы) разные
6 июл 16, 15:27    [19375970]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
zasandator
Member [скрыт] [заблокирован]

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

ЛЬШГ, надо было на ПТ постить )))))
6 июл 16, 15:29    [19375982]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
zasandator
MSSQLAndDotNet,

ЛЬШГ, надо было на ПТ постить )))))
Так он уже...
6 июл 16, 15:37    [19376045]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
buser
TaPaK
пропущено...

sql прибивает план к клиентскому приложению?

Сказали же - настройки соединения (set'ы) разные


настройки соединения = настройки сеанса? какие шансы что "set'ы" случайно и забыто выставлены (ГДЕ кстати :)) ?
6 июл 16, 15:37    [19376054]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
TaPaK
buser
пропущено...

Сказали же - настройки соединения (set'ы) разные


настройки соединения = настройки сеанса? какие шансы что "set'ы" случайно и забыто выставлены (ГДЕ кстати :)) ?

Да, сеты выставляет студия свои, адо свои, вы ничего не выставляете, все выставлено за вас и не как вы хотели
6 июл 16, 15:44    [19376115]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
MSSQLAndDotNet
Member

Откуда:
Сообщений: 10398
o-o
MSSQLAndDotNet
спасибо за советы
честно говоря я плохо понимаю как сиквел строит и кеширует планы выполнения
на выходных обязательно разберусь

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

пропущено...



https://habrahabr.ru/post/109580/

я правильно понял?

нет, неправильно.
у вас 2 разных плана получились,
теперь он из студии все время один план долбит, из приложения другой.
вам надо понять, у вас как часто меняются параметры.
если все время и планы правда разные нужны, и можете себе позволить перекомпилировать каждый раз,
то сразу после запроса option(recompile) вас спасет.
если рекомпиляции нежелательны, надо понять, с какими параметрaми скомпилировался "хороший" план
и прописать хинтом эти параметры


параметры всегда одни и те же
значения параметров всегда разные

в хранимке несколько запросов
результаты их всех собираются в @list
после последнего(из-за которого все тормозило) сделал так:
...
						OR t.task_number = @taskNumber)
			) AS Tasks
		OPTION(RECOMPILE)
		SET @count = @count + @@ROWCOUNT
	END

	SELECT TOP(@limit) obj_name, obj_id, [name], [desc], [text], [mail], fl_deleted FROM @list ORDER BY rownum
END


меньше секунды теперь выполняется запрос
надеюсь это не временно:)

всем спасибо за советы, ушел читать теорию
6 июл 16, 15:46    [19376130]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

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

ну так пусть поставит SET ARITHABORT ON и всё прояснится :)
6 июл 16, 15:47    [19376133]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
[quot MSSQLAndDotNet]
o-o
пропущено...
результаты их всех собираются в @list
после последнего(из-за которого все тормозило) сделал так:
...
						OR t.task_number = @taskNumber)
			) AS Tasks
		OPTION(RECOMPILE)
		SET @count = @count + @@ROWCOUNT
	END

	SELECT TOP(@limit) obj_name, obj_id, [name], [desc], [text], [mail], fl_deleted FROM @list ORDER BY rownum
END


меньше секунды теперь выполняется запрос
надеюсь это не временно:)

всем спасибо за советы, ушел читать теорию


full duplex
6 июл 16, 15:51    [19376175]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
TaPaK
o-o,
ну так пусть поставит SET ARITHABORT ON и всё прояснится :)

никак и не поставит.
скорее, в студии OFF выставит, вот это можно.
---
RECOMPILE это ок, если их немного, рекомпиляций.
может, как народ на сайт повалит, ждать начнете уже сами репомпиляции
6 июл 16, 15:59    [19376238]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
LogrusAS
Member

Откуда: Киев
Сообщений: 197
Почему это не поставит? Конечно я не вижу на чем сайт написан. Но, например, для C# что нибудь такое можно:

        public static void OpenWithOptions(this SqlConnection connection)
        {
            connection.Open();
            var command = new SqlCommand(
@"SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON 
SET ANSI_NULL_DFLT_ON ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL ON", 
                               connection) { CommandTimeout = 0 };
            command.ExecuteNonQuery();
        }


И вызывать OpenWithOptions вместо стандартного Open.
Я в свое время тоже наткнулся на такую проблему. Причем там не процедура была, а не очень сложный запрос.
Уже лет 5 таскаю этот код из проекта в проект.
Опции, кстати, брал из студии. Ну и статью какую то тогда нашел.
6 июл 16, 16:23    [19376454]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
o-o
Guest
ну теперь-то бесполезно ставить,
план того запроса больше не сохраняется
---
да и просто выставить опции это же все равно не решение,
это чисто для проверки догадки подходит, а уже и так проверили рекомпайлом.
причина же не в опциях, а в первом вызове процедуры.
если при этом были "хорошие" параметры, то и план сохранился хороший,
а у него в этот раз именно с сайта вызвали с нетипичными параметрами.
это ж все равно до первого опустошения proccache, а потом могут снова
вызвать с плохими параметрами при любых выставленных опциях
и закэшируется это самое в первый раз прослушанное
6 июл 16, 16:40    [19376547]     Ответить | Цитировать Сообщить модератору
 Re: Почему из SSMS запрос выполняется быстрее?  [new]
TaPaK
Member

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

у него явно не в параметрах дело, а в том что весь запрос он сразу отдаёт клиенту и скорее всего стоит halfduplex или сеть или и то и то, можно было сразу это увидеть посмотрев ожидания...
6 июл 16, 16:44    [19376574]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить