Re: SelectMethod=cursor and SelectMethod=direct




Frank Brouwer wrote:

Hi Joe, Thanks for the answer.

We use prepared statements only, one per connection and we don't use updateable and/or scrollable resultsets. All the database updates/inserts are done by sql using a prepared statement and then setting the field parameters. After processing a resultset the prepared statement is closed and the connection is returned to the connection pool, using a "finally block" in the method.

Reading your advice it seems best to switch using direct cursors. My only concern is the memory usage as a direct cursor reads the complete resultset into memory (I asume analyzing profiler output).

Any tips on that issue?

TIA,

Frank.

The driver *should* not have to do so. It can/should leave the data latent on the net, and read it in as needed, unless and until some other client activity, such as a second concurrent statement requesting data. At that time the driver would have to read everything from the first query, and buffer it. If the free MS driver reads everything in immediately, it's just something you would have to deal with, alloting your client enough memory to handle it. As an architectural point, whenever a client is extracting enough raw data from the DBMS to have memory issues, I always look for a way to process the data where it is, in the DBMS, using stored procedures, and extract only the info a human really wants. If this sort of thing is possible, your performance will be much better. Also, as Alin states, there are better, more modern up-to-date drivers than this one. Joe Weinstein at BEA


"Joe Weinstein" <joeNOSPAM@xxxxxxx> wrote in message news:u8KCl0UNFHA.2468@xxxxxxxxxxxxxxxxxxxxxxx



Frank Brouwer wrote:


Hello All,

A query is taking more than 2 minutes to fetch 51 rows using
SelectMethod=cursor, when I change that to SelectMethod=direct I get the
results split seccond.  So in the system only the SelectMethod has been
changed nothing else.

Has any one a clue why there is so much differance?
What are the rules to know what method is best?

TIA,
Frank Brouwer

Hi. This setting is an artifact of a design decision in the current free MS driver.
I believe this will change in the next major relase. (MS should confirm/deny)
If you never need multiple-statement transactions, or you never have more than
one open statement at a time, you can always use direct. Otherwise you need cursor
mode. With direct, if you have multiple statements open at one time, this driver
creates extra DBMS sessions under-the-covers to process each statement.
However, there may be some DBMS tuning you can do to make cursor mode run
faster. Does the table (do the tables) you are querying have an appropriate index
to make a row-by-row cursor efficient on the data you want? Are you making your
result sets updateable or scrollable? If you don't have to do that, don't. Plain
forward-only non-updateable result sets are fastest.


Joe Weinstein at BEA





.



Relevant Pages

  • Re: SelectMethod=cursor and SelectMethod=direct
    ... All the database updates/inserts ... After processing a resultset the prepared statement is closed ... concern is the memory usage as a direct cursor reads the complete resultset ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Cursor problems Wd 2003
    ... Graham, I haven't actually updated the driver yet, but it is a recent driver ... Whether the cursor is at the ... Graham Mayor - Word MVP ... Word MVP web site http://word.mvps.org ...
    (microsoft.public.word.docmanagement)
  • Re: How to show/hide cursor on attach/deattach usb mouse
    ... If the cursor is not visible, your display driver is not drawing it. ... Can you have any idea where i put registration notifications for ...
    (microsoft.public.windowsce.platbuilder)
  • Re: How to show/hide cursor on attach/deattach usb mouse
    ... Along with that Display Driver is modified with RequestDeviceNotifications, ... and switching on/off the cursor. ... handling USB mouse device notifications. ...
    (microsoft.public.windowsce.platbuilder)
  • Re: How to show/hide cursor on attach/deattach usb mouse
    ... I have modified the display driver properly and all works... ... Can you have any idea where i put registration notifications for usb mouse ... There's no 'enable/disable cursor here' function. ...
    (microsoft.public.windowsce.platbuilder)