stored procedure is taking 1 hr to execute and loss the connection with the database as number of record is huge
im not able to find out what other way to make it execute fast.
here is my stored procedure.
What I have tried:
USE [VAccountsDUBAI]
GO
/****** Object: StoredProcedure [dbo].[PRC_ScheduleMonthWise] Script Date: 04/29/2017 10:56:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[PRC_ScheduleMonthWise](
@Months NVARCHAR(30),
@CompanyID BIGINT ,
@YearID INT
)
AS
BEGIN
DECLARE @ScheduleNo nvarchar(10)
DECLARE @Curr nvarchar(10) = @YearID
, @cols_C AS NVARCHAR(MAX), @cols_C1 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX),@query1 AS NVARCHAR(MAX),@query2 AS NVARCHAR(MAX),@query3 nvarchar(MAX),@query4 NVARCHAR(MAX),@FinalQuery NVARCHAR(MAX)
, @cols AS NVARCHAR(MAX),@Curryear AS NVARCHAR(MAX),@BrcMapID nvarchar(3),@BranchName Nvarchar(50)
if (@BrcMapID is null )
begin
select @BrcMapID=STUFF((SELECT ',' + CAST(BranchMappingID as varchar(10))
from vw_BranchYearMapping where CompanyID=@CompanyID and FYearId=@YearID FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
end
SELECT @BrcMapID = BranchmappingID,@BranchName = BranchName FROM vw_BranchYearMapping where CompanyID = @CompanyID AND FYearId = @Curr
set @Curryear = '[Total '+ Isnull(@BranchName,'All') +']'
select @cols_C1 = STUFF((SELECT ',' + QUOTENAME(DateName( month , DateAdd( month , id , -1 )))
from dbo.FN_getID(@Months) FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
select @cols_C = STUFF((SELECT ' ISNULL(' + QUOTENAME(DateName( month , DateAdd( month , id , -1 ))) +',0)+'
from dbo.FN_getID(@Months) FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
print 'only columns ' + @cols_C1
set @cols_C= CASE WHEN @cols_C IS NOT NULL THEN ',('+SUBSTRING(@cols_C,0,LEN(@cols_C))+') AS '+@Curryear ELSE '' END
select @cols_C1 + @cols_C
DECLARE @ScheduleTable TABLE
(
[SchID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SCHNo Nvarchar(10)
)
INSERT INTO @ScheduleTable(SCHNo) (SELECT ScheduleNo FROM Mst_ProfitNLossMapping WHERE ISNULL(ScheduleNo ,'') <> '' AND IsDeleted = 0 AND CompanyID = @CompanyID AND FyearID = @YearID)
select * from @ScheduleTable
DECLARE @Counter INT = 1
WHILE @Counter <= (SELECT COUNT(*) FROM @ScheduleTable) BEGIN
SELECT @ScheduleNo = SCHNo FROM @ScheduleTable WHERE SchID = @Counter
SET @query = 'DECLARE @CurrentYear int='+@Curr+',@CompanyID int =' + CONVERT(NVARCHAR(5),@CompanyID) + ',@sch varchar(5)= ' + @ScheduleNo + ';
SELECT GroupID,GroupName,LedgerName,'+ @cols_C1 + @cols_C +'
FROM(
SELECT GroupID,'''' AS GroupName,LedgerName,DateName(month,DateAdd( month,[Month Name],-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) AS TOTAL from vw_report where BranchMappingID = ' + @BrcMapID + ' AND scheduleno = ' + @ScheduleNo + ' AND YearID = @CurrentYear AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) AND CompanyID =@CompanyID GROUP BY LedgerID,LedgerName,GroupID,GroupName,[Month Name]
UNION ALL '
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 vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 1 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) > (SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 0 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) THEN
ABS((SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 1 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) + (SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 0 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL))) ELSE 0 END) -'
SET @query2 = ' (CASE WHEN (SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 0 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) > (SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 1 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) THEN
ABS((SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 1 AND YearID =@CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) ) + (SELECT ISNULL(SUM(ChargeBaseAmount),0) from vw_report where BranchMappingID = ' + @BrcMapID + ' AND ScheduleNo = @sch AND DRCR = 0 AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL))) ELSE 0 END) AS TOTAL
FROM Mst_ProfitNLossMapping where ScheduleNo = @sch AND CompanyID = @CompanyID '
SET @query3 = ' UNION ALL
select GroupID,(SELECT '''' + GroupName FROM Mst_Group WHERE GroupID = vw_Report.GroupID) AS GroupName,'''' AS LedgerName,DateName(month,DateAdd( month,[Month Name],-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) AS TOTAL from vw_Report where BranchMappingID = ' + @BrcMapID + ' AND YearID = @CurrentYear AND CompanyID = @CompanyID AND ScheduleNo = @sch AND ([Month Name] in ('+@Months+') OR [Month Name] IS NULL) group by GroupID,ScheduleNo,[Month Name]
) AS B
PIVOT
(
SUM(TOTAL)
for VoucherMonth in ('+@cols_c1+')
)
p order by GroupID,GroupName DESC '
select @query , @query1 ,@query2,@query3
SET @FinalQuery = @query + @query1+ @query2 + @query3;
EXECUTE( @FinalQuery )
SET @Counter += 1
END
end