June 4, 2014 at 12:46 am
GO
/****** Object: StoredProcedure [dbo].[Report_AccBal] Script Date: 06/02/2014 22:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================
-- Author:
-- Create date:
-- Description:
-- ================================================
--exec [Report_AccBal] '1,2,3,4,5','1,2,3,4,5,6,7,8,9','06/03/2014'
ALTER PROCEDURE [dbo].[Report_AccBal]
@sPortfolio varchar(100),
@sAcctStatus varchar(100),
@TxnDate varchar(50)
AS
BEGIN
Set NoCount ON;
declare @ID int,@Total int,@vCreditApp nvarchar(50),@iRateType int,@mGPS money,@PrinBal money,@mFee money,@mAccIntRec money;
declare @tbl table(ID int IDENTITY(1,1),vCreditApp nvarchar(50),mAmtFinanced money,CyberAcc nvarchar(50),iRateType int,
mGPS money,PrinBal money,mFee money,mAccIntRec money)
-------------------------------------
declare @dtTxnDate datetime
if isdate(@TxnDate)=1
begin
set @dtTxnDate=convert(datetime,@TxnDate,101);
end
else
begin
goto ProcEnd;
end
-------------------------------------------
declare @tblPortfolio table(iPortfolio int)
Insert into @tblPortfolio(iPortfolio)Select [value] from dbo.fn_Split(@sPortFolio,',')
-------------------------------------------
declare @tblAcctStatus table(iAcctStatus int)
Insert into @tblAcctStatus(iAcctStatus)Select [value] from dbo.fn_Split(@sAcctStatus,',')
-------------------------------------------
--selected portfolio and Account Status
declare @vPortfolioList varchar(500),@vAccStatusList varchar(500)
select @vPortfolioList='',@vAccStatusList=''
Select @vPortfolioList=@vPortfolioList+','+PortfolioName From Portfolio where ID in (Select iPortfolio from @tblPortfolio)
if len(@vPortfolioList)>0 and left(@vPortfolioList,1)=','
begin
set @vPortfolioList=SUBSTRING( @vPortfolioList, 2, len(@vPortfolioList)-1)
end
Select @vAccStatusList=@vAccStatusList+','+vAccStatus From AccStatus where ID in (Select iAcctStatus from @tblAcctStatus)
if len(@vAccStatusList)>0 and left(@vAccStatusList,1)=','
begin
set @vAccStatusList=SUBSTRING( @vAccStatusList, 2, len(@vAccStatusList)-1)
end
Select (@vPortfolioList)as PortfolioList,(@vAccStatusList)as AcctStatusList,('-')as Fromdate,(@TxnDate)as Todate
-------------------------------------------
Insert Into @tbl(vCreditApp,mAmtFinanced,iRateType,CyberAcc)
Select L.vCreditApp,L.mAmtFinanced,L.iRateType,F.vAccountNumber
From [dbo].[Get_AcctSnap](@dtTxnDate) S inner join LoanSetupRegular L on S.nCreditApp=L.vCreditApp
left outer join FundingCreditApp F on L.vCreditApp=F.vCreditAppNum
where S.iPortfolio in (Select iPortfolio from @tblPortfolio) and
S.iAcctStatus in (Select iAcctStatus from @tblAcctStatus) and
datediff(dd,L.dCreationDate,@dtTxnDate)>=0
---------------------------------------
Select @ID=1,@Total=count(ID) From @tbl
---------------------------------------
while @ID<=@Total
begin
Select @vCreditApp=vCreditApp,@iRateType=iRateType From @tbl where ID=@ID
if len(@vCreditApp)>0
begin
Select @mFee=[dbo].[Get_FeeDueTxn](@vCreditApp,@dtTxnDate);
Select @mGPS=[dbo].[Get_OtherAmts](@vCreditApp,@dtTxnDate);
set @PrinBal=dbo.Get_PrincipalBalanceTxn(@vCreditApp,@dtTxnDate);
if @iRateType=2--Rule of 78
begin
set @mAccIntRec=[dbo].[Get_InterestR78](@vCreditApp,@dtTxnDate,1);
end
else--simple interest
begin
set @mAccIntRec=[dbo].[Get_InterestTxn](@vCreditApp,@dtTxnDate,null);
end
update @tbl set PrinBal=@PrinBal,mFee=@mFee,mAccIntRec=@mAccIntRec--,mGPS=@mGPS
where ID=@ID;
end
select @ID=@ID+1,@vCreditApp='',@iRateType=0,@mFee=0,@mAccIntRec=0,@mGPS=0;
end
---------------------------------------
Select vCreditApp,CyberAcc,dbo.GetCustomerName3(vCreditApp) as vName,
PrinBal,mFee,mAccIntRec,
(isnull(PrinBal,0)+isnull(mFee,0))as mAccBal
From @tbl;
---------------------------------------
ProcEnd:
END
June 4, 2014 at 3:50 am
may be like this..not sure if work or not as don't have sample data
INSERT INTO @tbl
(
vCreditApp,
mAmtFinanced,
iRateType,
CyberAcc,
mGPS,
PrinBal,
mFee,
mAccIntRec
)
SELECT L.vCreditApp,
L.mAmtFinanced,
L.iRateType,
F.vAccountNumber,
[dbo].[Get_OtherAmts](L.vCreditApp, @dtTxnDate) AS MGPS,
dbo.Get_PrincipalBalanceTxn(L.vCreditApp, @dtTxnDate) PRINBAL,
[dbo].[Get_FeeDueTxn](L.vCreditApp, @dtTxnDate),
CASE
WHEN L.iRateType = 2
THEN [dbo].[Get_InterestR78](L.vCreditApp, @dtTxnDate, 1)
ELSE [dbo].[Get_InterestTxn](L.vCreditApp, @dtTxnDate, NULL)
END
FROM [dbo].[Get_AcctSnap](@dtTxnDate) S
INNER JOIN LoanSetupRegular L ON S.nCreditApp = L.vCreditApp
LEFT OUTER JOIN FundingCreditApp F ON L.vCreditApp = F.vCreditAppNum
WHERE S.iPortfolio IN
(
SELECT iPortfolio
FROM @tblPortfolio
)
AND S.iAcctStatus IN
(
SELECT iAcctStatus
FROM @tblAcctStatus
)
AND DATEDIFF(dd, L.dCreationDate, @dtTxnDate) >= 0
June 4, 2014 at 7:46 am
Hi and welcome to the forums. You have a lot more issues with this stored proc than just a while loop. I can only imagine how slow this must run. Let's take a look at the problem areas one at a time.
First we have a split function. It appears to be a table valued function but given the rest of the code it deserves a closer examination. Does this splitter have a loop, cursor or use xml? Does it have more than 1 statement? If the answer to either of these is yes then you need to read the article in my signature about splitting strings and replace your split function with a faster one immediately.
Scalar functions. Your code is full of scalar functions. I counted 7 of them.
[Get_acctsnap]
[Get_feeduetxn]
[Get_otheramts]
Get_principalbalancetxn
[Get_interestr78]
[Get_interesttxn]
Getcustomername3
Scalar functions are horrible for performance because they basically force you to process stuff row by row. Given the large amount of these I suspect your system is full of scalar functions. You should read up on scalar functions and how terrible they are from a performance perspective.
SARGability. This is the ability of the sql engine to effectively use indexes. You have at least one nonSARGable predicate.
Datediff(dd, L.dCreationDate, @dtTxnDate) >= 0
This means that the sql engine is forced to evaluate every single row in the table. If I understand your logic correctly this should be the same thing.
L.dCreationDate > dateadd(DAY, 1, cast(@dtTxnDate as date))
We can help point you in the right direction here but this is a pretty big task for an online forum. Your code needs to be completely rebuilt if you really want to make this fast.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 7:46 am
Here is a formatted version of the stored proc for anybody else who might be interested.
GO
/****** Object: StoredProcedure [dbo].[Report_AccBal] Script Date: 06/02/2014 22:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================
-- Author:
-- Create date:
-- Description:
-- ================================================
--exec [Report_AccBal] '1,2,3,4,5','1,2,3,4,5,6,7,8,9','06/03/2014'
ALTER PROCEDURE [dbo].[Report_accbal] @sPortfolio VARCHAR(100),
@sAcctStatus VARCHAR(100),
@TxnDate VARCHAR(50)
AS
BEGIN
SET NoCount ON;
DECLARE @ID INT,
@Total INT,
@vCreditApp NVARCHAR(50),
@iRateType INT,
@mGPS MONEY,
@PrinBal MONEY,
@mFee MONEY,
@mAccIntRec MONEY;
DECLARE @tbl TABLE
(
ID INT IDENTITY(1, 1),
vCreditApp NVARCHAR(50),
mAmtFinanced MONEY,
CyberAcc NVARCHAR(50),
iRateType INT,
mGPS MONEY,
PrinBal MONEY,
mFee MONEY,
mAccIntRec MONEY
)
-------------------------------------
DECLARE @dtTxnDate DATETIME
IF Isdate(@TxnDate) = 1
BEGIN
SET @dtTxnDate=CONVERT(DATETIME, @TxnDate, 101);
END
ELSE
BEGIN
GOTO ProcEnd;
END
-------------------------------------------
DECLARE @tblPortfolio TABLE
(
iPortfolio INT
)
INSERT INTO @tblPortfolio
(iPortfolio)
SELECT [value]
FROM dbo.Fn_split(@sPortFolio, ',')
-------------------------------------------
DECLARE @tblAcctStatus TABLE
(
iAcctStatus INT
)
INSERT INTO @tblAcctStatus
(iAcctStatus)
SELECT [value]
FROM dbo.Fn_split(@sAcctStatus, ',')
-------------------------------------------
--selected portfolio and Account Status
DECLARE @vPortfolioList VARCHAR(500),
@vAccStatusList VARCHAR(500)
SELECT @vPortfolioList = '',
@vAccStatusList = ''
SELECT @vPortfolioList = @vPortfolioList + ',' + PortfolioName
FROM Portfolio
WHERE ID IN (SELECT iPortfolio
FROM @tblPortfolio)
IF Len(@vPortfolioList) > 0
AND LEFT(@vPortfolioList, 1) = ','
BEGIN
SET @vPortfolioList=Substring(@vPortfolioList, 2, Len(@vPortfolioList) - 1)
END
SELECT @vAccStatusList = @vAccStatusList + ',' + vAccStatus
FROM AccStatus
WHERE ID IN (SELECT iAcctStatus
FROM @tblAcctStatus)
IF Len(@vAccStatusList) > 0
AND LEFT(@vAccStatusList, 1) = ','
BEGIN
SET @vAccStatusList=Substring(@vAccStatusList, 2, Len(@vAccStatusList) - 1)
END
SELECT ( @vPortfolioList )AS PortfolioList,
( @vAccStatusList )AS AcctStatusList,
( '-' ) AS Fromdate,
( @TxnDate ) AS Todate
-------------------------------------------
INSERT INTO @tbl
(vCreditApp,
mAmtFinanced,
iRateType,
CyberAcc)
SELECT L.vCreditApp,
L.mAmtFinanced,
L.iRateType,
F.vAccountNumber
FROM [dbo].[Get_acctsnap](@dtTxnDate) S
INNER JOIN LoanSetupRegular L
ON S.nCreditApp = L.vCreditApp
LEFT OUTER JOIN FundingCreditApp F
ON L.vCreditApp = F.vCreditAppNum
WHERE S.iPortfolio IN (SELECT iPortfolio
FROM @tblPortfolio)
AND S.iAcctStatus IN (SELECT iAcctStatus
FROM @tblAcctStatus)
AND Datediff(dd, L.dCreationDate, @dtTxnDate) >= 0
---------------------------------------
SELECT @ID = 1,
@Total = Count(ID)
FROM @tbl
---------------------------------------
WHILE @ID <= @Total
BEGIN
SELECT @vCreditApp = vCreditApp,
@iRateType = iRateType
FROM @tbl
WHERE ID = @ID
IF Len(@vCreditApp) > 0
BEGIN
SELECT @mFee = [dbo].[Get_feeduetxn](@vCreditApp, @dtTxnDate);
SELECT @mGPS = [dbo].[Get_otheramts](@vCreditApp, @dtTxnDate);
SET @PrinBal=dbo.Get_principalbalancetxn(@vCreditApp, @dtTxnDate);
IF @iRateType = 2--Rule of 78
BEGIN
SET @mAccIntRec=[dbo].[Get_interestr78](@vCreditApp, @dtTxnDate, 1);
END
ELSE--simple interest
BEGIN
SET @mAccIntRec=[dbo].[Get_interesttxn](@vCreditApp, @dtTxnDate, NULL);
END
UPDATE @tbl
SET PrinBal = @PrinBal,
mFee = @mFee,
mAccIntRec = @mAccIntRec--,mGPS=@mGPS
WHERE ID = @ID;
END
SELECT @ID = @ID + 1,
@vCreditApp = '',
@iRateType = 0,
@mFee = 0,
@mAccIntRec = 0,
@mGPS = 0;
END
---------------------------------------
SELECT vCreditApp,
CyberAcc,
dbo.Getcustomername3(vCreditApp) AS vName,
PrinBal,
mFee,
mAccIntRec,
( Isnull(PrinBal, 0) + Isnull(mFee, 0) )AS mAccBal
FROM @tbl;
---------------------------------------
PROCEND:
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 3:09 am
Hello,
Thanks for your feedback.
I have attached the related tables and functions in "Queries.txt" file.
The testing data is shared in "TestingData.xls" file.
Please look into issue and let me know if you need any more information.
If you want to chat please contact me in skype .
My skype address is vkasireddy_ns.
Thanks & Regards,
Vinod
June 5, 2014 at 5:52 am
Before we look into the issue, have you read, understood and applied the suggestions in Sean's post regarding splitting strings, SARGability, scalar functions etc.? There's little point in working on your code as-is if you haven't applied these suggestions first.
June 5, 2014 at 6:39 am
vinodkumark_mca (6/5/2014)
Hello,Thanks for your feedback.
I have attached the related tables and functions in "Queries.txt" file.
The testing data is shared in "TestingData.xls" file.
Please look into issue and let me know if you need any more information.
If you want to chat please contact me in skype .
My skype address is xxxxxxxxxxx.
Thanks & Regards,
Vinod
Hi Vinod
Anyone wishing to help you using the structures you've provided will have to spend a half day or more converting those lists of table contents into INSERT statements to get the data into the tables, before they can begin working on your performance problem. Please read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[/url] which contains comprehensive instructions for preparing materials to augment your post.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 5, 2014 at 10:08 am
I was hopeful when I saw that you had posted some details. However, what you posted scares me to death. In addition to Chris' comment about not having sample data in a consumable format you have a real disaster on your hands here. I can guarantee you that there is absolutely no chance you are going to make this any faster. The loop in your original procedure is not even a drop in the bucket of issues with what you posted. I started through all your functions and they are full of very serious issues from a performance standpoint.
Your split function is probably the absolute slowest of all possible methods for a splitter. It is a multi-statment table function that contains a while loop. Replace that immediately with the function found by following the link in my signature about splitting strings.
From there is goes downhill very quickly. You have scalar function after scalar function that have nested calls to other scalar functions.
Then you have a bunch of table valued functions. This is good idea but all of them are multi statement table valued functions. These are notoriously bad for performance. In fact, a while loop or a cursor are generally faster.
The scope of this situation is far more than online forum can correct. I have one recommendation that will help you out of your situation. You need to hire a professional. Find somebody local who is a performance expert, there are several people around this website that do this kind of thing regularly (I am not one of those). The upside is that if you find a good professional they will not only help you fix the problem, they will help educate you on how to avoid this kind of thing in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply