Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Server Side Cursors and ADO Cursor Types

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2002-11-11 : 23:08:25
This article discusses some common ADO properties (cursor location, cursor type and lock type) and their effect on performance. It's mainly concerned with eliminating server-side cursors. These are identified in Profiler traces by sp_cursorfetch statements.

Article Link.

Posting Yak Master

200 Posts

Posted - 2002-11-12 : 11:40:05
You are correct. Client side cursors do not allow for Key Set or Dynamic cursors to be made. They require static or forward-only. I'm not sure, but I think forward-only will default to a client side cursor regardless of the cursor location property. Of course, the lock-type could affect that to.

adBatchLockOptimistic should also probably be used with client side cursors, though it does make updating a little more problematic at times since it does require more work. (Or, better yet, use adReadOnly and use con.Execute to issue update statements instead of relying on ADO's recordset to do the changes for you.)

ADO.NET on the other hand, bases everything on client side batch processing. yay.

"O Theos mou! Echo ten labrida en te mou kephale!"
Go to Top of Page

Posting Yak Master

107 Posts

Posted - 2002-11-12 : 12:19:03
Here are some great ASP/VB/SQL connection tips:

Edited by - muffinman on 11/12/2002 12:41:25
Go to Top of Page

Aged Yak Warrior

571 Posts

Posted - 2002-11-15 : 10:00:54
Thanks Graz. When I first started doing this kind of work a little while ago I was lucky enough to inherit ADO code that eventually demonstrated to me the general approach of "get in, grab all you might need, get out, then slam the door when you leave." Whether I can explain it or not I believe the removal of persistence (if that is the right word for multiple trips or enduring connections) is always a goal except when involved in DMO. Besides the ADO Object set is so vast it can simplify to limit your use to the aspects that fit the general approach of "get in, grab all you might need, get out, then slam the door when you leave". That's the meat. The rest is gravy.

Voted best SQL forum nickname...."Tutorial-D"
Go to Top of Page

Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-16 : 08:36:31

get in, grab all you might need, get out, then slam the door when you leave.

I totally agree with that. Most of the work i do calls a stored procedure that returns all the rows, then I do a getRows() on that and close the connection.

I don't want my ADO talking to the database for a millisecond longer than I need it to.

Go to Top of Page


64 Posts

Posted - 2002-11-21 : 11:44:07
I normally agree with everything said here, and normally never use a server side cursor. Just recently I found a situation where a server side cursor is useful for a read only situation.

I was looking for the fastest solution to get multiple result sets into a VB application, so I have a sproc that returns around 12 result sets. The result sets are being loaded into array structures in the VB application. Since a server side cursor returns data, as the data is available, the arrays in VB are being populated as data is streaming in from the server. As soon as the data is populated, the connection is closed. In the same situation, with a client side cursor the data was taking 5 to 10 times as long to get to the application and populated the arrays. On average, 5000 rows are being returned in the different result sets. Also, the more data that is returned from the server, the greater the performance difference becomes between the two cursors.

Just food for thought...

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-06-09 : 22:02:19

I know this article is 11 years old now but I wanted to say "Thank you" for writing it. The simplicity and clarity of your explanation helped me identify and solve several major performance problems tonight. Heh... unfortunately, it means that I have to get the vendor involved especially since a profile trace shows about 300 more cursor opens than closes per hour.

This article is proof positive that certain facts are timeless even across multiple revisions of SQL Server.

Thanks again, Bill.

--Jeff Moden
SQL Server MVP
Go to Top of Page

- Advertisement -