Stored procedure is taking 1 hr to execute and loss the connection with the database as number of record is huge so kindly help me out

Question

1.00/5 (1 vote)

See more:

, +

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

Posted 28-Apr-17 19:48pm

Member 13158185

Updated 29-Apr-17 5:19am

Add a Solution

Comments

CHill60 29-Apr-17 10:16am

I seriously doubt you actually need to use a WHILE loop. You could probably use a JOIN but there is too much code to wade through for me to prove it. That would certainly speed things up.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-04-29T05:19:00

Have a look at the query

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 ...

It is full of sub-queries. You need to replace those sub-queries with a join.
For example

SQL

(SELECT GroupName FROM Mst_Group WHERE GroupID = Mst_ProfitNLossMapping.GroupID) AS GroupName,
'' AS LedgerName,NULL AS BranchYear, ...
FROM Mst_ProfitNLossMapping M
...

can be replaced with

SQL

G.GroupName, 
'' AS LedgerName,NULL AS BranchYear, ...
FROM Mst_ProfitNLossMapping M
INNER JOIN Mst_Group G ON GroupID = M.GroupID)
...

You are actually querying vw_report 8 times per loop! That's going to hit your performance. Instead of using all of those sub-queries in the CASE clauses, query the information once only and join to the results e.g.

SQL

;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

That should simplify that calculation and make it easier to read - I think there is actually a bug in there.

As I said in my comment, there is probably scope to join to the @ScheduleTable table and get rid of that WHILE loop. I don't normally post links to my own stuff but my article has some information on how to avoid loops and I'm not going to repeat it all here - Processing Loops in SQL Server[^]