September 23, 2011 at 3:02 am
Hi there,
I am confronted with e system, which uses almost exclusively cursors to fetch data. There are a lot of performance issues, which I try to solve and remedy. I know all the bad things about cursors, but let's face it: the system is in use, no wya it is going to be rebuild(it's a commercial package) so I a stuck with cursors.
A few years ago I was faced with the same problem. Then and there a colleague came up with some server-side settings, with which to configure server-side cursors. Those were used then. We played with these settings and saw tremendous changes in speed to retrieve data. But for the heck of it, I can't find the documentation on that issue anymore. Googling the Internet or BOL has not helped me so far, partly because I am not sure what to ask/search for.
Has anyone an idea if there indeed are ways to configure the DBEngine to improve the speed ss-cursors? Or have I been dreaming about this?
Greetz,
Hans Brouwer
September 23, 2011 at 3:48 am
Server side cursors are less memory demanding for the client, but need an extra round trip to the server avery time you need to fetch a row. If you have performance issues, I don't think that a server side cursor will help.
If you have memory issues on the client, you're definitely fetching too many rows.
-- Gianluca Sartori
September 27, 2011 at 6:03 am
Tnx for answering.
I have performance issues and the cursors are not going away real soon. They are build in the communication layer of a commercial 3rd party software. I have been there before n a similar situation. With a far more knowledgeable collegua on the inner workings of SQL Server we came upon some config settings, which affected the way cursors behaved. But for the heck of it, I don't know what they were.
Greetz,
Hans Brouwer
September 27, 2011 at 6:26 am
It seems to me that you can't change the client/server setting of the cursors, can you?
If you're bound to using server side cursors, the only thing that comes to my mind is the "default cursor"=LOCAL|GLOBAL setting in the database properties-->Options applet. However, this won't change performance significantly (I don't think it would change it at all).
Is this what you're looking for? Sorry if I didn't understand your request properly.
Gianluca
-- Gianluca Sartori
September 29, 2011 at 1:45 am
Again, tnx for answering.
That sounds familiar, but there was more to it. We had a set of test scripts for measuring the performance. Basically you could turn all options ON (whatever those options are) or OFF. The difference was significant: when all options were on, this test set ran for 80 seconds. When everything was turned of, this same test set ran in 3 seconds...
I'm pretty sure these were server-sided cursors. I'll search some more with your hint.
Tnx again.
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy