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?



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?

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