Re: Sp_CursorOpen,Fetch,Close

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alin Sinpalean (alin_at_earthling.net)
Date: 01/26/05 

Date: 26 Jan 2005 15:25:22 -0800

chinn wrote:
> I have an application when i run a trace against i see a lot of those

> sp_cursoropen,fetch,close
> statements but they don't use ADO the app uses JDBC drivers(I think
MS-JDBC).
> Will above explaination applies to my situation or only applies to
ADO.If so
> could please let me know where the
> cachesize property setting can be done..

JDBC drivers can and do use the sp_cursor stored procedures just as ADO
does (you could do it also directly from an application, the only
problem is that they are undocumented).

The JDBC equivalent for the ADO CacheSize property is "fetch size".
Fetch size can be set for a Statement (and it will be applied to all
ResultSets created from that Statement) or directly for a ResultSet (by
calling setFetchSize() ). Unfortunately the fetch size is just an
indication and a driver may choose to ignore it as it doesn't affect
behavior, only performance; the MS JDBC driver choses to ignore it.

So if you want to improve SELECT performance you will have to switch
drivers; either use a commercial driver or jTDS (
http://jtds.sourceforge.net/ ). With these fetch size actually works
and the difference in performance is considerable.

Disclaimer: I'm a jTDS developer, so my oppinions might not be totally
impartial.

Alin Sinpalean,
The jTDS Project.

Relevant Pages

  • the guests, recoverys, and trees are all verbal and inner
    ... The emergence throughout the welcome supermarket is the driver that ... It turned, you specialised, yet Aloysius never ... quickly splited contrary to the kingdom. ... We fetch them, then we ...
    (sci.crypt)
  • Re: Object oriented method is inefficient with database calling
    ... that from a car instance. ... I don't think caching and prefetching are related. ... I call 'prefetching' the fetch action you're ... it in the very near future, e.g.: you need both car and driver, thus ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Problem updating a record containing a negative Value
    ... If it is the same in ADO or ADO.NET then this is definitely a bug in ODBC ... driver from the vendor. ...
    (microsoft.public.data.ado)
  • Re: Compile error with #import
    ... no ADO and no database access in the kernel. ... > I'm trying to access an SQL Server from my driver using ADO, ... > to my file I get compile errors. ...
    (microsoft.public.development.device.drivers)
  • ADO Select Problem
    ... I got bit today by a problem either in ADO.pm or ADO itself. ... During testing, if I comment out the first fetch, the second fails ... This is perl, v5.8.7 built for MSWin32-x86-multi-thread ... exec 7861700 ...
    (perl.dbi.users)