Briefly,
selectMethod=cursor
- theoretically requires more server-side resources than
selectMethod=direct
- only loads at most batch-size records into client memory at once, resulting in a more predictable client memory footprint
- theoretically requires more server-side resources than
selectMethod=direct
- theoretically requires less server-side resources than
selectMethod=cursor
- will read the entire result set into client memory (unless the driver natively supports asynchronous result set retrieval) before the client application can iterate over it; this can reduce performance in two ways:
- reduced performance with large result sets if the client application is written in such a way as to stop processing after traversing only a fraction of the result set (with
direct
it has already paid the cost of retrieving data it will essentially throw away; withcursor
the waste is limited to at most batch-size – 1 rows — the early termination condition should probably be recoded in SQL anyway e.g. asSELECT TOP
or window functions) - reduced performance with large result sets because of potential garbage collection and/or out-of-memory issues associated with an increased memory footprint
- reduced performance with large result sets if the client application is written in such a way as to stop processing after traversing only a fraction of the result set (with
- theoretically requires less server-side resources than
In summary,
- Can using
selectMethod=cursor
lower application performance? — either method can lower performance, for different reasons. Past a certain resultset size,cursor
may still be preferable. See below for when to use one or the other - Is
selectMethod=
an application-transparent setting on a JDBC connection? — it is transparent, but it can still break their app if memory usage grows significantly enough to hog their client system (and, correspondingly, your server) or crash the client altogether
Source: JDBC connection to very busy SQL 2000: selectMethod=cursor vs selectMethod=direct? – Stack Overflow