March 6, 2006 at 9:22 am
Hi,
I'm looking for some basic cursor info:
We have a mutlitier system
SQL Server 2000 back end, CORBA logic server (changing soon to WebSphere), Java and HTML clients).
The logic servers interact with the database through the Microsoft JDBC driver for SQL Server 2000.
One of our most-used app forms is basically a grid of orders. Using the "direct" select method, this form can take a long time to open. We have tried switching to the "cursor" selct method, with really helps the opening time. Where cursors hurt us, though, is if the user tries to sort the list by clicking on a grid column header. Doing this can take a lot of time and has even pegged the CPU on the client machine, occasionally, in tests.
We use a query to populate the grid and this performance hit that occurs on sorting is even more protracted if the user tries to sort on a field that is not contained in the childmost table of the query. Sorting in this way is not such an issue with the "direct" method. That method seems to be slow on open, sort, and close, although not as bad as sorting with the cursor method.
I am hoping someone would be willing to describe the ins and outs of using cursors vs. direct selects. Maybe cursors are more viable if we only allow sorting by indexed fields in the grid?
Thanks!
March 7, 2006 at 4:07 am
IMHO the best info you can get about cursors is how to avoid them, especially if you're just populating a grid. Set-based approach with standard SELECT should outperform cursors in most situations, sometimes by far. Probably the best way to go is to find out, why selects are so slow...
First obvious step would be to check indexes (whether there are necessary indexes and whether they are not fragmented). Another thing that helps a lot is to use OPTION (fast n), and probably also retrieve only a few rows, not all - basically as many as fit on one screen.
Example:
SELECT TOP 35 col1, col2 ....
FROM table ... JOIN ....
WHERE ....
OPTION(fast 35)
Our system uses this and works quite fine even on tables that have several millions of rows. When user moves the scrollbar slider all the way down, the same query is run again, this time with the number both in TOP and FAST doubled, i.e. 70... and so on, so that it takes some time to retrieve several thousands of rows, but rarely anyone needs to do that. Sorting will always be slower than retrieval of unsorted data, but we can sort by double-click on a column header and it works good.
PS. I don't know much about Java and HTML clients, so I can only hope I haven't been talking about something that can't be done there.
March 7, 2006 at 8:18 am
Thanks for the reply.
Yeah, my knowledge of cursors - beyond a basic grasp of what they do - amounts to "try everything else first."
Regarding our apps, I'm not sure if I can use the OPTION directive you mention, especially if it expects me to change the numbers on each fetch. If such a thing is built into our environments infrastructure, then maybe.
Thanks again!
March 7, 2006 at 8:38 am
The OPTION(fast n) does not require to change numbers on each fetch. It's just that it is so in our system... maybe you'll be happy without any TOP clause and fixed number in the OPTION. Remember my knowledge of your needs is very limited - I only know what you have posted. Maybe this is something entirely different than what you want... but it could be worth exploring along these lines.
I'm sure you'll find a way to optimize the performance sooner or later, so let's hope it will be rather sooner than later :-). Good luck!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply