August 2, 2012 at 7:52 pm
I need to use a cursor to run through my table FormalEvaluation and then execute the stored procedure spMyScore. The two parameters in the stored procedure should be the values from the first two columns of my FormalEvaluation table.
Next, I would need to write the results of the store procedure to a new table.
I created a cursor but it runs out of memory and there should only be two to three thousand rows of data. I am new to this and I am probably not using the cursor or the stored procedure correctly. Can anyone help me get this working?
Below is my stored procedure:
CREATE PROCEDURE [dbo].[spMyScore]
AS
BEGIN
DECLARE @SchoolYearID INT
DECLARE @EvaluateeEvaluationID INT
DECLARE@SourceEvaluateeMilestone INT
DECLARE cur_MyFormalEval cursor for
select EvaluateeEvaluationID, SourceEvaluateeMilestone from FormalEvaluation
OPEN cur_MyFormalEval
FETCH NEXT FROM cur_MyFormalEval
INTO @EvaluateeEvaluationID, @SourceEvaluateeMilestone
WHILE (@@FETCH_STATUS <> -1)
SELECT @SchoolYearID = SchoolYearID
FROM EvaluateeEvaluation
WHERE ID = @EvaluateeEvaluationID
SELECT TV.OverallScore AS Score,
CASE WHEN TV.OverallScore > 3.5 THEN 'Highly Effective'
WHEN TV.OverallScore > 2.5 AND TV.OverallScore < 3.5 THEN 'Effective'
WHEN TV.OverallScore > 1.5 AND TV.OverallScore < 2.5 THEN (
CASE WHEN TV.SubRoleID <> 4 THEN 'Developing' ELSE 'Needs Improvement' END)
WHEN TV.OverallScore > 0.0 AND TV.OverallScore < 1.5 THEN 'Unsatisfactory'
END AS PerformanceLevel
FROM
(SELECT
SUM(CASE WHEN RD.Sequence=1 THEN RDS.DomainScore*0.68
WHEN RD.Sequence=2 THEN RDS.DomainScore*0.14
WHEN RD.Sequence=3 THEN RDS.DomainScore*0.08
WHEN RD.Sequence=4 THEN RDS.DomainScore*0.10
ELSE 0 END) AS OverallScore,
SR.AgendaLayoutID,
SR.ID AS SubRoleID
from [dbo].[fnRubricDomainScoresMarzano](@SourceEvaluateeMilestone) RDS
INNER JOIN RubricDomain RD ON RD.ID = RDS.RubricDomainID
INNER JOIN EvaluateeEvaluation TE ON TE.ID = RDS.EvaluateeEvaluationID
INNER JOIN Evaluatee T ON T.ID = TE.EvaluateeID
INNER JOIN SubRole SR on SR.ID = T.SubRoleID
WHERE RDS.EvaluateeEvaluationID = @EvaluateeEvaluationID
GROUP BY RDS.EvaluateeEvaluationID,
SR.AgendaLayoutID, SR.ID) AS TV
CLOSE cur_MyFormalEval
END
August 2, 2012 at 9:03 pm
typical cursor flow is:
declare cursor ...
open cursor ...
fetch from cursor ...
while (@@FETCH_STATUS <> -1)
BEGIN
/*do something using the values you fetched from the cursor.*/
fetch from cursor ... --get next values, (and also updates @@FETCH_STATUS)
END
close cursor ...
deallocate cursor ...
August 3, 2012 at 7:08 am
Without data, it's hard to say what the best approach is, but consider using CROSS APPLY instead of the CURSOR. Here's something that will help get you started.
SELECT EvaluateeEvaluationID, SourceEvaluateeMilestone
FROM FormalEvaluation AS fe
CROSS APPLY [dbo].[fnRubricDomainScoresMarzano](SourceEvaluateeMilestone) RDS
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2012 at 7:21 am
i think the cursor is missing the FETCH that is supposed to be inside the cursor, so it knows to get the next record.
as it is now, it is processing the same record(the first) over and over agian till you run out of memory.
every cursor should have
fetch
while
begin
fetch
end
this should be just before the END of the while:
FETCH NEXT FROM cur_MyFormalEval
INTO @EvaluateeEvaluationID, @SourceEvaluateeMilestone
Lowell
August 3, 2012 at 7:35 am
Thanks. I am trying the CROSS APPLY now and it has been running for over 14 minutes now.
August 3, 2012 at 7:58 am
SpringTownDBA (8/2/2012)
typical cursor flow is:
declare cursor ...
open cursor ...
fetch from cursor ...
while (@@FETCH_STATUS <> -1)
BEGIN
/*do something using the values you fetched from the cursor.*/
fetch from cursor ... --get next values, (and also updates @@FETCH_STATUS)
END
close cursor ...
deallocate cursor ...
Hi SpringTownDBA,
I changed the cursor and it is returning data now, but it is taking an extremely long time to do so.
CREATE PROCEDURE spSTTProcEvalTest
AS
BEGIN
DECLARE @EvaluateeEvaluationID INT
DECLARE @SourceEvaluateeMilestone INT
DECLARE @getEvals CURSOR
SET @getEvals = CURSOR FOR
SELECT EvaluateeEvaluationID, SourceEvaluateeMilestone FROM FormalEvaluation
OPEN @getEvals
FETCH NEXT
FROM @getEvals INTO @EvaluateeEvaluationID, @SourceEvaluateeMilestone
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @EvaluateeEvaluationID +' '+ @SourceEvaluateeMilestone
SELECT TV.OverallScore AS Score,
CASE WHEN TV.OverallScore > 3.5 THEN 'Highly Effective'
WHEN TV.OverallScore > 2.5 AND TV.OverallScore < 3.5 THEN 'Effective'
WHEN TV.OverallScore > 1.5 AND TV.OverallScore < 2.5 THEN (
CASE WHEN TV.SubRoleID <> 4 THEN 'Developing' ELSE 'Needs Improvement' END)
WHEN TV.OverallScore > 0.0 AND TV.OverallScore < 1.5 THEN 'Unsatisfactory'
END AS PerformanceLevel
FROM
(SELECT
SUM(CASE WHEN RD.Sequence=1 THEN RDS.DomainScore*0.68
WHEN RD.Sequence=2 THEN RDS.DomainScore*0.14
WHEN RD.Sequence=3 THEN RDS.DomainScore*0.08
WHEN RD.Sequence=4 THEN RDS.DomainScore*0.10
ELSE 0 END) AS OverallScore,
SR.AgendaLayoutID,
SR.ID AS SubRoleID
from [dbo].[fnRubricDomainScoresMarzano](@SourceEvaluateeMilestone) RDS
INNER JOIN RubricDomain RD ON RD.ID = RDS.RubricDomainID
INNER JOIN EvaluateeEvaluation TE ON TE.ID = RDS.EvaluateeEvaluationID
INNER JOIN Evaluatee T ON T.ID = TE.EvaluateeID
INNER JOIN SubRole SR on SR.ID = T.SubRoleID
WHERE RDS.EvaluateeEvaluationID = @EvaluateeEvaluationID
GROUP BY RDS.EvaluateeEvaluationID,
SR.AgendaLayoutID, SR.ID) AS TV
FETCH NEXT
FROM @getEvals INTO @EvaluateeEvaluationID, @SourceEvaluateeMilestone
END
CLOSE @getEvals
DEALLOCATE @getEvals
END
August 3, 2012 at 8:09 am
Ok, a couple of things to look at
1. how expensive is [dbo].[fnRubricDomainScoresMarzano]() ?
2. you also might try adding OPTION (RECOMPILE) to the end of the query (parameter sniffing)
... AS TV OPTION (RECOMPILE)
3. can you post the estimated execution plan?
August 3, 2012 at 8:21 am
Can you post the DDL (CREATE FUNCTION statement) for [dbo].[fnRubricDomainScoresMarzano]?
August 3, 2012 at 9:01 am
There is a slight logic error in the following CASE statement. You will get a NULL value returned if TV.OverallScore is 0.0, 1.5, 2.5, or 3.5. If you look at the second code block, you will see how I would write the code block.
CASE WHEN TV.OverallScore > 3.5 THEN 'Highly Effective'
WHEN TV.OverallScore > 2.5 AND TV.OverallScore < 3.5 THEN 'Effective'
WHEN TV.OverallScore > 1.5 AND TV.OverallScore < 2.5 THEN (
CASE WHEN TV.SubRoleID <> 4 THEN 'Developing' ELSE 'Needs Improvement' END)
WHEN TV.OverallScore > 0.0 AND TV.OverallScore < 1.5 THEN 'Unsatisfactory'
END AS PerformanceLevel
CASE WHEN TV.OverallScore > 3.5 THEN 'Highly Effective'
WHEN TV.OverallScore > 2.5 THEN 'Effective'
WHEN TV.OverallScore > 1.5 THEN (CASE WHEN TV.SubRoleID <> 4
THEN 'Developing'
ELSE 'Needs Improvement'
END)
WHEN TV.OverallScore >= 0.0 THEN 'Unsatisfactory'
END AS PerformanceLevel
August 3, 2012 at 9:01 am
Lynn Pettis (8/3/2012)
Can you post the DDL (CREATE FUNCTION statement) for [dbo].[fnRubricDomainScoresMarzano]?
Hi Lynn,
Here is the DDL for [dbo].[fnRubricDomainScoresMarzano].
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fnRubricDomainScoresMarzano] (@SourceEvaluateeMilestoneID INT)
RETURNS @tempTable TABLE(RubricDomainID INT, EvaluateeEvaluationID INT, DomainScore decimal(18,4))
AS
BEGIN
INSERT INTO @tempTable
SELECT TV.RubricDomainID,TV.EvaluateeEvaluationID,MAX(TV.DomainScore)
FROM (
SELECT
C.RubricDomainID,
TM.EvaluateeEvaluationID,
dbo.fnDomainCalculateMarzano('4',C.RubricDomainID,TM.EvaluateeEvaluationID,@SourceEvaluateeMilestoneID) AS DomainScore
FROM EvaluateeComponentScore TCS
INNER JOIN EvaluateeComponent TC ON TC.ID = TCS.EvaluateeComponentID
INNER JOIN Component C oN TC.ComponentID = C.ID
INNER JOIN EvaluateeMilestone TM ON TC.EvaluateeMilestoneID = TM.ID
WHERE TM.ID IN (SELECT SelectedEvaluateeMilestoneID FROM EvaluateeMilestoneScoreOn WHERE EvaluateeMilestoneID=@SourceEvaluateeMilestoneID)
GROUP BY
C.RubricDomainID,
TM.EvaluateeEvaluationID
UNION
SELECT
C.RubricDomainID,
TM.EvaluateeEvaluationID,
dbo.fnDomainCalculateMarzano('3',C.RubricDomainID,TM.EvaluateeEvaluationID,@SourceEvaluateeMilestoneID) AS DomainScore
FROM EvaluateeComponentScore TCS
INNER JOIN EvaluateeComponent TC ON TC.ID = TCS.EvaluateeComponentID
INNER JOIN Component C oN TC.ComponentID = C.ID
INNER JOIN EvaluateeMilestone TM ON TC.EvaluateeMilestoneID = TM.ID
WHERE TM.ID IN (SELECT SelectedEvaluateeMilestoneID FROM EvaluateeMilestoneScoreOn WHERE EvaluateeMilestoneID=@SourceEvaluateeMilestoneID)
GROUP BY
C.RubricDomainID,
TM.EvaluateeEvaluationID
UNION
SELECT
C.RubricDomainID,
TM.EvaluateeEvaluationID,
dbo.fnDomainCalculateMarzano('2',C.RubricDomainID,TM.EvaluateeEvaluationID,@SourceEvaluateeMilestoneID) AS DomainScore
FROM EvaluateeComponentScore TCS
INNER JOIN EvaluateeComponent TC ON TC.ID = TCS.EvaluateeComponentID
INNER JOIN Component C oN TC.ComponentID = C.ID
INNER JOIN EvaluateeMilestone TM ON TC.EvaluateeMilestoneID = TM.ID
WHERE TM.ID IN (SELECT SelectedEvaluateeMilestoneID FROM EvaluateeMilestoneScoreOn WHERE EvaluateeMilestoneID=@SourceEvaluateeMilestoneID)
GROUP BY
C.RubricDomainID,
TM.EvaluateeEvaluationID
UNION
SELECT
C.RubricDomainID,
TM.EvaluateeEvaluationID,
dbo.fnDomainCalculateMarzano('1',C.RubricDomainID,TM.EvaluateeEvaluationID,@SourceEvaluateeMilestoneID) AS DomainScore
FROM EvaluateeComponentScore TCS
INNER JOIN EvaluateeComponent TC ON TC.ID = TCS.EvaluateeComponentID
INNER JOIN Component C oN TC.ComponentID = C.ID
INNER JOIN EvaluateeMilestone TM ON TC.EvaluateeMilestoneID = TM.ID
WHERE TM.ID IN (SELECT SelectedEvaluateeMilestoneID FROM EvaluateeMilestoneScoreOn WHERE EvaluateeMilestoneID=@SourceEvaluateeMilestoneID)
GROUP BY
C.RubricDomainID,
TM.EvaluateeEvaluationID
)TV
GROUP BY
TV.RubricDomainID,TV.EvaluateeEvaluationID
RETURN
END
August 3, 2012 at 9:09 am
Okay, we have another tvf, dbo.fnDomainCalculateMarzano. Can we get this code as well? By the way, I think I may know part of the problem and I will know more when I see the code for this tvf as well.
August 3, 2012 at 9:17 am
Lynn Pettis (8/3/2012)
Okay, we have another tvf, dbo.fnDomainCalculateMarzano. Can we get this code as well? By the way, I think I may know part of the problem and I will know more when I see the code for this tvf as well.
Here is code for dbo.fnDomainCalculateMarzano.
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnDomainCalculateMarzano] ( @Value Varchar(5),@RubricDomainID int, @EvaluateeEvaluationID int,@SourceEvaluateeMilestoneID int )
RETURNS int
AS
BEGIN
DECLARE @result int
SET @result = 0
DECLARE scoreCursor CURSOR
FOR
SELECT
TV.Value,
CAST(SUM(TV.DomainPercent) AS DECIMAL(8,4))
FROM
(SELECT
RDC.EvaluateeEvaluationID,
RDC.RubricDomainID,
CONVERT(varchar(5),CONVERT(int,RDC.Value)) AS Value,
CONVERT(Decimal(8,4),RDC.DomainPercent) AS DomainPercent
FROM [vwRubricDomainCalculate] RDC
WHERE RDC.EvaluateeEvaluationID = @EvaluateeEvaluationID AND RDC.RubricDomainID = @RubricDomainID AND
RDC.SOURCEEvaluateeMilestoneID =@SourceEvaluateeMilestoneID) TV
GROUP BY TV.Value
DECLARE @SubRoleID INT
SELECT @SubRoleID = SubRoleID
FROM Evaluatee E
INNER JOIN EvaluateeEvaluation EE ON EE.EvaluateeID = E.ID
WHERE EE.ID = @EvaluateeEvaluationID
DECLARE @varPercent1 DECIMAL(8,4)
SET @varPercent1 =0.0000
DECLARE @varPercent2 DECIMAL(8,4)
SET @varPercent2 =0.0000
DECLARE @varPercent3 DECIMAL(8,4)
SET @varPercent3 =0.0000
DECLARE @varPercent4 DECIMAL(8,4)
SET @varPercent4 =0.0000
DECLARE @varPercent0 DECIMAL(8,4)
SET @varPercent0 =0.0000
OPEN scoreCursor
DECLARE @VarValue VARCHAR(5), @VarPercent DECIMAL(8,4)
FETCH NEXT FROM scoreCursor INTO @VarValue, @VarPercent
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @VarValue = '1' BEGIN SET @varPercent1 = @VarPercent END
ELSE IF @VarValue = '2' BEGIN SET @varPercent2 = @VarPercent END
ELSE IF @VarValue = '3' BEGIN SET @varPercent3 = @VarPercent END
ELSE IF @VarValue = '4' BEGIN SET @varPercent4 = @VarPercent END
ELSE IF @VarValue = '0' BEGIN SET @varPercent0 = @VarPercent END
FETCH NEXT FROM scoreCursor INTO @VarValue, @VarPercent
END
CLOSE scoreCursor
DEALLOCATE scoreCursor
IF @SubRoleID = 4
BEGIN
IF @Value = '4' AND (@varPercent1 = 0.0 AND @varPercent0 = 0.0 AND @varPercent4 > 0.74)
SET @result = 4
ELSE IF @Value = '3' AND ((@varPercent3 + @varPercent4) > 0.745)
SET @result = 3
ELSE IF @Value = '2' AND ((@varPercent0 + @varPercent1) < 0.5 AND (@varPercent3 + @varPercent4) < 0.75)
SET @result = 2
ELSE IF @Value = '1' AND ((@varPercent0 + @varPercent1) > 0.49)
SET @result = 1
ELSE
SET @result = 1
END
ELSE
BEGIN
IF @Value = '4' AND (@varPercent1 = 0.0 AND @varPercent0 = 0.0 AND @varPercent4 > 0.64)
SET @result = 4
ELSE IF @Value = '3' AND ((@varPercent3 + @varPercent4) > 0.645)
SET @result = 3
ELSE IF @Value = '2' AND ((@varPercent0 + @varPercent1) < 0.5 AND (@varPercent3 + @varPercent4) < 0.65)
SET @result = 2
ELSE IF @Value = '1' AND ((@varPercent0 + @varPercent1) > 0.49)
SET @result = 1
ELSE
SET @result = 1
END
RETURN @result
END
GO
August 3, 2012 at 9:45 am
Here is part of your problem.
The function dbo.fnRubricDomainScoresMarzano is a multi-statement table valued function. What this means is that the function does not return its value (table) until the table is completely populated.
The function dbo.fnDomainCalculateMarzano is a scalar function using a cursor. This function is called for each row in the result set in each of the queries in the function dbo.fnRubricDomainScoresMarzano.
And all of this is being done inside of a cursor.
To improve the performance of this stored procedure is, in my opinion, going to take a complete rewrite. In the end, it will be worthwhile.
August 3, 2012 at 10:07 am
Lynn Pettis (8/3/2012)
Here is part of your problem.The function dbo.fnRubricDomainScoresMarzano is a multi-statement table valued function. What this means is that the function does not return its value (table) until the table is completely populated.
The function dbo.fnDomainCalculateMarzano is a scalar function using a cursor. This function is called for each row in the result set in each of the queries in the function dbo.fnRubricDomainScoresMarzano.
And all of this is being done inside of a cursor.
To improve the performance of this stored procedure is, in my opinion, going to take a complete rewrite. In the end, it will be worthwhile.
Thank you so much, Lynn, for all of you help. I didn't write those two functions. I was just told I needed to use them. I will rewrite them to make all of this run faster.
August 3, 2012 at 10:32 am
R2E2 (8/3/2012)
Lynn Pettis (8/3/2012)
Here is part of your problem.The function dbo.fnRubricDomainScoresMarzano is a multi-statement table valued function. What this means is that the function does not return its value (table) until the table is completely populated.
The function dbo.fnDomainCalculateMarzano is a scalar function using a cursor. This function is called for each row in the result set in each of the queries in the function dbo.fnRubricDomainScoresMarzano.
And all of this is being done inside of a cursor.
To improve the performance of this stored procedure is, in my opinion, going to take a complete rewrite. In the end, it will be worthwhile.
Thank you so much, Lynn, for all of you help. I didn't write those two functions. I was just told I needed to use them. I will rewrite them to make all of this run faster.
Look at rewriting the multi-statement table valued function as an inline table valued function. Also, look at doing the same for the scalar function. Read this SQL Spackle article for more information on this: http://www.sqlservercentral.com/articles/T-SQL/91724/.
If you need help with either, please post back. There are several of us that are more than willing to assist where we can.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy