Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
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. |
|
|
Lavos
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!" |
 |
|
|
MuffinMan
Posting Yak Master
107 Posts |
|
|
Sitka
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" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-16 : 08:36:31
|
quote:
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.Damian |
|
|
|
sherrer
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... |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-06-09 : 22:02:19
|
| Bill,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 ModenSQL Server MVP |
|
|
|
|
|
|
|
|
Article: Server Side Cursors and ADO Cursor Types