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

Откуда:
Сообщений: 63
Есть хранимая процедура
USE [t_artemtest]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [sm_report].[p_COUNTER_HouseController]
@supplierID					int,
@serviceID					int = null,
@controllerID				int,	--Контролер
@numberMonth			int		--Дата плана обхода
AS
DECLARE @dateCurrent			int

EXEC p_GET_currentDate_int @dateCurrent OUT	

; with fc as
(
select
	distinct f.accountID, f.supplierID
from
	dbo.flatsServices as f
where
		f.supplierID = @supplierID and f.calculated = 1 and f.dataClose > @dateCurrent
) 
,cmv as
(select counterID, max(cntrVlID) cntrVlID from countersvalues cv group by counterID)
 

SELECT
	s.nameSupplier,
	se.nameService,
    gdpo.nameGD as namePostOffice,
	substring(acc.accountN,1,3)+'-'+substring(acc.accountN,4,3)+'-'+substring(acc.accountN,7,3) as accountN,
	va.nameStreet,
	va.houseN,
	va.corpus,
	va.flatN,
	va.flatNint,
	ct.nameCounter,
	c.numberCounter,
	c.firsterValue,
	isnull(cv.valueEnd,'''') as valueEnd,	
	January, February, March, April, May, June, July, August, September, October, November, December
FROM 
	v_counterAccount as c
	JOIN flatsServices as fs ON c.objectID = fs.accountID and fs.serviceID = c.serviceID and fs.calculated = 1 and fs.dataClose > dbo.f_Get_currentDate_int()
	JOIN sm_address.account as acc ON c.objectID = acc.accountID
	JOIN v_address as va ON acc.flatID = va.flatID
	JOIN fc on c.objectID = fc.accountID --Лубов Д. 27.09.2017
	JOIN countersvalues cv ON cv.counterID=c.counterID 
	JOIN cmv ON cmv.cntrVlID=cv.cntrVlID 
	JOIN gd_guide as gdpo ON gdpo.gdID=va.postOfficeID
	JOIN countersTypes as ct ON c.cntrTypeID=ct.cntrTypeID
	JOIN suppliers as s ON fc.supplierID = s.supplierID
	JOIN services as se ON fs.serviceID = se.serviceID
	JOIN houseController as hc ON hc.houseID=va.houseID and fs.serviceID = hc.serviceID and hc.controllerID= isnull(@controllerID, hc.controllerID)
	JOIN deluxes as d ON fs.deluxeID = d.deluxeID and d.objectCalcID <> 131
WHERE  
	fs.serviceID = ISNULL(@serviceID, fs.serviceID)
    and c.dataclose>@dateCurrent

проблема в следующем, при запуске в студии mssql
USE [t_artemtest]
GO

DECLARE	@return_value int

EXEC	@return_value = [sm_report].[p_COUNTER_HouseController]
		@supplierID = 253,
		@serviceID = 3,
		@controllerID = NULL,
		@numberMonth = NULL

SELECT	'Return Value' = @return_value

GO


выполнение ХП растягивается на произвольное время(до 10 минут), какое серверу вздумается, в то же время полностью скопированный код из этой процедуры в отдельное окно mssql
[src]DECLARE
@supplierID					int = 253,
@serviceID					int = 3,
@controllerID				int,	--Контролер
@numberMonth			int,		--Дата плана обхода
@dateCurrent			int

EXEC p_GET_currentDate_int @dateCurrent OUT	

; with fc as
(
select
	distinct f.accountID, f.supplierID
from
	dbo.flatsServices as f
where
		f.supplierID = @supplierID and f.calculated = 1 and f.dataClose > @dateCurrent
) 
,cmv as
(select counterID, max(cntrVlID) cntrVlID from countersvalues cv group by counterID)
 

SELECT
	s.nameSupplier,
	se.nameService,
    gdpo.nameGD as namePostOffice,
	substring(acc.accountN,1,3)+'-'+substring(acc.accountN,4,3)+'-'+substring(acc.accountN,7,3) as accountN,
	va.nameStreet,
	va.houseN,
	va.corpus,
	va.flatN,
	va.flatNint,
	ct.nameCounter,
	c.numberCounter,
	c.firsterValue,
	isnull(cv.valueEnd,'''') as valueEnd,	
	January, February, March, April, May, June, July, August, September, October, November, December
FROM 
	v_counterAccount as c
	JOIN flatsServices as fs ON c.objectID = fs.accountID and fs.serviceID = c.serviceID and fs.calculated = 1 and fs.dataClose > dbo.f_Get_currentDate_int()
	JOIN sm_address.account as acc ON c.objectID = acc.accountID
	JOIN v_address as va ON acc.flatID = va.flatID
	JOIN fc on c.objectID = fc.accountID --Лубов Д. 27.09.2017
	JOIN countersvalues cv ON cv.counterID=c.counterID 
	JOIN cmv ON cmv.cntrVlID=cv.cntrVlID 
	JOIN gd_guide as gdpo ON gdpo.gdID=va.postOfficeID
	JOIN countersTypes as ct ON c.cntrTypeID=ct.cntrTypeID
	JOIN suppliers as s ON fc.supplierID = s.supplierID
	JOIN services as se ON fs.serviceID = se.serviceID
	JOIN houseController as hc ON hc.houseID=va.houseID and fs.serviceID = hc.serviceID and hc.controllerID= isnull(@controllerID, hc.controllerID)
	JOIN deluxes as d ON fs.deluxeID = d.deluxeID and d.objectCalcID <> 131
WHERE  
	fs.serviceID = ISNULL(@serviceID, fs.serviceID)
    and c.dataclose>@dateCurrent



выполняется за нормальные 2-4 секунды. Проштудировал уже все статьи с перекомпиляцией планов выполнения, очищением кэша ХП и т.д.
Подскажите пожалуйста кто сталкивался с такой проблемой, спасибо.
5 июн 19, 08:46    [21902088]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
Tohins
Member

Откуда:
Сообщений: 63
Версия сервера
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
5 июн 19, 08:51    [21902093]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
TaPaK
Member

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

http://www.sommarskog.se/query-plan-mysteries.html
5 июн 19, 09:02    [21902104]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
Tohins
выполнение ХП растягивается на произвольное время(до 10 минут), какое серверу вздумается, в то же время полностью скопированный код из этой процедуры в отдельное окно mssql
[SRC sql][/SRC]
Во первых, посмотрите всё таки, в чём разница, и получите актуальные планы ваыполнения для проблемного запроса

Tohins
Проштудировал уже все статьи с перекомпиляцией планов выполнения, очищением кэша ХП и т.д.
А параметр стуффинг попробовали? Это же первое, что приходит в голову, особенно для @dateCurrent
5 июн 19, 09:04    [21902107]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
TaPaK
Member

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

@стуффинг@
сниффинг, по ссылке все возможные варианты :)
5 июн 19, 09:08    [21902114]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
TaPaK
alexeyvg,

@стуффинг@
сниффинг
Ой

Да, по ссылке хорошая статья, но большая, не каждый сумеет прочитать :-)
5 июн 19, 09:10    [21902119]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
alexeyvg
TaPaK
alexeyvg,

@стуффинг@
сниффинг
Ой

Да, по ссылке хорошая статья, но большая, не каждый сумеет прочитать :-)

тогда OPTION(RECOMPILE) и расходимся :)
5 июн 19, 09:12    [21902121]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
Tohins
Member

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

OPTION(RECOMPILE) не помогает, уже пробовал

http://www.sommarskog.se/query-plan-mysteries.html
- эту статью я уже читал, проблема то не в приложении, а двух окнах студии по сути запускающими один код, один открыто, второй через скомпилированную ХП, сам понимаю что дело скорей всего в плане выполнения, но не могу понять чем планы могут отличаться при одном и том же коде
5 июн 19, 09:21    [21902128]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
Tohins
но не могу понять чем планы могут отличаться при одном и том же коде
Так посмотрите, чем отличаются, чего гадать.
"Почему" - в статье как раз и написано.
Кратко - из за разных оценок оптимизатора, или из за разных установок SET
5 июн 19, 09:24    [21902131]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
TaPaK
Member

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

автор
OPTION(RECOMPILE)

я ж надеюсь в процедуру вставили? и не WITH RECOMPILE?
показывайте медленный/быстрый план, и не картинкой
5 июн 19, 09:24    [21902132]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
Konst_One
Member

Откуда:
Сообщений: 11517
https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/
5 июн 19, 11:34    [21902316]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
aleks222
Member

Откуда:
Сообщений: 923
Konst_One
https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/

Опять лечим припарками геморрой?
v_counterAccount - это, нибось, семиэтажная вьюха? И еще парочка таких же?
5 июн 19, 16:01    [21902697]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
Konst_One
Member

Откуда:
Сообщений: 11517
это к ТС вопрос
5 июн 19, 16:03    [21902700]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с кодом Хранимой процедуры, и временем выполнения  [new]
Tohins
Member

Откуда:
Сообщений: 63
Alex22, нет, вьюха нормальная.

Всем спасибо, пока готовил планы на обсуждение еще раз внимательно изучил их, нашел проблемный индекс, проблема ушла.
7 июн 19, 01:15    [21904127]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить