June 26, 2012 at 1:16 am
I have one scenario, I need to calcualte performance for each ID based on the updated value, so i used Cursor, IN Cursor I am calling the performance SP and passing ID, My cursor is running 35 times. Will it affect the perfomance in future. What is the another way to do this?
June 26, 2012 at 1:52 am
Cursors have bad reputation, but in my opinion it is more because they are being used where they shouldn't be used. There were many cases in the past that I was able to boost performance by modifying code that used cursor into code that used DML statement on all rows. Of course that doesn't mean that cursors should be avoided in all cost. There are times that it helps us. For me it is mostly on administration scripts (for example fixing fragmentation scripts). Since we have no idea about your code and what you are trying to do, no one can tell you if you should modify your code and logic.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 26, 2012 at 1:55 am
Following on from Adi.
Please detail exactly what you are trying to acheive.
Supply DDL, DML and sample data along with the expected outcome (2nd link in my signature will help you on that).
With this information we would be able to detail what the best way to tackle the problem is, be it in a cursor or a set based operation.
June 26, 2012 at 3:50 am
Without seeing & testing the code, it's hard to make a definitive answer. But based on what you've said, yes, I suspect strongly that the cursor is going to affect performance negatively. It sounds like you would be better off using an IN statement or using a tally table to turn a list into a derived table that you can join against. For details on the tally table approach, look up the words "tally table" and "Jeff Moden" in the search box here on SSC. You'll find what you need there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 26, 2012 at 5:01 am
Hi Thanks for all of your reply....
Here is my code
--- Cursor To EXECUTE PERFORMANCE SP FOR EACH METRICID
DECLARE @METID INT
DECLARE @METRICVALUE FLOAT
DECLARE @GOAL FLOAT
DECLARE @TARGET FLOAT
DECLARE @PDID BIGINT
DECLARE @PHASE BIGINT
DECLARE @METRICID CURSOR
SET @METRICID = CURSOR FOR
SELECT MEtricsID,METRICSVALUE,GOAL,TARGET,PDID,PHASEID
FROM @LEANTEMP
OPEN @METRICID
FETCH NEXT
FROM @METRICID INTO @METID,@METRICVALUE,@GOAL,@TARGET,@PDID,@PHASE
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [EFFORTVARIANCEPERFORMANCE] @LEVELVALUEID,@METID,@METRICVALUE,@GOAL,@TARGET,@FROMDATE,@TODATE,@PDID,@PHASE
FETCH NEXT
FROM @METRICID INTO @METID,@METRICVALUE,@GOAL,@TARGET,@PDID,@PHASE
END
CLOSE @METRICID
DEALLOCATE @METRICID
June 26, 2012 at 6:25 am
Again, not knowing what's happening inside that procedure, I can't definitively say that yes, you can eliminate the cursor. But I'm still very strongly suggesting it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 26, 2012 at 11:04 pm
Here I am taking records from @LEANTEMP table and passing the values to SP to calculate performance for each Metric ID.
June 26, 2012 at 11:35 pm
Hi,
I don't know how it will affect your performance but definetely cursor is going to slow down the performance of your script above. In your case, how many records the table will grow up in the future?
It would be better to optimize it by adding a ID (identity column) to @LEANTEMP table and do a incremental loop. This way you can improve the performance. Also, you can think of changing your logic into SET-based approach. You can go thru the below link for SET-based approach.
Regards,
Ravi.
June 27, 2012 at 12:00 am
Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂
June 27, 2012 at 12:12 am
What kind of result is your SP returning??....If you are doing it for the whole table then may be you can change the SP to a Table Valued Function and Apply it on the whole table(Cross Apply)... Depends on what your SP is returning.
June 27, 2012 at 12:17 am
vijayarani87.s (6/27/2012)
Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂
Is all you have done replaced the CURSOR with a WHILE ?
This is a common anti-pattern, and any performance improvement is due to the default cursor being a writable cursor not read only.
Brad Shultz has a great 3 part article series starting here:
http://bradsruminations.blogspot.co.uk/2010/05/truth-about-cursors-part-1.html
Can you post the code of EFFORTVARIANCEPERFORMANCE , depending on complexity this could be turned into an inline table valued function ( or even a view) which will help considerably.
June 27, 2012 at 12:22 am
Hi Dave here is my code for performance
ALTER PROCEDURE [SCHEMAMANLOG].[PROC_TESTING_EFFORTVARIANCEPERFORMANCE]
(
@LEVELVALUEID BIGINT,
@METRICSID INT ,
@METRICSVALUE FLOAT,
@GOAL FLOAT,
@TARGET FLOAT,
@FROMDATE DATE,
@TODATE DATE,
@ProcessDisciplineID INT,
@PHASEID BIGINT
)
AS
BEGIN
DECLARE @COLORIND INT
DECLARE @PERFORMANCE varchar(50)
SELECT @COLORIND = HIGHERLOWERTHEBETTER
FROM SCHEMAMANLOG.MASMETRICS
WHERE METRICSID=@METRICSID
IF(@COLORIND=3 or @COLORIND=1) -- Higher THE BETTER/NORMAL
BEGIN
IF(@METRICSVALUE >= @TARGET)
set @PERFORMANCE='GREEN'
ELSE IF(@METRICSVALUE < @TARGET)
set @PERFORMANCE='RED'
ELSE IF(@METRICSVALUE is NULL)
set @PERFORMANCE='BLUE'
END
IF(@COLORIND=2) --Lower The Better
BEGIN
IF(@METRICSVALUE <= @TARGET)
set @PERFORMANCE='GREEN'
ELSE IF(@METRICSVALUE > @TARGET)
set @PERFORMANCE='RED'
ELSE IF(@METRICSVALUE is NULL)
set @PERFORMANCE='BLUE'
END
--OVERALL
Begin
UPDATE MEAS
SET MEAS.TSPP_PERFORMANCE = (CASE when @METRICSID=195 THEN @PERFORMANCE ELSE MEAS.TSPP_Performance END),
MEAS.PCE_PERFORMANCE = (CASE when @METRICSID=143 THEN @PERFORMANCE ELSE MEAS.PCE_PERFORMANCE END),
MEAS.ADLS3_PERFORMANCE = (CASE when @METRICSID=148 THEN @PERFORMANCE ELSE MEAS.ADLS3_PERFORMANCE END),
MEAS.EDR_PERFORMANCE = (CASE when @METRICSID=149 THEN @PERFORMANCE ELSE MEAS.EDR_PERFORMANCE END),
MEAS.CPUOM_PERFORMANCE = (CASE when @METRICSID=154 THEN @PERFORMANCE ELSE MEAS.CPUOM_PERFORMANCE END),
MEAS.RDL_PERFORMANCE = (CASE when @METRICSID=155 THEN @PERFORMANCE ELSE MEAS.RDL_PERFORMANCE END),
MEAS.ACOQ_PERFORMANCE = (CASE when @METRICSID=205 THEN @PERFORMANCE ELSE MEAS.ACOQ_PERFORMANCE END),
MEAS.PCOQ_PERFORMANCE = (CASE when @METRICSID=206 THEN @PERFORMANCE ELSE MEAS.PCOQ_PERFORMANCE END),
MEAS.FCOQ_PERFORMANCE = (CASE when @METRICSID=207 THEN @PERFORMANCE ELSE MEAS.FCOQ_PERFORMANCE END),
MEAS.PREEFF_PERFORMANCE = (CASE when @METRICSID=208 THEN @PERFORMANCE ELSE MEAS.PREEFF_PERFORMANCE END),
MEAS.ADD_PERFORMANCE = (CASE when @METRICSID=214 THEN @PERFORMANCE ELSE MEAS.ADD_PERFORMANCE END),
MEAS.ADDS1_PERFORMANCE = (CASE when @METRICSID=218 THEN @PERFORMANCE ELSE MEAS.ADDS1_PERFORMANCE END),
MEAS.ADDS2_PERFORMANCE = (CASE when @METRICSID=219 THEN @PERFORMANCE ELSE MEAS.ADDS2_PERFORMANCE END),
MEAS.RDD_PERFORMANCE = (CASE when @METRICSID=220 THEN @PERFORMANCE ELSE MEAS.RDD_PERFORMANCE END),
MEAS.SVTD_PERFORMANCE = (CASE when @METRICSID=221 THEN @PERFORMANCE ELSE MEAS.SVTD_PERFORMANCE END),
MEAS.SVTE_PERFORMANCE = (CASE when @METRICSID=225 THEN @PERFORMANCE ELSE MEAS.SVTE_PERFORMANCE END),
MEAS.TC_PERFORMANCE = (CASE when @METRICSID=229 THEN @PERFORMANCE ELSE MEAS.TC_PERFORMANCE END),
MEAS.EC_PERFORMANCE = (CASE when @METRICSID=230 THEN @PERFORMANCE ELSE MEAS.EC_PERFORMANCE END),
MEAS.TED_PERFORMANCE = (CASE when @METRICSID=234 THEN @PERFORMANCE ELSE MEAS.TED_PERFORMANCE END)
FROM schemamanlog.trnleanmetricsperformance "MEAS"
WHERE MEAS.c20phaseid IS NULL
AND MEAS.c20processdisciplineid IS NULL
AND MEAS.levelvalueID= @LEVELVALUEID
AND MEAS.fromdate = @FROMDATE
AND MEAS.todate = @TODATE
AND MEAS.isActive = 1
END
END
June 27, 2012 at 4:01 am
vijayarani87.s (6/27/2012)
Thanks Ravi i have changed the code, I am using while loop now...Hope it will not affect my performance in future 🙂
Just so we're clear, a WHILE loop is another type of cursor. They may not perform any faster, and can, depending on how it's used, perform slower.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 27, 2012 at 4:03 am
Based on that query code for the procedure, you can do the entire thing just by joining the query that fills your cursor to the update statement. You can do it in a single batch statement and be done.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 27, 2012 at 4:44 am
I dont think i can do this stuff in a single statement, because i need to find which colouring ID each metrics belongs to based on that only i can update records for each metric id
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply