November 23, 2014 at 12:45 pm
Need to resolve issue by NOT using cursor or while loop (reason - I am running stored procedure over 7 years - so a lot of data, the part of the stored procedure (has cursor) takes 1 hour and 27 minutes for just one year x 7 years = about 10.5 hours). I replaced cursor with set-based query in this post.
The idea would be to replace the ID = 1 / 2 / 3 / 4 used in (step2) of stored procedure script
(UPDATE #Part1 and Insert into #Part2) with a variable parameter like @ID, or use another method without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop, rather use set based method/s.
-- Data script (creates data for 7 students):
CREATE TABLE [dbo].[ImportTable](
[StudentUID] [int] NOT NULL,
[PaymentPlanDetailUID] [int] NOT NULL,
[PaymentDueDate] [nvarchar](11) NULL,
[Description] [varchar](12) NOT NULL,
[MinAmountDue] [money] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (92208, 481543, N'01 Mar 2012', N'Payment_Plan', 34500.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (92208, 494511, N'01 Mar 2011', N'Payment_Plan', 22900.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (92208, 1004651, N'01 Mar 2014', N'Payment_Plan', 19990.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (92208, 1047653, N'01 Mar 2013', N'Payment_Plan', 27800.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (162435, 1040636, N'01 Feb 2012', N'Payment_Plan', 33000.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (162435, 1040637, N'01 Mar 2013', N'Payment_Plan', 30210.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (162435, 1040638, N'01 Mar 2014', N'Payment_Plan', 19990.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (162435, 1040639, N'01 Mar 2014', N'Payment_Plan', 6750.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (206864, 952752, N'01 Mar 2014', N'Payment_Plan', 19990.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (206864, 1015989, N'01 Aug 2014', N'Payment_Plan', 1350.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (206864, 1047731, N'01 Feb 2013', N'Payment_Plan', 28500.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (245308, 1023979, N'01 Mar 2013', N'Payment_Plan', 3400.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (245308, 1023980, N'01 Apr 2014', N'Payment_Plan', 3400.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (245308, 1052760, N'01 Oct 2014', N'Payment_Plan', 4850.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014679, N'01 Mar 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014680, N'01 Apr 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014681, N'01 May 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014682, N'01 Jun 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014683, N'01 Jul 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014684, N'01 Aug 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014685, N'01 Sep 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014686, N'01 Oct 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (263576, 1014687, N'01 Nov 2014', N'Payment_Plan', 1150.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005539, N'01 Mar 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005540, N'01 Apr 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005541, N'01 May 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005542, N'01 Jun 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005543, N'01 Jul 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005544, N'01 Aug 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005545, N'01 Sep 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005546, N'01 Oct 2014', N'Payment_Plan', 1650.0000)
INSERT [dbo].[ImportTable] ([StudentUID], [PaymentPlanDetailUID], [PaymentDueDate], [Description], [MinAmountDue]) VALUES (277563, 1005547, N'01 Nov 2014', N'Payment_Plan', 1650.0000)
------------------------------------------------------------------------------------------------------
-- function script (used in stored procedure to get years, months, dates):
create function [dbo].[ADV_rptage]
(
@date datetime,
@AgingDate datetime
)
RETURNS @DateDiference Table
(
[Years]int,
[Months]int,
[Days]int
)
AS
begin
DECLARE @tmpdate datetime, @years int, @months int, @days int
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, @AgingDate) - CASE WHEN (MONTH(@date) > MONTH(@AgingDate)) OR (MONTH(@date) = MONTH(@AgingDate) AND DAY(@date) > DAY(@AgingDate)) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, @AgingDate) - CASE WHEN DAY(@date) > DAY(@AgingDate) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, @AgingDate)
Insert into @DateDiference
SELECT @years as years, @months as months, @days AS days
RETURN
end
-----------------------------------------------------------------------------------
-- stored procedure script:
create procedure [dbo].[usp_TEST1]
@StudentUID int,
@AgingDate datetime
AS
---------------------------------------
--Step 1:
Declare @PaymentDueDate datetime
Create table #Part1
(
ID int identity(1,1),
StudentUID int,
PaymentPlanDetailUID int,
PaymentDueDate datetime,
[Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
MinAmountDue Decimal(15,2)
)
Insert Into #Part1
Select Distinct
StudentUID,
PaymentPlanDetailUID,
PaymentDueDate,
[Description],
sum(MinAmountDue) Over(Partition by PaymentDueDate) as Amount
from [dbo].[ImportTable]
Where
StudentUID = @StudentUID
Create table #Part2
(
PlanDetailUID int,
PaymentDueDate datetime,
[Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
Amount Decimal(15,2),
Years int,
Months int,
[Days] int,
MonthDuration int
)
---------------------------------------
--Step 2:
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 1
--- the idea would be to replace the ID = 1 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,@PaymentDueDate,@AgingDate) AS MonthDuration
FROM #Part1
Where
StudentUID = @StudentUID
AND
ID = 1
--- the idea would be to replace the ID = 1 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
------------------------
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 2
--- the idea would be to replace the ID = 2 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,@PaymentDueDate,@AgingDate) AS MonthDuration
FROM #Part1
Where
StudentUID = @StudentUID
AND
ID = 2
--- the idea would be to replace the ID = 2 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
---------------------------
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 3
--- the idea would be to replace the ID = 3 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,@PaymentDueDate,@AgingDate) AS MonthDuration
FROM #Part1
Where
StudentUID = @StudentUID
AND
ID = 3
--- the idea would be to replace the ID = 3 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
------------------------
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 4
--- the idea would be to replace the ID = 4 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(@PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,@PaymentDueDate,@AgingDate) AS MonthDuration
FROM #Part1
Where
StudentUID = @StudentUID
AND
ID = 4
--- the idea would be to replace the ID = 4 with a variable parameter like @ID, or another method
--- without having to manually specify ID = 1 / 2 / 3 / 4, but not use cursor or while loop.
--- If you remove where clause then years, months, dates, MonthDuration defaults to the last ID (4).
-- SELECT * FROM #Part1
SELECT * FROM #Part2
-------------------------------------------------------------------------------
-- Below are examples of running the stored procedure for each of the 7 students:
-- exec usp_TEST1 92208,'2014-11-13'
-- exec usp_TEST1 162435,'2014-11-13'
-- exec usp_TEST1 206864,'2014-11-13'
-- exec usp_TEST1 245308,'2014-11-13'
-- exec usp_TEST1 263576,'2014-11-13'
-- exec usp_TEST1 277563,'2014-11-13'
-- exec usp_TEST1 284574,'2014-11-13'
--------------------------------------------------------------------------------
November 23, 2014 at 2:20 pm
You might be able to do this by using a Tally table. Jeff Moden has a great article on it.
November 23, 2014 at 11:21 pm
All 4 sections of Step 2 are identical except for the "ID =" thing. Why wouldn't you just do the following for the proc?
create procedure [dbo].[usp_TEST1]
@StudentUID int,
@AgingDate datetime
AS
---------------------------------------
--Step 1:
Create table #Part1
(
ID int identity(1,1),
StudentUID int,
PaymentPlanDetailUID int,
PaymentDueDate datetime,
[Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
MinAmountDue Decimal(15,2)
)
Insert Into #Part1
Select Distinct
StudentUID,
PaymentPlanDetailUID,
PaymentDueDate,
[Description],
sum(MinAmountDue) Over(Partition by PaymentDueDate) as Amount
from [dbo].[ImportTable]
Where
StudentUID = @StudentUID
Create table #Part2
(
PlanDetailUID int,
PaymentDueDate datetime,
[Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
Amount Decimal(15,2),
Years int,
Months int,
[Days] int,
MonthDuration int
)
---------------------------------------
--Step 2:
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,PaymentDueDate,@AgingDate) AS MonthDuration
FROM #Part1
Where
StudentUID = @StudentUID
AND
ID IN (1,2,3,4)
SELECT * FROM #Part2
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2014 at 12:58 pm
--Step 2:
Insert into #Part2
Select Distinct
StudentUID,
PaymentDueDate,[Description],MinAmountDue,
(SELECT Years FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Years,
(SELECT Months FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Months,
(SELECT [Days] FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS [Days],
DATEDIFF(MONTH,PaymentDueDate,@AgingDate) AS MonthDuration
-- replaced @PaymentDueDate with PaymentDueDate based on Jeff's reply (above)
FROM #Part1
Where
StudentUID = @StudentUID
-- AND ID IN (1,2,3,4) -- removed
SELECT * FROM #Part2
/*
With 2 cursors in 2nd proc (original), if I select 9 campuses, query finishes in 4 minutes 10 seconds.
After removal of 2 cursors from 2nd proc (step 1 and step 2 was 50% of it), if I select 9 campuses, query finishes in 3 minutes 47 seconds.
After replacement of while loop with cursor in 1st proc (which calls 2nd proc), query finishes in 3 minutes 43 seconds.
So from 250 minutes to 223 minutes = 27 minutes reduced (i.e. query runs now 11% faster).
So after all the effort, no real gain.
Will see if I can incorporate 2nd proc into 1st proc, compare duration.
*/
November 25, 2014 at 1:38 pm
kevin_nikolai (11/25/2014)
--Step 2:/*
With 2 cursors in 2nd proc (original), if I select 9 campuses, query finishes in 4 minutes 10 seconds.
After removal of 2 cursors from 2nd proc (step 1 and step 2 was 50% of it), if I select 9 campuses, query finishes in 3 minutes 47 seconds.
After replacement of while loop with cursor in 1st proc (which calls 2nd proc), query finishes in 3 minutes 43 seconds.
So from 250 minutes to 223 minutes = 27 minutes reduced (i.e. query runs now 11% faster).
So after all the effort, no real gain.
Will see if I can incorporate 2nd proc into 1st proc, compare duration.
*/
11% as a first attempt is not bad. Performance tuning usually involves many incremental steps.
What determines the campuses?? This isn't referred to in any of the documentation provided. Is it the ID?
Look up Jeff Moden's splitter function.
If I understand what you need to do, there may be a whole list of ID's required. So, using this code: AND ID IN (1,2,3,4) , you would replace it with AND EXISTS(SELECT X.ID FROM DelimitedSplit8K('1, 2, 3, 4', ',') X WHERE X.id = StudentUID)
That makes multiple selects into a single select, assuming that is what you need to do.
The function ADV_rptage is kind of over kill. Do a cross apply
CROSS APPLY CROSS APPLY [ADV_rptage](PaymentDueDate,@AgingDate) as Dates
The select would then be:
Dates.YEARS,
Dates.Months,
And so forth.
Is "Select Distinct" causing a table scan? Did you look at the execution plan? You populated the table #part1 by using the DISTINCT, the rest of the queries do not need them.
This is probably not very efficient
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 1
What about:
SELECT @PaymentDueDate = PaymentDueDate FROM #Part1 WHERE ID = 1
This may not make much of a difference, but it looks better!
Lastly, you really do not need temp tables.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 25, 2014 at 2:32 pm
Hi Michael.
Based on Jeff's reply, I no longer do the following:
UPDATE #Part1
SET @PaymentDueDate = PaymentDueDate WHERE ID = 1
----------------------------------
I derive ID from:
Create table #Part1
(
ID int identity(1,1)
)
*** I no longer use WHERE ID = 1, 2, 3, 4
-----------------------------------
Will test your suggestion:
The function ADV_rptage is kind of over kill. Do a cross apply.
CROSS APPLY [ADV_rptage](PaymentDueDate,@AgingDate) as Dates
The select would then be:
Dates.YEARS,
Dates.Months,
Dates.Days
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply