December 18, 2012 at 2:53 pm
I am hoping for some general information rather than specifics in coding any client app.
I am in the process of tuning our SQL 2008. It has a high transaction use by Hibernate using JDBC (v4.0 I think), therefore a high API cursor use.
I am wondering if less SQL resources will be used if the API cursor is client-side rather than Server-Side , therefore possibly some increased performance from the SQL Server.
What I know so far:
Profiler shows lots of cursor calls:
sp_cursorprepexec
FETCH API_CURSOR00000000003ACBCB
exec sp_cursorfetch 180215089,2,0,128
exec sp_cursorclose 180215089
exec sp_cursorunprepare 1073754801
exec sp_cursorexecute
exec sp_cursorclose 180209429
sys.dm_exec_cursors(0) properties returns -- API | Fast_Forward | Read Only | Global (0)
So I have deduced that it is a API Cursor that is Fast Forward / Read Only and because of the cursor procedures showing up in the profiler the cursor is Server Side???
I have read that a Server Side cursor will always have a minimum number of return trips to the server from the front end, where a client side cursor only has one hit as all the cursor work and maintenance is done on the client once the row set is returned.
To put things in perspective most calls only return one or two rows with the odd one maybe returned ten rows from the database.
Is there somebody might be able to confirm that changing the cursor to the client MAY help in the performance of SQL , or with any later versions of JDBC and the fact the Server Side cursor is READ ONLY / FAST FORWARD that there will be little difference.....thanks
December 18, 2012 at 3:04 pm
GYEAH... nHibernate.
Can you get a hand on your coders to explore the underlying foundation calls? If you can, they can probably get you a better idea of what that particular installation is trying to do.
As to cursors working better on SQL or Client, the answer is 'It Depends'. A lot depends on how much network traffic is being transmitted to keep the client side cursor straight comparive to a few pieces of data sent to the server to get it started and not having to be 'chatty' with a foreign environment.
The real answer is to get the heck out of using the cursor altogether, but that will depend a LOT on your politics and how good your coders are at rewiring nHibernate under the hood. In particular, there's a new structure that I've found to be very helpful in trying to get EF type systems to behave with SQL, that's the table parameter. It allows them to send you a table and then you can use SQL style coding to optimize that set internal to the server. But, that prepends that your coders can get the underlying update calls to USE a proc.
One of the biggest problems with ORMs (Obnoxiously Revolting Mechanisms... errr...) is that they want full control of the database interfacing because they want to lay on top of 'anything', using the engine as a simple cache'd datastore. Your coders will have to get under the hood if you want a chance of optimizing the result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply