This discussion is archived
3 Replies Latest reply: Oct 17, 2012 7:57 AM by cdelahun RSS

MS SQL Server 2008 performance problem

amehta5 Newbie
Currently Being Moderated
We use TopLink 10.1.3.5 to connect to MS SQL Server 2008.
What we are seeing is that when a query is being run by TopLink a lot of cursors open up and remain open. Our database CPU usage goes up and it affects the whole application.
Our DBA took a look at it and said the database shows FETCH_APICURSOR* being used for select statements.
Is there a way to tell TopLink not to use cursors for queries?

Thanks.
  • 1. Re: MS SQL Server 2008 performance problem
    cdelahun Pro
    Currently Being Moderated
    Can you pin point a particular TopLink query tied to the " FETCH_APICURSOR* " call in the app and post how it is being created?

    My guess is that the application is specifying the TopLink query object to return a cursor or stream and not closing it in all cases, or keeping them open for a long period - did you say they were leaking, or is it just that a large number are open at a time leading to performance problems?

    This streams+cursors are described in the TopLink docs here
    http://docs.oracle.com/cd/E21764_01/web.1111/b32441/qryadv.htm#CJGJBHGJ
    or the 10g docs here:
    http://sqltech.cl/doc/oas10gR3/web.1013/b13593/qryadv010.htm

    If this is the case, you might want to use a different strategy such as pagination instead of cursors, described here:
    http://docs.oracle.com/cd/E17904_01/web.1111/b32441/optimiz.htm#CHDIBGFE

    Best Regards,
    Chris
  • 2. Re: MS SQL Server 2008 performance problem
    amehta5 Newbie
    Currently Being Moderated
    The query is a simple named query of Expression type on a descriptor based on a view
    Select * from my_view
    where col1 like param1
    and col2 = param2
    and col3 = param3

    We do not use cursor or a stream to get the results.
    The query is executed by calling session.executeQuery(queryName) which returns a vector.

    Are there any database setting that we need to change?
    Thanks
  • 3. Re: MS SQL Server 2008 performance problem
    cdelahun Pro
    Currently Being Moderated
    I am not that familar with MS SQL or its drivers, but my guess after a quick search is that this is a lower level JDBC setting issue:
    http://msdn.microsoft.com/en-us/library/ms187073(v=sql.105).aspx

    If so, you will have to look over the documentation for the driver you are using for performance tuning. My understanding though is that cursors tied to the resultset should still get closed once the resultset is closed. So if there is a problem with the amount of time they are remaining open, it might be that your queries are returning too much data and could be trimmed using pagination to limit the data being sent.

    http://wiki.eclipse.org/Optimizing_the_EclipseLink_Application_(ELUG)
    has numerous options that might help from the application side of things.

    Best Regards,
    Chris

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points