SQL Server JDBC - SelectMethod=cursor and SelectMethod=direct

Author

SelectMethod=cursor and SelectMethod=direct

Frank Brouwer

2005-03-30, 9:40 am

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

Joe Weinstein

2005-03-30, 7:03 pm

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

Frank Brouwer

2005-03-31, 7:01 am

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@bea.com> wrote in message
news:u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl...
>
>
> Frank Brouwer wrote:
>
>
> 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
>

Alin Sinpalean

2005-03-31, 7:01 am

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?

You could try another driver, such as jTDS (disclaimer: I'm a jTDS
developer). As you can see the MS driver either retrieves ResultSets
requesting one row at a time (in cursor mode) or caches the ResultSet
contents into memory (in direct mode). There's no other choice. (jTDS
will not retrieve rows one by one or cache them into memory
unnecessarily.)

There are also quite a few commercial drivers, if you can afford them.
A Google search will get you a complete list.

Alin,
The jTDS Project.

Joe Weinstein

2005-03-31, 8:01 pm

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@bea.com> wrote in message
> news:u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl...
>
>
>
>

Alin Sinpalean

2005-04-01, 8:01 pm

Joe Weinstein wrote:
> The driver *should* not have to do so.

It should not. But it does. According to the "Microsoft SQL Server 2000
driver for JDBC User Guide and Reference" page 27: "You should avoid
using direct when executing queries that produce a large amount of
data, as the result set is cached completely on the client and
constrains memory."

Alin,
The jTDS Project.

Joe Weinstein

2005-04-01, 8:01 pm

Alin Sinpalean wrote:

> Joe Weinstein wrote:
>
>
> It should not. But it does.

Yeah, I know. That's why the emphasis on should.
Did you see email from me about a post to
comp.lang.java.databases about the jTDS driver?

Carb Simien [MSFT]

2005-04-04, 8:01 pm

--------------------
| From: "Frank Brouwer" <frank. brouwer_no_spam@trim
ergo.com>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| References: < 424ac31a$0$96828$dbd
41001@news.wanadoo.nl>
<u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl>
| Subject: Re: SelectMethod=cursor and SelectMethod=direct
| Date: Thu, 31 Mar 2005 11:18:46 +0200
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Lines: 65
| Message-ID: < 424bc07b$0$21008$dbd
45001@news.wanadoo.nl>
| Organization: Wanadoo
| NNTP-Posting-Date: 31 Mar 2005 09:18:51 GMT
| NNTP-Posting-Host: ap-w-47d7.adsl.wanadoo.nl
| X-Trace: 1112260731 dr1.euro.net 21008 212.129.199.215:59847
| X-Complaints-To: abuse@wanadoo.nl
| Path:
TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!npeer.de.kpn-eurorings.net!border2.nntp.ams.giganews.com!nntp.giganews
.com!news2.euro.net!postnews2.euro.net!news.wanadoo.nl!not-for-mail
| Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.jdbcdriver:6785
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| 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@bea.com> wrote in message
| news:u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl...
| >
| >
| > 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
| >
|
|
|

Hi Frank,

If you do decide to use cursor mode with ResultSets defined as
TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then you,
you can additionally test various values for the fetch size to tune the
performance. Otherwise, your fetch size will only be 1.

Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

chinn

2005-04-25, 11:23 am

Hi Simien,
I see lot of select statements with sp_cursoropen with thousands of reads in
the
profiler but if i take the query and run it in the QA it will be 1/4 of
those reads .I
am wondering we will have to tweak somethings like you mentioned below.Could
you help me to find out where i could find those settings like fetchsize and
selectmode..

Thanks In Advance..

""Carb Simien [MSFT]"" wrote:

>
> --------------------
> | From: "Frank Brouwer" <frank. brouwer_no_spam@trim
ergo.com>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | References: < 424ac31a$0$96828$dbd
41001@news.wanadoo.nl>
> <u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl>
> | Subject: Re: SelectMethod=cursor and SelectMethod=direct
> | Date: Thu, 31 Mar 2005 11:18:46 +0200
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Response
> | Lines: 65
> | Message-ID: < 424bc07b$0$21008$dbd
45001@news.wanadoo.nl>
> | Organization: Wanadoo
> | NNTP-Posting-Date: 31 Mar 2005 09:18:51 GMT
> | NNTP-Posting-Host: ap-w-47d7.adsl.wanadoo.nl
> | X-Trace: 1112260731 dr1.euro.net 21008 212.129.199.215:59847
> | X-Complaints-To: abuse@wanadoo.nl
> | Path:
> TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
> ne.de!npeer.de.kpn-eurorings.net!border2.nntp.ams.giganews.com!nntp.giganews
> .com!news2.euro.net!postnews2.euro.net!news.wanadoo.nl!not-for-mail
> | Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.jdbcdriver:6785
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | 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@bea.com> wrote in message
> | news:u8KCl0UNFHA.2468@tk2msftngp13.phx.gbl...
> | >
> | >
> | > 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
> | >
> |
> |
> |
>
> Hi Frank,
>
> If you do decide to use cursor mode with ResultSets defined as
> TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then you,
> you can additionally test various values for the fetch size to tune the
> performance. Otherwise, your fetch size will only be 1.
>
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
>
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
>

Joerg von Frantzius

2005-05-02, 7:23 am

Joe Weinstein schrieb:

> 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)

Hi Joe,

do you know any further details about that next major release, like
*WHEN* it might be released??

Thanks,
Jörg.

Shelby Goerlitz [MSFT]

2005-05-03, 8:23 pm

A new driver is planned for SQL Server 2005 and we're getting close to
announcing details for the next release. I'll post details to this
newsgroup as soon as they are available.

-shelby

Shelby Goerlitz
Microsoft, SQL Server

"Joerg von Frantzius" <joerg.von.frantzius@artnology.REMOVE.com> wrote in
message news:%23krlimvTFHA.3612@TK2MSFTNGP10.phx.gbl...
> Joe Weinstein schrieb:
>
>
> Hi Joe,
>
> do you know any further details about that next major release, like *WHEN*
> it might be released??
>
> Thanks,
> Jörg.

Sponsored Links