Member 13158185 Ответов: 1

Хранимая процедура занимает 1 час для выполнения и потери соединения с базой данных, так как количество записей огромно, поэтому любезно помогите мне

хранимая процедура занимает 1 час для выполнения и потери соединения с базой данных, так как количество записей огромно
я не в состоянии выяснить, какой еще способ заставить его работать быстро.

вот моя хранимая процедура.

Что я уже пробовал:

Используйте [VAccountsDUBAI]
ГО
/ * * * * * * Объект: StoredProcedure [dbo].[PRC_ScheduleMonthWise] Дата Сценария: 29.04.2017 10:56:40 ******/
УСТАНОВИТЕ ANSI_NULLS ON
ГО
УСТАНОВИТЕ QUOTED_IDENTIFIER НА
ГО

Изменить процедуру [dbo].[PRC_ScheduleMonthWise](
@Months NVARCHAR(30),
@CompanyID BIGINT ,
@YearID INT
)
АС
НАЧАТЬ
Объявить @ScheduleNo nvarchar(10)
Объявите @Curr nvarchar(10) = @YearID
, @cols_C как nvarchar(Макс), @cols_C1 как nvarchar(Макс), @Query и как nvarchar(Макс),@запроса query1 как nvarchar(Макс),@query2 как nvarchar(Макс),@query3 тип nvarchar(Макс),@query4 тип nvarchar(Макс),@FinalQuery тип nvarchar(Макс)
, @cols как тип nvarchar(Макс),@Curryear как nvarchar(Макс),@BrcMapID тип nvarchar(3),@BranchName тип nvarchar(50)
если (@BrcMapID равно null )
начать
select @BrcMapID=STUFF ((SELECT', ' + CAST(BranchMappingID as varchar(10))
из vw_BranchYearMapping, где CompanyID=@CompanyID и FYearId=@YearID для XML-пути ("), введите
). value ('.', ' NVARCHAR (MAX)')
,1,1,'');

конец

Выберите @BrcMapID = BranchmappingID,@BranchName = BranchName от vw_BranchYearMapping где CompanyID = @CompanyID и FYearId = @Карр

set @Curryear = '[Total '+ Isnull(@BranchName, 'All') +']'



выберите @cols_C1 = гадость((выбора ',' + функции quotename(функция datename( месяц , функция dateadd( месяца , удостоверение личности , -1 )))
от ДБО.FN_getID (@Months) для XML PATH (") введите
). value ('.', ' NVARCHAR (MAX)')
,1,1,'');

выберите @cols_C = гадость((выберите функции isnull(' + функции quotename(функция datename( месяц , функция dateadd( месяц , код , -1 ))) +',0)+'
от ДБО.FN_getID (@Months) для XML PATH (") введите
). value ('.', ' NVARCHAR (MAX)')
,1,1,'');

печать "только столбцы" + @cols_C1
набор @cols_C= случай, когда @cols_C не null тогда ',('+подстрока(@cols_C,0,лен(@cols_C))+') AS '+@Curryear ELSE " END
выберите @cols_C1 + @cols_C
Объявить таблицу @ScheduleTable
(
[SchID] int IDENTITY(1,1) первичный ключ КЛАСТЕРИЗОВАН,
Щно Тип Nvarchar(10)
)
Вставить в @ScheduleTable (SCHNo) (выберите ScheduleNo из Mst_ProfitNLossMapping, где ISNULL(ScheduleNo,") < & gt; " И IsDeleted = 0 и CompanyID = @CompanyID и FyearID = @YearID)

выберите * из @ScheduleTable

Объявить @Counter INT = 1
WHILE @Counter <= (SELECT COUNT (*) FROM @ScheduleTable) BEGIN
Выберите @ScheduleNo = SCHNo из @ScheduleTable, где SchID = @ Counter
Набор запроса @ = 'объявить @делится инт='+@Карр+',@CompanyID инт =' + конвертировать(тип nvarchar(5)@CompanyID) + ',@СЧ тип varchar(5)= ' + @ScheduleNo + ';
Выберите GroupID, GroupName,LedgerName,'+ @cols_C1 + @cols_C +'
ОТ(
Выберите группы"," как Имя_группы,LedgerName,функция datename(месяц,функция dateadd( месяц,[название месяца],-1)) как VoucherMonth,(в случае, когда сумма(ChargeBaseAmount) &ГТ;= 0, то АБС(сумма(ChargeBaseAmount)) ELSE 0 END) - (случай, когда SUM (ChargeBaseAmount) < 0 затем ABS(SUM(ChargeBaseAmount)) Иначе 0 конец) как сумма из vw_report где BranchMappingID = '+ @BrcMapID + 'и scheduleno =' + @ScheduleNo + ' и YearID = @и делится ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) и CompanyID =@CompanyID группы по LedgerID,LedgerName,группы,группы,[название месяца]
СОЮЗ ВСЕХ '
Набор @запроса query1= 'выберите группы,(выберите имя группы из Mst_Group где группы = Mst_ProfitNLossMapping.GroupID) как GroupName,"" как LedgerName, NULL как BranchYear,
(Случай, когда (выберите функции isnull(сумма(ChargeBaseAmount),0) от vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 1 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) и GT; (выберите функции isnull(сумма(ChargeBaseAmount),0) из vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 0 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) ЗАТЕМ
АБС((выберите функции isnull(сумма(ChargeBaseAmount),0) от vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 1 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) + (выберите функции isnull(сумма(ChargeBaseAmount),0) из vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 0 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null))) ELSE 0 END) -'
Набор @query2 = '(случай, когда (выберите функции isnull(сумма(ChargeBaseAmount),0) от vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 0 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) и GT; (выберите функции isnull(сумма(ChargeBaseAmount),0) из vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 1 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) ЗАТЕМ
АБС((выберите функции isnull(сумма(ChargeBaseAmount),0) от vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 1 и YearID =@делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null) ) + (выберите функции isnull(сумма(ChargeBaseAmount),0) из vw_report где BranchMappingID = '+ @BrcMapID + ' и ScheduleNo = @SCH и DRCR = 0 и YearID = @делится и CompanyID = @CompanyID и ([название месяца] В ('+@месяцев+') или [название месяца] имеет значение null))) ELSE 0 END) КАК ИТОГ
Из Mst_ProfitNLossMapping, где ScheduleNo = @sch и CompanyID = @CompanyID '
SET @query3 = ' UNION ALL
выберите GroupID, (SELECT '' '' + GroupName FROM Mst_Group WHERE GroupID = vw_Report.GroupID) AS GroupName, '' '' AS LedgerName, DateName (месяц, DateAdd (месяц, [Название месяца], - 1)) AS VoucherMonth, (CASE WHEN SUM (ChargeBaseAmount)> = 0 THEN ABS (SUM (ChargeBaseAmount)) ELSE 0 END) - (CASE WHEN SUM (ChargeBaseAmount) <0 THEN ABS (SUM (ChargeBaseAmount)) ELSE 0 END) КАК ИТОГО из vw_Report, где BranchMappingID = '+ @BrcMapID +' AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ScheduleNo = @sch AND ([Название месяца] в ('+ @ Months +') ИЛИ [Название месяца] IS NULL) группа по GroupID, ScheduleNo, [Название месяца]
) КАК Б
СТЕРЖЕНЬ
(
СУММА)
для VoucherMonth in ('+@cols_c1+')
)
п порядок группы,группы по убыванию '
выберите @query, @query1 ,@query2,@query3

SET @FinalQuery = @query + @query1+ @query2 + @query3;
Выполнить (@FinalQuery )
SET @Counter += 1
КОНЕЦ
конец

CHill60

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

1 Ответов

Рейтинг:
1

CHill60

Взгляните на запрос 

SET @query1= 'SELECT groupID,(SELECT GroupName FROM Mst_Group WHERE GroupID = Mst_ProfitNLossMapping.GroupID) AS GroupName,'''' AS LedgerName,NULL AS BranchYear,
    (CASE WHEN (SELECT ISNULL(SUM(ChargeBaseAmount),0) from ...
Он полон подзапросов. Вам нужно заменить эти подзапросы соединением.
Например 
(SELECT GroupName FROM Mst_Group WHERE GroupID = Mst_ProfitNLossMapping.GroupID) AS GroupName,
'' AS LedgerName,NULL AS BranchYear, ...
FROM Mst_ProfitNLossMapping M
...
может быть заменен на 
G.GroupName, 
'' AS LedgerName,NULL AS BranchYear, ...
FROM Mst_ProfitNLossMapping M
INNER JOIN Mst_Group G ON GroupID = M.GroupID)
...
На самом деле вы запрашиваете vw_report 8 раз за цикл! Это ударит по вашему выступлению. Вместо того чтобы использовать все эти подзапросы в предложениях CASE, запросите информацию только один раз и присоединитесь к результатам, например 
;WITH CTE AS
(
	SELECT * from 
	(SELECT DRCR, SUM(ChargeBaseAmount) AS S from @T 
	where BranchMappingID = 22 AND ScheduleNo = @sch AND YearID = @CurrentYear AND CompanyID = @CompanyID AND (ISNULL([Month Name]. 'null') in (@Months)
	GROUP BY DRCR) qry 
	PIVOT (	MAX(S) FOR DRCR IN ([0],[1])) P
)
SELECT G.GroupName, '' AS LedgerName,NULL AS BranchYear, 
	CASE WHEN CTE.[1] > CTE.[0] THEN ABS(CTE.[1]) + CTE.[0] ELSE 0 END - ... etc
FROM Mst_ProfitNLossMapping M
INNER JOIN Mst_Group G ON GroupID = M.GroupID)
LEFT OUTER JOIN CTE ON 1=1
Это должно упростить этот расчет и облегчить его чтение - я думаю, что на самом деле там есть ошибка.

Как я уже сказал в своем комментарии, вероятно, есть возможность присоединиться к таблице @ScheduleTable и избавиться от этого цикла WHILE. Обычно я не публикую ссылки на свои собственные материалы, но в моей статье есть некоторая информация о том, как избежать циклов, и я не собираюсь повторять все это здесь - Циклы обработки в SQL Server[^]