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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply