February 2, 2015 at 10:07 pm
Comments posted to this topic are about the item Performance Improvement for Cursors in Stored Procedures
February 3, 2015 at 5:28 am
Interesting article. But I wasn't too familiar with cursors, having only written 1 to solve an issue, so I had to hit the internet to see where/how to apply the LOCAL, and FAST_FORWARD, options. I did find a link to an article that explained, in additional detail, why this is probably the way to go if cursors must be used.
http://sqlperformance.com/2012/09/t-sql-queries/cursor-options
February 3, 2015 at 5:28 am
Briant,
thanks for the article.
I think the first snippet of the code should be like this:
WHILE @@FETCH_STATUS = 0
BEGIN
SET @hDeptTaskInstructions = ' ' + @hDeptTaskInstructions + ' ' + isnull(@hTaskInstructions, ' ')
FETCH NEXT FROM TaskInstructions_cursor INTO @hTaskInstructions
END;
The presented logic has issues. Please double check it.
The bottom line is - if @hTaskInstructions is null, you should take of it before you append it to @hDeptTaskInstructions.
Otherwise you would lost all previous values
Thanks
February 3, 2015 at 5:44 am
Yakov Shlafman (2/3/2015)
Briant,thanks for the article.
I think the first snippet of the code should be like this:
WHILE @@FETCH_STATUS = 0
BEGIN
SET @hDeptTaskInstructions = ' ' + @hDeptTaskInstructions + ' ' + isnull(@hTaskInstructions, ' ')
FETCH NEXT FROM TaskInstructions_cursor INTO @hTaskInstructions
END;
The presented logic has issues. Please double check it.
The bottom line is - if @hTaskInstructions is null, you should take of it before you append it to @hDeptTaskInstructions.
Otherwise you would lost all previous values
Thanks
Thank you Yakov. I didn't write the original code. This was a real piece of application code from a system I recently inherited. Yes there are logical problems in much of the code (and many bugs). These stored procedures support an ancient app written in VB6 that has even more logical issues (which I have started refactoring for a rewrite into more modern VB.NET and will hopefully totally replace)...
My goal is to make things better every time I touch the code. I may not leave it perfect, but better than it was originally.
February 3, 2015 at 5:48 am
jyouq (2/3/2015)
Interesting article. But I wasn't too familiar with cursors, having only written 1 to solve an issue, so I had to hit the internet to see where/how to apply the LOCAL, and FAST_FORWARD, options. I did find a link to an article that explained, in additional detail, why this is probably the way to go if cursors must be used.http://sqlperformance.com/2012/09/t-sql-queries/cursor-options
The cursor modifiers are part of the cursor definition line. There are many other articles that explain in more detail what the modifiers do. If you are writing your own procedures, I'd recommend avoiding cursors if possible. But if you MUST use them, do whatever you can to speed them up, Local and Fast_Forward are the quick fixes for 90% or more of the cursor based procedures around.
February 3, 2015 at 6:26 am
Great and timely article. Using your search query I discovered we have 129 procs using cursors. However, I was hoping you would show code examples of the Local and Fast Forward. I'll look it up, but it's often helpful to see examples which are proven to work.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 3, 2015 at 7:03 am
Caruncles (2/3/2015)
Great and timely article. Using your search query I discovered we have 129 procs using cursors. However, I was hoping you would show code examples of the Local and Fast Forward. I'll look it up, but it's often helpful to see examples which are proven to work.Thanx!
I see that I left the cursor declaration off the code section when I copied the article from word to the online editor.
DECLARE TaskInstructions_cursor CURSOR Local Fast_Forward FOR
SELECT JobTaskData.TaskInstructions
FROM JobTaskData INNER JOIN Task ON JobTaskData.Task_ID = Task.Task_ID
WHERE (JobTaskData.Quote_ID = @hQuote_ID) AND
(JobTaskData.VersionNo = @hVersionNo) AND
(Task.Department_ID = @hDepartment_ID) AND
(Task.TaskTypeID = 1)
OPEN TaskInstructions_cursor
The code example was for a very simple usage of the cursor, but in general you can check the procedure to see if it is using the Cursor in the most common pattern...
Declare Cursor Cursorname For select whatever
Open Cusrorname
Fetch Next from cursorname
WHILE @@FETCH_STATUS = 0
begin
do something with the data
Fetch next from cursorname
end
close cursorname
deallocate cursorname
If that's your structure, you can add the Local and FAST_FORWARD keywords to the cursor definition for a quick improvement without changing the rest of the code or its behavior. (As always test on a non-production node first and make sure to save a copy of the original before changing it. There are good discussions about source control elsewhere.)
February 3, 2015 at 7:11 am
Thanx! Your article and links provider by responders has prompted me to do more research (which I should have already done!).
Good Job!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 3, 2015 at 7:47 am
1. I never use static cursor but cursor variable
declare @C cursor
set @C = cursor fast_forward for select * from kd_zp
would be interesting to see if there is a difference in performance.
Advantage is, that you do not need to de-allocate them.
1. best and logically correct way to concatenate results
select textstring = (select atnummer from lieferant FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
February 3, 2015 at 8:57 am
Nice article.
I use cursors reasonably often for administrative task.
Somewhere along the line I picked up a misconception. I though LOCAL and FAST_FORWARD were defaults.
LOCAL might be the default if it is configured so in the database options.
This code will show you what is currently configured for all your databases in the instance.
SELECTName
, is_Cursor_close_on_commit_on
, is_Cursor_close_on_commit_on_text = CASE is_Cursor_close_on_commit_on WHEN 1 THEN 'True' ELSE 'False' END
, is_local_cursor_default
, is_local_cursor_default_text = CASE is_local_cursor_default WHEN 1 THEN 'Local' ELSE 'Global' END
FROMsys.Databases
From BOL Declare Cursor
READ ONLY
Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
FORWARD_ONLY
Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.
FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
Cheers
February 3, 2015 at 9:27 am
This article provides a good INTRODUCTION to a set-based method of eliminating "row-by-agonizing-row" (RBAR) cursor code but it is not the complete story. There is more information to consider BEFORE deploying such code in a production environment.
The three-part SET method of eliminating the use of cursors has been abundantly discussed in previous articles by people I regard as real experts.
About two or three years ago different opinions were expressed regarding its use. One side was concerned that theoretically there is no formal guarantee that MS SQL Server will always respect the expected order/sequence of rows. The opposite side consider that in real life, on huge sets, the theoretical problem has never materialized and that dramatic speed improvements have been achieved (several orders of magnitude). Both the main proponent and opponent of this method have consistently demonstrated a level of expertise that is light-years above what I could ever dream of achieving.
I have no idea if this difference of opinions has ever been settled. Maybe the experts who expressed their opinions would provide a short update of the current situation.
Another suggestion was made by a friend regarding the use of CLR and dictionaries in C# - he claims this structure is blindingly fast even for a RBAR client-side execution and that it leaves the classic SQL Server stored procedure choking on its fumes.
February 3, 2015 at 9:31 am
I also read on an MS web site that the default cursor for SQL Server 2008 is set as GLOBAL.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 3, 2015 at 10:15 am
h.tobisch (2/3/2015)
1. I never use static cursor but cursor variabledeclare @C cursor
set @C = cursor fast_forward for select * from kd_zp
would be interesting to see if there is a difference in performance.
Advantage is, that you do not need to de-allocate them.
1. best and logically correct way to concatenate results
select textstring = (select atnummer from lieferant FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
I've never used cursor variables and can't speak to the performance differences with static cursors. I try to avoid cursors for procedures I've written, but it is an interesting question. Perhaps one that you could write an article about????
Thank you for an improvement on my concatenation query. I'll test it and may use it to replace my version. Like I said above, my goal is to make things better than I found them.
February 3, 2015 at 10:47 am
j-1064772 (2/3/2015)
This article provides a good INTRODUCTION to a set-based method of eliminating "row-by-agonizing-row" (RBAR) cursor code but it is not the complete story. There is more information to consider BEFORE deploying such code in a production environment.The three-part SET method of eliminating the use of cursors has been abundantly discussed in previous articles by people I regard as real experts.
About two or three years ago different opinions were expressed regarding its use. One side was concerned that theoretically there is no formal guarantee that MS SQL Server will always respect the expected order/sequence of rows. The opposite side consider that in real life, on huge sets, the theoretical problem has never materialized and that dramatic speed improvements have been achieved (several orders of magnitude). Both the main proponent and opponent of this method have consistently demonstrated a level of expertise that is light-years above what I could ever dream of achieving.
I have no idea if this difference of opinions has ever been settled. Maybe the experts who expressed their opinions would provide a short update of the current situation.
Another suggestion was made by a friend regarding the use of CLR and dictionaries in C# - he claims this structure is blindingly fast even for a RBAR client-side execution and that it leaves the classic SQL Server stored procedure choking on its fumes.
Yes, it's only an introduction and I can't claim to be an expert on rewriting cursor based procedures into SET based procedures. (Though I suspect I'll become an expert before I finish with this system).
My goal was to show a real example of code that could be improved, some simple steps that can be taken to improve it and possibly encourage others to make little changes to improve their systems.
I suspect your friend's CLR code would be faster in some situations, especially when it's tailored to a specific data set. But often what's fastest in one system is not so much in another. Like everything else, it should be tested on a DEV node with representative data before putting it into production. In my case, converting to CLR is more change than I'm prepared to make at this time for these stored procedures. It would be interesting for someone to do comparisons and write up the results though...
February 3, 2015 at 10:51 am
Your final solution isn't the most efficient way to concatenate the strings. The STUFF function combined with FOR XML PATH would be the best solution. It would look something like this:
SELECT
STUFF
((
SELECT ' ' + TaskInstructions
FROM JobTaskData jtd
JOIN Task t ON t.Task_ID = jtd.Task_ID
WHERE jtd.Quote_ID = @hQuote_ID
AND jtd.VersionNo = @hVersionNo
AND t.Department_ID = @hDepartment_ID
AND t.TaskTypeID = 1
FOR XML PATH('')
),1,1,'')
The existence of cursors in a code base is usually a sign that code was written by procedural-language programmers who "can do SQL". They have a tendency to think in linear terms instead of thinking in sets. SQL is far more efficient when dealing with sets rather than individual rows.
Long experience with an application that uses far too many cursors has taught me that the only time a cursor is the right answer is if an action taken with one record changes what needs done with another record. In any other situation, find a set-based solution. And if you're sure there isn't a set-based solution, you need to look harder. RBAR (row by agonizing row, or "rebar") is to be avoided wherever possible.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply