November 3, 2011 at 8:39 am
I have an application using a fast_forward cursor to loop through data in a table and perform other tasks in each cursor iteration.
The cursor is defined as follows:
DECLARE invoice_cursor CURSOR FAST_FORWARD FOR
...
SELECT ...
FROM [invoice]
...
...
That statement is blocking inserts into the invoice table.
Would the blocking be alleviated if I placed a NOLOCK hint in the SELECT statement above?
Or would it be better if I used a STATIC cursor instead to alleviate the blocking?
And one last question: are shared locks acquired on the underlying tables when a fast_forward cursor is first opened?
I would think yes, but I would like to confirm (for my sanity's sake 🙂 ).
(I already know that shared locks are obtained on each row as it is fetched).
And, yes, I agree that cursors are "bad" and all that, but this is not my application. 🙂
I'm just trying to manage the blocking without major code rewrites.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 3, 2011 at 9:05 am
If you have to use a cursor for this, make it STATIC instead of FAST_FORWARD. It'll probably be faster that way, and it will definitely release locks faster.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2011 at 9:09 am
GSquared (11/3/2011)
If you have to use a cursor for this, make it STATIC instead of FAST_FORWARD. It'll probably be faster that way, and it will definitely release locks faster.
Thank you.
Won't STATIC take a longer time to open since it needs to load the whole resultset on tempdb?
During that time it will block writers to the underlying tables.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 3, 2011 at 11:14 am
Try setting the Transaction Isolation Level to Snapshot (if the database will allow that). That will help with blocking during that time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 2:36 am
I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.
November 4, 2011 at 6:28 am
r.dragoi-1095738 (11/4/2011)
I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.
If dirty reads are acceptable, then NoLock, or Read Uncommitted, are acceptable. If the data actually matters to anyone anywhere at all, then don't use those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 8:25 am
r.dragoi-1095738 (11/4/2011)
I would use the hint first (nolock) .. if that still doesn't work I would select the rows first into a variable table and after that I would use the cursor (over the variable table) , I assume that you don't update anything while you are using the cursor -- since you are using FAST_FORWARD cursor.
Thank you. Storing the data in a table variable would ensure no row locks are taken on the underlying data while a row is fetched
However, inserting the data into the table variable would still place shared locks until the base SELECT statement is completed (unless NOLOCK is used).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 4, 2011 at 11:41 pm
r.dragoi-1095738 (11/4/2011)
I would select the rows first into a variable table and after that I would use the cursor (over the variable table)
I would advise that as well.
I may be talking nonsense but in my experience, when you isolate just the data you want (being in a table variable or temp table), the cursor will behave better and you'd not have to worry about locks.
That works great when the data you're working on is not huge.
That is, if you really need a cursor... 😀
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 5, 2011 at 9:19 am
codebyo (11/4/2011)
r.dragoi-1095738 (11/4/2011)
I would select the rows first into a variable table and after that I would use the cursor (over the variable table)I would advise that as well.
I may be talking nonsense but in my experience, when you isolate just the data you want (being in a table variable or temp table), the cursor will behave better and you'd not have to worry about locks.
That works great when the data you're working on is not huge.
That is, if you really need a cursor... 😀
Best regards,
I agree that by storing the result set in a table variable or temp table you avoid the shared row locks on the rows being fetched.
However, you still cannot avoid the locks taken up when the cursor is opened, ie when the cursor SELECT is executed, unless you run that SELECT in some form of READ-UNCOMMITTED isolation mode.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 8, 2011 at 6:19 am
You guys do realize that selecting into a table variable, and then running a cursor on the table variable, is pretty much what a Static cursor does all by itself, right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 6:50 am
GSquared (11/8/2011)
You guys do realize that selecting into a table variable, and then running a cursor on the table variable, is pretty much what a Static cursor does all by itself, right?
Hmm... I was under the impression that selecting the data first would be faster in large tables.
I remember having changed a code that way in the past and it became a faster query but maybe it was coincidential and it was because a different factor.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 8, 2011 at 6:54 am
Back to step 0.
DELETE the cursor and rewrite to set based.
Post the code if you need help doing that.
November 8, 2011 at 7:50 am
Ninja's_RGR'us (11/8/2011)
Back to step 0.DELETE the cursor and rewrite to set based.
Post the code if you need help doing that.
😀 Of course, my friend. That will be arranged one day.
The problem is we're short on personal and we don't have time to change much code right now. But I'm changing some code bit by bit but we're in a situation where almost all of our stored procedures (if not all of them) have one or more cursors and are very expensive on the resources side.
Thanks for the advice and I'll probably have to get some help here when we're ready for that big step of destroying our highly RBAR'd code.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply