SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Server Side Cursors and ADO Cursor Types
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/11/2002 :  23:08:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
200 Posts

Posted - 11/12/2002 :  11:40:05  Show Profile  Reply with Quote
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

MuffinMan
Posting Yak Master

USA
107 Posts

Posted - 11/12/2002 :  12:19:03  Show Profile  Reply with Quote
Here are some great ASP/VB/SQL connection tips:

http://www.sql-server-performance.com/visual_basic_performance.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/adoapp.asp



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

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 11/15/2002 :  10:00:54  Show Profile  Reply with Quote
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

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/16/2002 :  08:36:31  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page

sherrer
Yak Posting Veteran

64 Posts

Posted - 11/21/2002 :  11:44:07  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 06/09/2013 :  22:02:19  Show Profile  Reply with Quote
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 Moden
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000