Re: SelectMethod=cursor and SelectMethod=direct

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.

"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
    ... Produced By Microsoft MimeOLE V6.00.2900.2180 ... After processing a resultset the prepared statement is ... | concern is the memory usage as a direct cursor reads the complete ... |>> Frank Brouwer ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Is perl better? :(((
    ... The last cursor is closed ... > when the connection is closed. ... you should close the prepared statement instead of waiting for ... FirstSQL/J Object/Relational DBMS ...
    (comp.lang.java.programmer)
  • Re: Is perl better? :(((
    ... The last cursor is closed ... > when the connection is closed. ... you should close the prepared statement instead of waiting for ... FirstSQL/J Object/Relational DBMS ...
    (comp.lang.java.databases)
  • Re: SelectMethod=cursor and SelectMethod=direct
    ... All the database updates/inserts are done by sql using a prepared statement and then setting the field parameters. ... My only concern is the memory usage as a direct cursor reads the complete resultset into memory. ... 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. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: SelectMethod=cursor and SelectMethod=direct
    ... > We use prepared statements only, one per connection and we don't use ... After processing a resultset the prepared statement is ... such as jTDS (disclaimer: ...
    (microsoft.public.sqlserver.jdbcdriver)