|
Проблема с кодом Хранимой процедуры, и временем выполнения
#39822608
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Есть хранимая процедура
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
[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 секунды. Проштудировал уже все статьи с перекомпиляцией планов выполнения, очищением кэша ХП и т.д.
Подскажите пожалуйста кто сталкивался с такой проблемой, спасибо. |
|
|
